SQL Server DMV Reference: The DBA Cheat Sheet for Performance Diagnostics

---

Dynamic Management Views are SQL Server’s diagnostic windows into performance internals. They show where the server is waiting, which queries burn CPU and I/O, which indexes are used or wasted, and how execution plans are being reused or abused. This article is the reference version: the one to bookmark and return to during an incident, not the one to read once and forget. Every query here is production-safe and ready to copy.

1 What DMVs Are and Are Not Beginner

DMVs live under the sys.dm_* namespace and expose live engine data: waits, queries, cache, sessions, indexes, I/O, and memory. They have been part of SQL Server since version 2005 and now number in the hundreds, covering the query processor, storage engine, and memory manager.

Three things DMVs are not:

• Not gospel. Brent Ozar, Erik Darling, and Kendra Little have all documented cases where DMVs mislead. They can reset after restarts, roll data up in unexpected ways, and reflect cumulative totals that skew heavily toward events that happened right after the last restart. Always interpret DMV data in context.
• Not a replacement for Extended Events. DMVs provide snapshots and aggregates. Extended Events capture individual event instances with timestamps. For root cause analysis of intermittent problems, Extended Events is the right tool. DMVs are where to start, not where to finish.
• Not persistent. Most DMVs reset on SQL Server restart, database detach and attach, and in some cases on availability group failover. Data reflects activity only since the last reset. Run DMV queries against a server that has been running under normal production workload, not one that restarted six hours ago.

2 Permissions Required Beginner

Permission	Grants Access To	How to Grant
VIEW SERVER STATE	All server-level DMVs: wait stats, sessions, requests, cache, I/O	GRANT VIEW SERVER STATE TO [LoginName];
VIEW DATABASE STATE	Database-scoped DMVs: index usage, partition stats, query store	GRANT VIEW DATABASE STATE TO [UserName];
sysadmin	Everything, implicitly	Included by role membership

3 The DMV Cheat Sheet: Ranked by Usefulness Beginner

The list below is ordered by how often each DMV appears in real production investigations. The first five should be muscle memory for any SQL Server DBA.

Rank	DMV	What It Shows	Start Here When
1	sys.dm_os_wait_stats	Cumulative wait times since last restart by wait type	Server is slow and the cause is unknown
2	sys.dm_exec_requests	All currently executing requests with CPU, reads, and blocking	Something is slow or blocking right now
3	sys.dm_exec_query_stats	Aggregated execution stats for cached plans: CPU, reads, duration	Finding the worst-offending queries
4	sys.dm_exec_sql_text	SQL text for a given plan or request handle	Cross-applied with query_stats or requests
5	sys.dm_exec_query_plan	Estimated XML execution plan for a cached plan handle	Reviewing what plan is in cache for a query
6	sys.dm_db_index_usage_stats	Seeks, scans, lookups, and updates per index since restart	Identifying unused indexes or missing seeks
7	sys.dm_db_missing_index_details	Indexes SQL Server wished existed during query compilation	Starting point for index tuning (validate before creating)
8	sys.dm_io_virtual_file_stats	Read and write latency per database file	PAGEIOLATCH or WRITELOG waits in the delta
9	sys.dm_exec_cached_plans	Cached plan memory usage and reuse counts	Plan cache bloat from single-use ad-hoc queries
10	sys.dm_exec_sessions	All connected sessions with login, host, and resource settings	Identifying which application or user is generating load
11	sys.dm_os_waiting_tasks	Tasks actively waiting for resources right now	Complements wait_stats with real-time active waits
12	sys.dm_db_partition_stats	Row counts and space usage per table and index partition	Quick table size check during tuning
13	sys.dm_exec_input_buffer	Last statement sent by a specific session	Ad-hoc troubleshooting when you need to see what a session ran last

4 Wait Statistics: Where Is the Server Hurting Beginner

Start every performance investigation here. SQL Server records accumulated wait times for every wait type since the last restart. The top wait types tell which diagnostic path to follow next.

-- Baseline wait statistics (exclude benign background waits)
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    ROUND(wait_time_ms / 1000.0, 1)        AS TotalWaitSec,
    ROUND(signal_wait_time_ms / 1000.0, 1) AS SignalWaitSec,
    ROUND((wait_time_ms - signal_wait_time_ms) / 1000.0, 1) AS ResourceWaitSec,
    ROUND(wait_time_ms * 100.0
        / SUM(wait_time_ms) OVER(), 2)     AS PctOfTotalWaits
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    -- Benign waits to exclude from analysis
    'SLEEP_TASK', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_DBSTARTUP',
    'SLEEP_DBTASK', 'SLEEP_TEMPDBSTARTUP', 'SLEEP_SYSTEMTASK',
    'DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'DBMIRROR_EVENTS_QUEUE',
    'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'HADR_WORK_QUEUE', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'ONDEMAND_TASK_QUEUE', 'CHECKPOINT_QUEUE', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'RESOURCE_QUEUE', 'SERVER_IDLE_CHECK', 'SNI_HTTP_ACCEPT',
    'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'WAIT_XTP_OFFLINE_CKPT_NEW_LOG'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Quick interpretation guide in comments:
-- PAGEIOLATCH_SH / EX  = slow storage or large scans
-- CXPACKET / CXCONSUMER = parallelism issues (MAXDOP, cost threshold)
-- LCK_M_*             = blocking and transaction design
-- WRITELOG             = log I/O stalls or autogrowth
-- SOS_SCHEDULER_YIELD  = CPU pressure
-- RESOURCE_SEMAPHORE   = memory grant waits

5 Active Sessions and Blocking Beginner

-- Currently active requests: what is running right now
SELECT
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time / 1000        AS ElapsedSec,
    r.logical_reads,
    r.blocking_session_id,
    DB_NAME(r.database_id)             AS DatabaseName,
    LEFT(t.text, 500)                  AS QueryText
FROM sys.dm_exec_requests              r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50               -- exclude system sessions
ORDER BY r.total_elapsed_time DESC;

-- Blocked sessions only: find the blocking chain
SELECT
    r.session_id                        AS BlockedSession,
    r.blocking_session_id               AS BlockedBy,
    r.wait_type,
    r.wait_time / 1000                  AS WaitSec,
    DB_NAME(r.database_id)              AS DatabaseName,
    LEFT(t.text, 300)                   AS BlockedQuery
FROM sys.dm_exec_requests              r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0
ORDER BY r.wait_time DESC;

-- Find the head blocker: the session blocking others but not itself blocked
SELECT session_id                       AS HeadBlocker
FROM sys.dm_exec_sessions
WHERE session_id IN (
    SELECT blocking_session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
)
AND session_id NOT IN (
    SELECT session_id
    FROM sys.dm_exec_requests
    WHERE blocking_session_id <> 0
);

-- What is the head blocker actually doing
-- Replace 55 with the session_id returned above
SELECT event_info
FROM sys.dm_exec_input_buffer(55, NULL);

6 Expensive Queries: CPU, Reads, Duration Intermediate

These queries read from the plan cache and return aggregated statistics since the last restart or cache clear. They are the starting point for identifying which queries are responsible for the load the wait statistics identified.

-- Top queries by total CPU (most expensive overall since restart)
SELECT TOP 10
    qs.total_worker_time / 1000             AS TotalCPUMs,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS AvgCPUMs,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgElapsedMs,
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    DB_NAME(st.dbid)                        AS DatabaseName,
    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 QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats               qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

-- Top queries by average logical reads (I/O-heavy queries)
SELECT TOP 10
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000 AS AvgCPUMs,
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgElapsedMs,
    DB_NAME(st.dbid)                        AS DatabaseName,
    LEFT(st.text, 300)                      AS QueryText
FROM sys.dm_exec_query_stats               qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AvgLogicalReads DESC;

-- Top queries by average elapsed time (slowest per execution)
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000 AS AvgElapsedMs,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count / 1000   AS AvgCPUMs,
    qs.total_logical_reads / qs.execution_count         AS AvgLogicalReads,
    DB_NAME(st.dbid)                                    AS DatabaseName,
    LEFT(st.text, 300)                                  AS QueryText
FROM sys.dm_exec_query_stats                            qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)         st
ORDER BY AvgElapsedMs DESC;

7 Plan Cache and Ad-Hoc Bloat Intermediate

Ad-hoc queries that run once and never reuse their plan consume memory in the plan cache. On busy OLTP servers this can consume gigabytes of memory holding plans that will never be used again. This is the query Erik Darling calls the classic plan cache bloat diagnostic.

-- Find single-use ad-hoc plans consuming plan cache memory
SELECT TOP 20
    cp.usecounts,
    cp.size_in_bytes / 1024             AS SizeKB,
    cp.objtype,
    LEFT(st.text, 300)                  AS QueryText
FROM sys.dm_exec_cached_plans           cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE cp.objtype = 'Adhoc'
AND   cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

-- Summary: how much cache is single-use ad-hoc vs reused
SELECT
    cp.objtype,
    COUNT(*)                            AS PlanCount,
    SUM(cp.size_in_bytes) / 1024 / 1024 AS TotalMB,
    SUM(CASE WHEN cp.usecounts = 1 THEN cp.size_in_bytes ELSE 0 END)
        / 1024 / 1024                   AS SingleUseMB
FROM sys.dm_exec_cached_plans           cp
GROUP BY cp.objtype
ORDER BY TotalMB DESC;

-- Check if "optimize for ad hoc workloads" is enabled
-- This stores a stub on first execution and full plan on second,
-- reducing single-use cache bloat significantly
SELECT name, value_in_use
FROM sys.configurations
WHERE name = 'optimize for ad hoc workloads';

8 Index Usage and Missing Indexes Intermediate

-- Index usage: seeks, scans, lookups, and writes per index
-- Unused indexes (zero seeks/scans/lookups but writes > 0) are candidates for removal
SELECT
    OBJECT_NAME(ius.object_id)          AS TableName,
    i.name                              AS IndexName,
    i.type_desc                         AS IndexType,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    -- Indexes with zero reads but non-zero writes are write overhead with no read benefit
    CASE WHEN ius.user_seeks + ius.user_scans + ius.user_lookups = 0
         AND ius.user_updates > 0
         THEN 'REVIEW FOR REMOVAL'
         ELSE ''
    END                                 AS Flag
FROM sys.dm_db_index_usage_stats        ius
JOIN sys.indexes                        i
    ON ius.object_id = i.object_id
   AND ius.index_id  = i.index_id
WHERE ius.database_id = DB_ID()
AND   i.type_desc NOT IN ('HEAP')
ORDER BY (ius.user_seeks + ius.user_scans + ius.user_lookups) ASC,
         ius.user_updates DESC;

-- Missing index suggestions with impact score and ready-to-run CREATE INDEX
SELECT TOP 20
    OBJECT_NAME(mid.object_id)          AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.unique_compiles,
    migs.user_seeks,
    ROUND(migs.avg_total_user_cost
        * migs.avg_user_impact
        * (migs.user_seeks + migs.user_scans), 0) AS ImpactScore,
    -- Ready-to-run statement (validate before executing in production)
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id)
        + '_Missing ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '')
        + CASE WHEN mid.equality_columns IS NOT NULL
               AND mid.inequality_columns IS NOT NULL
               THEN ', ' ELSE '' END
        + ISNULL(mid.inequality_columns, '') + ')'
        + CASE WHEN mid.included_columns IS NOT NULL
               THEN ' INCLUDE (' + mid.included_columns + ')'
               ELSE '' END + ';'        AS SuggestedIndex
FROM sys.dm_db_missing_index_details    mid
JOIN sys.dm_db_missing_index_groups     mig  ON mig.index_handle = mid.index_handle
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
ORDER BY ImpactScore DESC;

9 File I/O Latency Intermediate

-- File I/O latency per database file
-- Run when PAGEIOLATCH or WRITELOG waits dominate the wait stats
SELECT
    DB_NAME(mf.database_id)             AS DatabaseName,
    mf.type_desc,
    mf.name                             AS LogicalName,
    vfs.num_of_reads,
    vfs.io_stall_read_ms,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    vfs.io_stall_read_ms
        / NULLIF(vfs.num_of_reads, 0)   AS AvgReadMs,
    vfs.io_stall_write_ms
        / NULLIF(vfs.num_of_writes, 0)  AS AvgWriteMs,
    mf.physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files                   mf
    ON vfs.database_id = mf.database_id
   AND vfs.file_id     = mf.file_id
ORDER BY AvgReadMs DESC, AvgWriteMs DESC;

-- Rough latency benchmarks for context:
-- Data file reads:  under 5ms = fast,  5-20ms = acceptable, 20ms+ = investigate
-- Log file writes:  under 1ms = fast,  1-5ms  = acceptable, 5ms+  = investigate
-- TempDB files:     under 5ms = fast,  5ms+   = investigate for spills

10 Workshop: Build a Daily DMV Health Snapshot Advanced

The goal of this workshop is a stored procedure that captures the key DMV signals every day and saves them to history tables. With 30 or 60 days of snapshots, trends become visible: a gradual increase in PAGEIOLATCH waits, a new query appearing in the top CPU list, an index that suddenly starts getting zero seeks. These trends are invisible if DMV data is only ever read in the moment.

-- Step 1: Create history tables to persist DMV snapshots
USE YourDBADatabase;
GO

CREATE TABLE dbo.WaitStatsHistory (
    SnapshotTime        DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    ServerName          NVARCHAR(128)   NOT NULL DEFAULT @@SERVERNAME,
    WaitType            NVARCHAR(60)    NOT NULL,
    WaitingTasksCount   BIGINT          NOT NULL,
    TotalWaitMs         BIGINT          NOT NULL,
    ResourceWaitMs      BIGINT          NOT NULL,
    SignalWaitMs        BIGINT          NOT NULL
);

CREATE TABLE dbo.TopQueryHistory (
    SnapshotTime        DATETIME2       NOT NULL DEFAULT SYSDATETIME(),
    ServerName          NVARCHAR(128)   NOT NULL DEFAULT @@SERVERNAME,
    DatabaseName        NVARCHAR(128)   NULL,
    TotalCPUMs          BIGINT          NOT NULL,
    ExecutionCount      BIGINT          NOT NULL,
    AvgCPUMs            BIGINT          NOT NULL,
    AvgLogicalReads     BIGINT          NOT NULL,
    QueryText           NVARCHAR(1000)  NULL
);

-- Step 2: Create the snapshot stored procedure
CREATE OR ALTER PROCEDURE dbo.usp_CaptureDMVSnapshot
AS
BEGIN
    SET NOCOUNT ON;

    -- Capture top wait types (excluding benign waits)
    INSERT INTO dbo.WaitStatsHistory
        (WaitType, WaitingTasksCount, TotalWaitMs, ResourceWaitMs, SignalWaitMs)
    SELECT TOP 15
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        wait_time_ms - signal_wait_time_ms,
        signal_wait_time_ms
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'SLEEP_TASK', 'WAITFOR', 'LAZYWRITER_SLEEP', 'SLEEP_DBSTARTUP',
        'SLEEP_DBTASK', 'SLEEP_TEMPDBSTARTUP', 'SLEEP_SYSTEMTASK',
        'DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
        'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'DBMIRROR_EVENTS_QUEUE',
        'SQLTRACE_BUFFER_FLUSH', 'REQUEST_FOR_DEADLOCK_SEARCH',
        'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'ONDEMAND_TASK_QUEUE',
        'CHECKPOINT_QUEUE', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'RESOURCE_QUEUE'
    )
    AND waiting_tasks_count > 0
    ORDER BY wait_time_ms DESC;

    -- Capture top 10 queries by total CPU
    INSERT INTO dbo.TopQueryHistory
        (DatabaseName, TotalCPUMs, ExecutionCount, AvgCPUMs, AvgLogicalReads, QueryText)
    SELECT TOP 10
        DB_NAME(st.dbid),
        qs.total_worker_time / 1000,
        qs.execution_count,
        qs.total_worker_time / qs.execution_count / 1000,
        qs.total_logical_reads / qs.execution_count,
        LEFT(st.text, 1000)
    FROM sys.dm_exec_query_stats          qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY qs.total_worker_time DESC;

END;
GO

-- Step 3: Schedule via SQL Agent (run daily at 8 AM)
-- Create a SQL Agent job with a T-SQL step:
-- EXEC YourDBADatabase.dbo.usp_CaptureDMVSnapshot;

-- Step 4: Review trends with this query
-- Shows wait type trends over the last 30 days
SELECT
    CAST(SnapshotTime AS DATE)          AS SnapshotDate,
    WaitType,
    AVG(TotalWaitMs)                    AS AvgTotalWaitMs,
    MAX(TotalWaitMs)                    AS MaxTotalWaitMs
FROM dbo.WaitStatsHistory
WHERE SnapshotTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY CAST(SnapshotTime AS DATE), WaitType
ORDER BY SnapshotDate DESC, AvgTotalWaitMs DESC;

-- Step 5: Optional cleanup to prevent unbounded growth
DELETE FROM dbo.WaitStatsHistory
WHERE SnapshotTime < DATEADD(DAY, -90, GETDATE());

DELETE FROM dbo.TopQueryHistory
WHERE SnapshotTime < DATEADD(DAY, -90, GETDATE());

References

• Microsoft Docs: System Dynamic Management Views
• Microsoft Docs: sys.dm_os_wait_stats
• Microsoft Docs: sys.dm_exec_query_stats
• Paul Randal (SQLskills): Wait Statistics, or Please Tell Me Where It Hurts
• Brent Ozar: First Responder Kit (Blitz scripts built on DMVs)
• Erik Darling: DMV scripts, plan cache analysis, and query tuning
• SQLYARD: When SQL Server Is Slow: Triage Playbook
• SQLYARD: SQL Server Blocking vs Deadlocks Guide
• SQLYARD: SQL Server Index Tuning Guide
• SQLYARD: A Beginner-to-Advanced Guide to Query Store