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!
procedure cache bloating–what a nuisance! Thanks for these, Glenn!
Pingback: @GlennAlanBerry posts Five DMV Queries That Will Make You a Superhero in 2011! | sqlmashup
Thanks for these, Glenn. Much appreciated! You just made my job a lot easier.
Glad you liked them, Don.
Hi Glenn, thx for the info, it was helpful … I have some more questions on performance, is there any way that I can send you personal email?
Pingback: DMV queries for QA servers. |
Is there any way to know when the dbcc command to clear sys.dm_os_wait_stats was last executed? I was hoping this was logged in the instance error log, but can’t seem to find it. Any direction you can provide would be most appreciated.
I don’t know that there is any place where that is logged.
Pingback: SQL Server A to Z - Waits | Cleveland DBA
Thanks Glenn. Very helpful scripts. Please note that the column single_pages_kb in query2 has been replaced by the column pages_kb on SQL Server 2012.