Five DMV Queries That Will Make You a Superhero in 2011!

Way back in January, 2008, I wrote a blog post called  “Five DMV Queries That Will Make You A Superhero!” which continues to get lots of hits three years later. I thought it was time to update this with a new version that you can download from here.

Query 1 looks at cumulative waits for the entire database instance since the last time SQL Server was started (or the last time the wait stats were cleared with the command shown below).

-- Clear Wait Stats for entire instance
    DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

Query 1 tells you what resources that SQL Server is spending the most time waiting for. This can help you focus your subsequent diagnostic and troubleshooting efforts, so it is very valuable. I do want to caution people not to obsess over the results though. What I mean by that is that I frequently see people get very worried about what ever shows up as the top wait type, even if their SQL Server instance is running just fine, with no performance or scalability problems. 

SQL Server is always waiting on something, but many wait types are pretty benign (which is why I try to filter many of them out). If your instance is running well, with no other indicators of trouble, relax a little! This query is much more valuable when you are experiencing performance problems, or you see other symptoms of stress.

Query 2 lets you look at memory clerk usage, which will help you find issues with procedure cache bloating with ad-hoc query plans. Back in SQL Server 2005 days, the SQL Server Agent was a major culprit here. I also see many ad-hoc plans coming from SharePoint 2007 and 2010. The worst offender may be your own developers, if they like to write ad-hoc SQL by concatenating a T-SQL query in the client or middle-tier.

You can use the “Optimize for ad-hoc workloads” instance setting (which was new in SQL Server 2008) to help minimize this problem. You may need to use the old band-aid approach of periodically running the command shown below.

-- Clear ad-hoc SQL plans for entire SQL instance
    DBCC FREESYSTEMCACHE('SQL Plans'); 

Query 3 will find the most expensive cached stored procedures in the current database, from a CPU perspective. You would definitely want to run this if you saw any signs of CPU pressure. You can easily change the sort order and column selection of this query to focus on other pain areas, such as logical reads, logical writes, etc.

Queries 4 and 5 will help you tune your relational index usage and selection. I usually call those two queries, “the dynamic duo”, since they are so useful for getting the right number of useful indexes for your workload. This is critical for getting the best performance and scalability for your databases.

I talked about this subject in much more detail recently in a post called “Index Tuning for Mere Mortals”.

These five queries should give you plenty of opportunities to “save the day”, and impress your boss with your awesome SQL Server tuning skills, so have fun!