Playing Doctor with DMVs

SQL Server 2005 and newer come with Dynamic Management Views (DMVs) that can help you conquer some tough troubleshooting challenges.  In this one-hour video, I show you how to find hyperactive files & filegroups, catch potential partitioning pitfalls, sniff out heaps, and use a Central Management Server to triage your servers.

Here’s my favorite DMV queries from around the web:

SQL Server’s Medical File – sys.dm_os_wait_stats – this DMV shows what SQL Server has been waiting on since the last time it restarted (or the last time someone reset the DMV using DBCC SQLPERF).

• DMV query to check file activity – which files are most heavily accessed
• DMV query to find heaps – tables without a clustered index
• Central Management Server – article at SQLServerPedia with a video tutorial on how to set it up.

SQL Server’s Current Pulse – sys.dm_exec_requests – this view shows what’s executing right now on the server.  To be useful, you have to join it to a few other DMVs, but thankfully there’s a bunch of good queries already written for you:

• Adam Machanic’s sp_WhoIsActive – great replacement for sp_who and sp_who2.  It’s completely free, and it fetches all kinds of cool data like the current wait stats and execution plan.  Watch my video on how to use it.
• Check restore progress – want to know when your restore will finish?  Tony Rogerson shows you how.

SQL Server’s Recent Medical History – sys.dm_exec_query_plan – shows queries in the procedure cache that have been executing recently.  This particular set of DMVs is a little less reliable than the rest because the contents can change at any time based on how much memory your server has, but nonetheless, it’s a great way to see what you missed a few minutes ago.

• Glenn Berry’s DMV Troubleshooting Queries – a whole bunch of very useful queries with plenty of comments to explain what they’re doing.  Don’t just paste it in and hit F5 – run each query one by one and examine the results.
• Procedure cache contents sorted by CPU or reads – a simple query from SQLServerPedia to examine the cache.  Restrict the results by doing just SELECT TOP 20 on servers that have more than a few gigabytes of memory, because the procedure cache can be very large.
• Queries in the procedure cache with implicit conversions – this particular performance problem is really tough to catch, and the procedure cache makes it much easier because we can examine the plan contents.  To learn more about implicit conversions, check out the footnotes on my sargability post.
• Grant Fritchey’s excellent book on query tuning – if you want help understanding what’s in an execution plan and how to make queries go faster, check out my review of his book.

SQL Server Index Health – the index DMVs are controversial among experts because they frequently get abused.  Just because SQL Server’s DMVs suggest that you should add an index doesn’t really mean you should.  Be careful when you’re using these.

• SQLServerPedia’s Index DMV Queries – I’ve recorded videos on each query explaining how to use it.

And check out my other free SQL Server training videos.