In a previous post, Collecting Historical Wait Statistics, I discussed how you can easily collect historical wait stats by using the DMV sys.dm_os_wait_stats. Well today, I’d like to cover the same concept, but this time collect historical IO file stats from the DMV, sys.dm_io_virtual_files_stats. However, I wanted to improve on the code to make it even easier to implement. The data collection process is still implemented the same way. First, we’ll need to create a history table to store the data. The data is stored in time slices with the cumulative values as well as the difference (TimeDiff_ms, NumOfReadsDiff, NumOfWritesDiff,...
Continue reading...Performance
Collecting Historical Wait Statistics
As a DBA, I’m sure you’ve heard many times to always check the sys.dm_os_wait_stats DMV to help diagnose performance issues on your server. The DMV returns information about specific resources SQL Server had to wait for while processing queries. The counters in the DMV are cumulative since the last time SQL Server was started and the counters can only be reset by a service restart or by using a DBCC command. Since DMVs don’t persist their data beyond a service restart, we need to come up with a way to collect this data and be able to run trending reports...
Continue reading...An Alternative to SELECT COUNT(*) for Better Performance
Sometimes rapid code development doesn’t always produce the most efficient code. Take the age old line of code SELECT COUNT(*) FROM MyTable. Obviously this will give you the row count for a table, but at what cost? Doing any SELECT * from a table will ultimately result in a table or clustered index scan. Turning on STATISTICS IO on reveals 5753 logical reads just to return the row count of 776286.Table ‘FactProductInventory’. Scan count 1, logical reads 5753, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Starting with SQL Server 2005, Microsoft introduced a...
Continue reading...Dealing with a Fragmented Heap
Just for the record, this happens to be one of my favorite interview questions to ask candidates. At some point in time, there will be a database containing tables without clustered indexes (a heap) that you will be responsible for maintaining. I personally believe that every table should have a clustered index, but sometimes my advice is not always followed. Additionally there can be databases from a 3rd party vendor that have this same design. Depending on the what those heap tables are used for, over time it’s possible they can become highly fragmented and degrade query performance. A fragmented heap is...
Continue reading...The Performance Impact to Prefix Stored Procedures with sp_
Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries. In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,… This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that’s prefixed with sp_. All DBAs know, or should know, you should not prefix stored procedures with sp_. Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online. I’m not going to discuss the do’s and don’ts of naming conventions. What I want...
Continue reading...
