The Dynamic Management Views Worth Knowing by Heart

SQL Server dynamic management views expose activity and internal state that ordinary application tables cannot show. A few well-chosen queries can tell you where to investigate next.

Start With the Question

A useful diagnostic query answers something specific. What is running now?

What has consumed CPU since entering cache? Which files are nearly full? Those questions need different views and different interpretations.

Many server-level performance views require VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. Earlier releases commonly use VIEW SERVER STATE. Database-scoped views have their own requirements, and hosted services can expose a narrower scope.

Check the documented permission for each view before granting access. Do not make an application account a server administrator just to run a dashboard. A diagnostic account should have a deliberate purpose.

Separate Sessions From Requests

A session is a connection context. A request is work currently executing within that context. An idle pooled connection can have a session without an active request, which makes the distinction useful during incidents.

SELECT r.session_id, s.login_name, s.host_name,
       r.status, r.command, r.cpu_time,
       r.total_elapsed_time, r.wait_type,
       r.blocking_session_id, DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

This is a snapshot, so brief requests can finish before you capture them. Sleeping sessions can still matter when they retain open transactions. Investigate a blocking chain before deciding that its oldest visible request is the cause.

Treat Waits as Accumulated Evidence

Wait statistics explain categories of waiting, not a complete root cause. Their totals normally accumulate since startup or the last reset. A large total from yesterday may tell you little about the current slowdown.

SELECT TOP (20) wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

Capture two snapshots across the period that matters and compare the differences. Separate background activity from waits associated with your workload. Do not clear shared counters merely to make a personal investigation easier.

Signal wait time is the portion spent waiting for CPU after the resource wait completes. It can help frame a CPU investigation. It does not identify the expensive statement by itself.

Use Cached Query Statistics Carefully

Cached query statistics summarize completed executions associated with cached plans. Entries disappear when plans leave cache. This makes the view useful for a quick lead, but incomplete as a long-term record.

SELECT TOP (20) qs.execution_count,
       qs.total_worker_time, qs.total_elapsed_time,
       qs.total_logical_reads, qs.last_execution_time,
       SUBSTRING(st.text,
         (qs.statement_start_offset / 2) + 1,
         ((CASE qs.statement_end_offset
             WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END
           - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

Worker and elapsed time values are reported in microseconds, with accuracy limited to milliseconds. Compare total cost and execution count together. A cheap statement repeated constantly can deserve attention before one expensive occasional execution.

Give Index Activity an Observation Window

Index usage counters describe access since their relevant reset boundary. A missing row or zero seeks does not prove an index is useless. The index might support a monthly report or enforce a constraint.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS table_name, i.name,
       u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
  ON u.object_id = i.object_id
 AND u.index_id = i.index_id
 AND u.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
  AND i.index_id > 0;

Run this in the database you want to investigate. Record the server startup time and any relevant maintenance or database events. Review the index definition and workload coverage before proposing removal.

Check Space Without Mixing Its Meanings

Allocated file space, space used inside a file, and free disk space are different quantities. This database-scoped view reports data-file allocation categories. It does not tell you whether the underlying volume can accommodate another growth.

SELECT file_id, total_page_count, allocated_extent_page_count,
       unallocated_extent_page_count,
       user_object_reserved_page_count,
       internal_object_reserved_page_count
FROM sys.dm_db_file_space_usage;

The values are page counts, and a SQL Server page contains eight kilobytes. Pair this evidence with file settings and volume capacity. Save timestamps with every sample so that later comparisons describe the same observation window.

A DMV is not a diagnosis, it is evidence for your next question.

This post was rewritten from scratch in September 2026. The original, published on 2007-08-16, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.