Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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!


Comments

Posted by Jason Brimhall on 8 February 2011

Thanks Glenn.  These should prove useful.

Posted by Tobias.Pick on 9 February 2011

Awesome, thanks a lot!!!

Posted by mgruebe3 on 9 February 2011

Thanks - I love SQL Server Central!

Posted by Anita Rego on 9 February 2011

Very useful.. thanks!

Posted by Michael Kaplan on 9 February 2011

Glenn,

Queries one and two rock. However it appears that queries 3, 4 and 5 have vanished from the post.

Posted by Wally on 9 February 2011

^^^ What Glenn said...

Posted by Glenn Berry on 9 February 2011

kaplanm,  

The actual queries that go with this post have to be downloaded from Dropbox, using the link I provided in the post.

Posted by Glenn Berry on 9 February 2011

Anita,

Glad you liked them. Thanks!

Posted by petethielen on 9 February 2011

They are in the download in the first paragraph.

Posted by petethielen on 9 February 2011

... and they rock!

Thanks Glenn!

Posted by mark.marsh on 9 February 2011

Kaplan & Wally,  you need to follow the link to the original post to see th 5 queries

sqlserverperformance.wordpress.com/.../five-dmv-queries-that-will-make-you-a-superhero

The dbcc calls above are in addition to the query's this artical is post about.

Posted by Glenn Berry on 9 February 2011

Pete Thielen,

Thanks for the kind words.

Posted by Robert L Davis on 9 February 2011

Nice set of queries!!

Posted by Glenn Berry on 9 February 2011

Thanks, Robert.

Posted by Dave Schutz on 9 February 2011

Thanks Glenn. Think I'll add these into my SQL Scripts Manager.

Posted by daveygf on 9 February 2011

thanks Glenn

Posted by DF Kimball on 10 February 2011

Nice !!

Posted by Jinroh on 11 February 2011

Thanks for this greatfull scripts :)

Posted by G Bryant McClellan on 11 February 2011

I suggest changing the ORDER BY in query 4 to the average worker time calculation. Consider that a very efficient stored procedure called many times can rack up a very large accumulated total worker time. If you sort by the average instead you can see those that are most expensive PER execution. I've already used that to tweak a couple of stored procedures for good effect.

Overall, well done and thanks!

Posted by Glenn Berry on 11 February 2011

G Bryant,

I am actually more interested in finding the stored procedures that have the highest total cumulative impact on SQL Server in a particular area. If a very efficient SP is called very frequently, it could be the most expensive, cumulatively, and I would want to know that.

If the SP in question has been optimized as well as possible, I would be talking to the developers about why we need to call the SP so often, can we use middle-tier caching, etc.

Of course, as I suggested in the post, you are free to sort how ever you like, depending on what you are trying to discover.

Leave a Comment

Please register or log in to leave a comment.