Recently we encountered and filed a benign reporting BUG which reports the maxsize of the data file incorrectly. If you create a database & then alter the initial size of the database to be greater than maxsize & then query sys.database_files, sys.master_files, sysaltfiles the maxsize appears to be incorrectly reporting the older value of maxsize.… Continue reading SQL Server Datafile maxsize reported incorrectly
Category: SQL 2012
Disabling Non-Clustered Columnstore Index doesn’t appear to execute in Stored Procedure in SQL 2012
Consider the following scenario, you have a large fact table with non-clustered columnstore index. In SQL 2012, Columnstore index is not updatable hence at night when you load or purge the table, the first step would be to disable non-clustered columnstore index followed by insert/update/delete to the table and the final step would be to… Continue reading Disabling Non-Clustered Columnstore Index doesn’t appear to execute in Stored Procedure in SQL 2012
How SQL Server Writes Transaction Log Sequentially
Last week in of the SQL Community Forum, one of my friend asked the following question If I have 2 Tlog files for one database, how will these files fillup? A) File 1 will be filled first and then File 2 B) Both the files will be filled together. If B is true then how… Continue reading How SQL Server Writes Transaction Log Sequentially
Impact of Pre-grown Data & Log Files on the Restore Time of the Database
Today, I was asked the following question by one of my colleague who specializes in Sharepoint If the actual data is 50GB in size & if we create a pre-grown data file of 200GB to avoid autogrow & fragmentation, the database restore time for the pre-grown database should be much larger than the restore time… Continue reading Impact of Pre-grown Data & Log Files on the Restore Time of the Database
Troubleshooting SQL Server Hang using system_health Xevents
Recently my customer reported they were seeing intermittent server hangs, connection timeouts and slow performance on their SQL Server instance. The first question I had, do we have PSSDIAG running on the SQL Instance for me to be able to understand the cause of the hang. However since we won’t be running PSSDIAG proactively on… Continue reading Troubleshooting SQL Server Hang using system_health Xevents
Can number of worker threads in SQL Server exceed max worker threads setting?
Recently while troubleshooting a performance issue on the SQL Instance which was experiencing a very high THREADPOOL wait type I observed something which I was unaware until I hit this issue. While I was looking at the system health xel files captured at the time of the issue, specifically at the query_processing event trace, the… Continue reading Can number of worker threads in SQL Server exceed max worker threads setting?
Analyze SQL Database Table Space Usage using TreeMap Visualization in Excel 2013
One of fascinating features of Excel 2013 is “App for Office” option which exposes us to some of the great apps from Office store which you can download and install. One of such useful apps which I came across is TreeMap Data Visualization developed by Microsoft Research which allows you to represent hierarchical data of… Continue reading Analyze SQL Database Table Space Usage using TreeMap Visualization in Excel 2013
Web Application Analytics using IIS Logs & Powerview
I have been a big fan of Powerview for its adhoc reporting capability and visualizations since its release. With Excel 2013, Powerview integrated natively with excel makes Excel even more powerful giving it adhoc reporting and data exploration capability. Technically, we all need BI in some form or the other and hence we all perform… Continue reading Web Application Analytics using IIS Logs & Powerview
SQL Reporting Services Performance Tuning Tips & Tricks
It has been really long time since I have blogged. However I will try to blog as often as I can. Very Recently, I performed a SSRS Performance Review to tune performance reports for one of our customers. While analyzing some long running reports we identified some usual issues which may not be aware while we are… Continue reading SQL Reporting Services Performance Tuning Tips & Tricks
Gotcha – Listening SQL Server Availability Group Listener on non-default port
While configuring SQL Server AlwaysON Availability Groups, as a part of security hardening practice it is recommended to configure Availability Group to listen on a non-default port other than 1433. However the moment you configure your Availability Group Listener on a non-default port and if you SQL Server instance is also configured to listen on… Continue reading Gotcha – Listening SQL Server Availability Group Listener on non-default port