The DMVs Worth Knowing

Dynamic management views are the server telling you about itself in real time. There are hundreds. Three of them do most of the work, and one more answers "what is happening right now."

Note the scope difference from Query Store: DMVs read the plan cache and current activity, so they reset on restart and lose entries when plans are evicted. They are excellent for "what is happening now" and "what has been expensive since the last restart."

sys.dm_exec_requests — what is running this second

Run this while the slow report is executing. It is the fastest way to see whether the query is working or waiting.

SELECT  r.session_id,
        r.status,                       -- running / runnable / suspended
        r.command,
        r.wait_type,
        r.wait_time        AS wait_time_ms,
        r.blocking_session_id,
        r.cpu_time         AS cpu_ms,
        r.total_elapsed_time AS elapsed_ms,
        r.logical_reads,
        r.granted_query_memory * 8 / 1024 AS granted_memory_mb,
        DB_NAME(r.database_id) AS database_name,
        SUBSTRING(t.text,
                  (r.statement_start_offset/2) + 1,
                  ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                        ELSE r.statement_end_offset END - r.statement_start_offset)/2) + 1
                 ) AS running_statement,
        p.query_plan
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)      AS t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle)   AS p
WHERE r.session_id <> @@SPID
  AND r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

Read it like this:

• status = 'running' or 'runnable' → burning CPU (runnable means "ready, waiting for a CPU scheduler" — lots of runnable sessions means CPU pressure).
• status = 'suspended' with a wait_type → waiting on the thing named in wait_type.
• blocking_session_id > 0 → another session holds a lock you need. That is a concurrency problem, not a query problem (Module 6).
• The query_plan column gives you the live estimated plan of the exact statement running now, which is invaluable when a report is slow only in production.

sys.dm_exec_query_stats — the expensive queries since restart

Aggregated per cached plan. This is the DMV equivalent of Query Store's top consumers, useful when Query Store is off.

SELECT TOP (25)
       qs.execution_count,
       qs.total_worker_time  / 1000            AS total_cpu_ms,
       qs.total_worker_time  / qs.execution_count / 1000 AS avg_cpu_ms,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_elapsed_ms,
       qs.total_logical_reads / qs.execution_count       AS avg_logical_reads,
       qs.total_rows / qs.execution_count                AS avg_rows_returned,
       qs.max_grant_kb / 1024                            AS max_grant_mb,
       qs.creation_time,
       qs.last_execution_time,
       SUBSTRING(t.text,
                 (qs.statement_start_offset/2) + 1,
                 ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                       ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1
                ) AS statement_text,
       p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
ORDER BY qs.total_worker_time DESC;

Two columns people underuse:

• avg_rows_returned — a report query returning 500,000 rows per execution is a design problem no index will solve. It is the first hard evidence for the client-side aggregation issue in Module 6.
• max_grant_kb — a query asking for gigabytes of memory grant will queue behind other queries waiting for memory, and starve the rest of the workload. Large grants usually trace back to a bad row estimate (Module 2).

Sort by total_worker_time to find what costs the server most; sort by avg_elapsed_ms to find what a user is complaining about.

sys.dm_os_wait_stats — the aggregate "what are we waiting on"

Cumulative since the instance started (or since someone cleared it). Covered in detail next lesson; the shape is:

SELECT TOP (15)
       wait_type,
       wait_time_ms / 1000.0                      AS wait_seconds,
       (wait_time_ms - signal_wait_time_ms)/1000.0 AS resource_wait_seconds,
       signal_wait_time_ms / 1000.0               AS cpu_queue_seconds,
       waiting_tasks_count,
       CAST(100.0 * wait_time_ms
            / NULLIF(SUM(wait_time_ms) OVER (), 0) AS decimal(5,2)) AS pct_of_all_waits
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT IN (
      'CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK',
      'SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
      'XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',
      'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
      'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
      'QDS_ASYNC_QUEUE','QDS_SHUTDOWN_QUEUE','HADR_FILESTREAM_IOMGR_IOCOMPLETION',
      'DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP','PREEMPTIVE_XE_GETTARGETSTATE')
ORDER BY wait_time_ms DESC;

The exclusion list filters out idle/background waits that would otherwise dominate the top of the list and tell you nothing.

Index usage, while you are here

Before Module 3, one more DMV worth having in your pocket — it tells you which indexes are actually being used and which only cost you writes:

SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
        OBJECT_NAME(i.object_id)        AS table_name,
        i.name                          AS index_name,
        i.type_desc,
        s.user_seeks, s.user_scans, s.user_lookups,
        s.user_updates,                       -- writes: the cost side
        s.last_user_seek, s.last_user_scan
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
       ON s.object_id = i.object_id
      AND s.index_id  = i.index_id
      AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
ORDER BY s.user_updates DESC;

An index with 900,000 user_updates and zero seeks/scans/lookups is pure overhead. Note the caveat: these counters reset on instance restart and on some index rebuild operations, so check sqlserver_start_time in sys.dm_os_sys_info before concluding an index is unused — a week of uptime is meaningful, an hour is not.

The short version

dm_exec_requests for what is happening now, including blocking and waits. dm_exec_query_stats for what has been expensive since restart, with the plan attached. dm_os_wait_stats for the aggregate picture. dm_db_index_usage_stats before you add or drop an index. Query Store when it is available, because it survives restarts and lets you compare before and after.