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).

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:

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.

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.

And check out my other free SQL Server training videos.

3 Responses to Playing Doctor with DMVs
  1. sap
    November 27, 2010 | 9:37 PM

    All I want to say is Great work and Thanks Guys!

  2. Mohammed
    January 27, 2011 | 11:33 AM

    I recently attended you training on improving backup/restores performance. It was great. In the session you mentioned to use :”SELECT r.percent_complete,

    estimated_finish_time = DATEADD(MILLISECOND, estimated_completion_time, CURRENT_TIMESTAMP),

    t.FROM sys.dm_exec_requests AS r CROSS APPLY

    sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.session_id = 149″

    to find status of restore. I am using litespeed to restore a 300gb db and the finish time shows current time and percent_complete is 0. Is there something i am missing here?

    • Brent Ozar
      January 27, 2011 | 12:32 PM

      Mohammed – your best bet for LiteSpeed questions is to contact support. Thanks!

Leave a Reply

Wanting to leave an <em>phasis on your comment?

Notify me of followup comments via e-mail. You can also subscribe without commenting.