Essential DMV Queries for SQL Server Performance Monitoring

Dynamic Management Views (DMVs) are powerful built-in tools in SQL Server that provide real-time insights into database performance, resource usage, and system health. This comprehensive guide covers the most essential DMV queries that every DBA should know to monitor and optimize SQL Server performance effectively.

Table of Contents

1. Introduction to Dynamic Management Views

Dynamic Management Views (DMVs) have become an indispensable tool for database administrators. Unlike traditional system tables, DMVs provide dynamic, real-time information about the internal state of SQL Server.

What Are DMVs?

DMVs are system views that return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. They provide visibility into:

  • Query execution statistics and plans
  • Memory usage and buffer pool status
  • CPU and I/O consumption
  • Blocking and locking information
  • Index usage patterns
  • Wait statistics

2. CPU Performance Monitoring

2.1 Top CPU-Consuming Queries (Cached in memory)

This query returns information about queries that have been executed and are currently cached in memory. It shows the top 20 CPU-intensive queries that have completed execution. Note that this does not show currently executing queries.

SELECT TOP 20
    qs.total_worker_time / qs.execution_count AS avg_cpu_time_ms,
    qs.total_worker_time AS total_cpu_time_ms,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time_ms,
    SUBSTRING(
        qt.text,
        (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1
    ) AS query_text,
    DB_NAME(qt.dbid) AS database_name,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Understanding the Results:

Column Description
avg_cpu_time_ms Average CPU time per execution in milliseconds
total_cpu_time_ms Total CPU time consumed by all executions
execution_count Number of times the query has been executed
query_text The actual SQL query text

2.2 Currently Executing High CPU Queries

To identify queries that are currently executing and consuming high CPU, use the following query:

SELECT 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time AS current_cpu_time_ms,
    r.total_elapsed_time AS elapsed_time_ms,
    r.logical_reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS database_name,
    s.host_name,
    s.program_name,
    s.login_name,
    SUBSTRING(
        qt.text,
        (r.statement_start_offset/2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset)/2) + 1
    ) AS current_query_text,
    qp.query_plan
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s 
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID  -- Exclude current session
    AND s.is_user_process = 1  -- Only user processes
ORDER BY r.cpu_time DESC;

💡 Tip: This query is extremely useful when you notice sudden CPU spikes. It shows what’s happening right now, not what happened in the past.

3. Memory and Buffer Pool Analysis

3.1 Buffer Pool Usage by Database

SQL Server caches data pages in memory (buffer pool) to reduce disk I/O. This query shows how much memory each database is using in the buffer pool:

SELECT 
    CASE database_id
        WHEN 32767 THEN 'ResourceDB'
        ELSE DB_NAME(database_id)
    END AS database_name,
    COUNT(*) * 8 / 1024 AS buffer_pool_mb,
    COUNT(*) AS page_count
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY buffer_pool_mb DESC;

3.2 Memory Clerks Analysis

This query shows how SQL Server allocates memory within the max server memory setting. You can identify which components are consuming memory:

SELECT TOP 20
    [type] AS clerk_type,
    SUM(pages_kb) / 1024 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY memory_mb DESC;

3.3 Overall Server Memory Status

Get a comprehensive view of SQL Server memory usage:

SELECT 
    total_physical_memory_kb / 1024 AS total_physical_memory_mb,
    available_physical_memory_kb / 1024 AS available_physical_memory_mb,
    total_page_file_kb / 1024 AS total_page_file_mb,
    available_page_file_kb / 1024 AS available_page_file_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

4. Identifying Slow and Expensive Queries

4.1 Longest Running Queries (Cached in memory)

Find queries with the highest average execution time. This returns information about completed queries cached in memory:

SELECT TOP 20
    qs.execution_count,
    qs.total_elapsed_time / 1000000.0 AS total_elapsed_time_sec,
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_time_ms,
    qs.total_worker_time / 1000000.0 AS total_worker_time_sec,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_physical_reads,
    SUBSTRING(
        qt.text,
        (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1
    ) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_elapsed_time / qs.execution_count DESC;

4.2 Queries with High Logical Reads (Cached in memory)

Logical reads indicate how much data is being read from memory. High values suggest inefficient queries or missing indexes. This also returns information about completed queries:

SELECT TOP 20
    qs.execution_count,
    qs.total_logical_reads,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_logical_writes,
    qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_time_ms,
    SUBSTRING(
        qt.text,
        (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1
    ) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_logical_reads DESC;

💡 Best Practice: Queries with high logical reads (typically >10,000 per execution) are prime candidates for optimization through indexing, query rewriting, or schema changes.

5. Blocking and Wait Statistics

5.1 Current Blocking Sessions

Quickly identify blocking status in your SQL Server instance.

-- blocking sessions
SELECT
  r.session_id          AS blocked_session_id,
  r.blocking_session_id AS blocking_session_id,
  r.status              AS blocked_status,
  r.command             AS blocked_command,
  DB_NAME(r.database_id) AS database_name,
  r.wait_type,
  r.wait_time           AS wait_time_ms,
  r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC, r.blocking_session_id, r.session_id;

This query shows both the blocking sessions and the blocked sessions, providing a complete view of the blocking chain:

-- Complete blocking chain view
WITH BlockingChain AS (
    SELECT 
        r.session_id AS blocked_session_id,
        r.blocking_session_id,
        r.wait_type,
        r.wait_time AS wait_time_ms,
        r.wait_resource,
        DB_NAME(r.database_id) AS database_name,
        r.status AS blocked_status,
        r.command AS blocked_command,
        s.host_name AS blocked_host,
        s.program_name AS blocked_program,
        s.login_name AS blocked_login,
        t.text AS blocked_query
    FROM sys.dm_exec_requests AS r
    INNER JOIN sys.dm_exec_sessions AS s
        ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    WHERE r.blocking_session_id > 0
)
SELECT 
    bc.blocking_session_id,
    bs.status AS blocking_status,
    blocking_req.command AS blocking_command,
    bs.host_name AS blocking_host,
    bs.program_name AS blocking_program,
    bs.login_name AS blocking_login,
    blocking_text.text AS blocking_query,
    bc.blocked_session_id,
    bc.blocked_status,
    bc.blocked_command,
    bc.blocked_host,
    bc.blocked_program,
    bc.blocked_login,
    bc.blocked_query,
    bc.wait_type,
    bc.wait_time_ms,
    bc.wait_resource,
    bc.database_name
FROM BlockingChain AS bc
INNER JOIN sys.dm_exec_sessions AS bs
    ON bc.blocking_session_id = bs.session_id
LEFT JOIN sys.dm_exec_requests AS blocking_req
    ON bc.blocking_session_id = blocking_req.session_id
OUTER APPLY sys.dm_exec_sql_text(blocking_req.sql_handle) AS blocking_text
ORDER BY bc.wait_time_ms DESC;

This query shows both the blocking query and the blocked query.

-- Alternative simpler view showing just key information
SELECT 
    r.blocking_session_id AS [Blocking SPID],
    r.session_id AS [Blocked SPID],
    r.wait_type AS [Wait Type],
    r.wait_time AS [Wait Time (ms)],
    r.wait_resource AS [Wait Resource],
    DB_NAME(r.database_id) AS [Database],
    blocked_session.program_name AS [Blocked Program],
    blocking_session.program_name AS [Blocking Program],
    SUBSTRING(blocked_text.text, 
        (r.statement_start_offset/2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(blocked_text.text)
            ELSE r.statement_end_offset
        END - r.statement_start_offset)/2) + 1
    ) AS [Blocked Query],
    blocking_text.text AS [Blocking Query]
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS blocked_session
    ON r.session_id = blocked_session.session_id
INNER JOIN sys.dm_exec_sessions AS blocking_session
    ON r.blocking_session_id = blocking_session.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS blocked_text
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT TOP 1 sql_handle 
     FROM sys.dm_exec_connections 
     WHERE session_id = r.blocking_session_id)
) AS blocking_text
WHERE r.blocking_session_id > 0
ORDER BY r.wait_time DESC;

Understanding the Results:

Column Description
blocking_session_id / Blocking SPID The session ID that is causing the block
blocked_session_id / Blocked SPID The session ID that is being blocked
blocking_query The query being executed by the blocking session (may be NULL if idle)
blocked_query The query waiting to execute in the blocked session
wait_time_ms How long the session has been blocked (in milliseconds)

💡 Tip: The blocking session might not have an active query if it’s holding locks from a previous statement within an open transaction. In such cases, the blocking_query will be NULL, but you can investigate the transaction status using sys.dm_tran_session_transactions.

5.2 Wait Statistics Analysis

Understand where SQL Server is spending time waiting. This is crucial for diagnosing performance issues:

SELECT TOP 20
    wait_type,
    wait_time_ms,
    wait_time_ms * 100.0 / SUM(wait_time_ms) OVER() AS percentage,
    waiting_tasks_count,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Common Wait Types and Their Meanings:

Wait Type Description Common Causes
PAGEIOLATCH_* Waiting for data pages to be read from disk Slow I/O subsystem, insufficient memory
LCK_M_* Lock waits Blocking, long transactions, poor indexing
CXPACKET Parallel query coordination waits Unbalanced parallelism, consider MAXDOP tuning
SOS_SCHEDULER_YIELD CPU pressure Insufficient CPU, inefficient queries
WRITELOG Waiting for transaction log writes Slow log disk, high transaction volume

6. Index Usage and Missing Indexes

6.1 Missing Index Recommendations

SQL Server tracks missing indexes that could improve query performance. This query shows the most impactful missing indexes:

SELECT TOP 20
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id, mid.database_id) + '_' 
        + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
        + CASE WHEN mid.inequality_columns IS NOT NULL THEN '_' + 
            REPLACE(REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', ''), ']', '') ELSE '' END
        + ' ON ' + mid.statement
        + ' (' + ISNULL(mid.equality_columns, '') 
        + CASE WHEN mid.inequality_columns IS NOT NULL 
            THEN CASE WHEN mid.equality_columns IS NOT NULL THEN ',' ELSE '' END + mid.inequality_columns 
            ELSE '' END + ')'
        + CASE WHEN mid.included_columns IS NOT NULL 
            THEN ' INCLUDE (' + mid.included_columns + ')' ELSE '' END AS create_index_statement,
    migs.user_seeks,
    migs.user_scans,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details AS mid
INNER JOIN sys.dm_db_missing_index_groups AS mig 
    ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS migs 
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

6.2 Unused Indexes Detection

Identify indexes that are never or rarely used. These indexes consume storage and slow down write operations:

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 AS index_type,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ps.row_count,
    ps.used_page_count * 8 / 1024 AS index_size_mb
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = DB_ID()
INNER JOIN sys.dm_db_partition_stats AS ps
    ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.type_desc NOT IN ('HEAP', 'CLUSTERED')
    AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND (ius.user_seeks + ius.user_scans + ius.user_lookups = 0 OR ius.user_seeks IS NULL)
    AND ius.user_updates > 0
ORDER BY ius.user_updates DESC, index_size_mb DESC;

⚠️ Caution: Before dropping any index, verify it’s not required for constraints, used by specific maintenance windows, or critical for disaster recovery scenarios. Test in a non-production environment first.

7. I/O Performance Statistics

7.1 Database File I/O Statistics

Identify I/O bottlenecks at the file level:

SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.name AS file_logical_name,
    mf.type_desc AS file_type,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.num_of_bytes_read / 1024 / 1024 AS read_mb,
    vfs.io_stall_read_ms,
    CASE 
        WHEN vfs.num_of_reads = 0 THEN 0
        ELSE vfs.io_stall_read_ms / vfs.num_of_reads
    END AS avg_read_latency_ms,
    vfs.num_of_writes,
    vfs.num_of_bytes_written / 1024 / 1024 AS written_mb,
    vfs.io_stall_write_ms,
    CASE 
        WHEN vfs.num_of_writes = 0 THEN 0
        ELSE vfs.io_stall_write_ms / vfs.num_of_writes
    END AS avg_write_latency_ms,
    vfs.io_stall_read_ms + vfs.io_stall_write_ms AS total_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

I/O Latency Guidelines:

  • Excellent: < 5ms average latency
  • Good: 5-10ms average latency
  • Acceptable: 10-20ms average latency
  • Poor: 20-50ms average latency
  • Critical: > 50ms average latency

7.2 TempDB Performance

Monitor TempDB usage and contention. Note that you must be in the TempDB context to query sys.database_files:

-- TempDB file usage (must USE TempDB first or specify database context)
USE TempDB;
GO

SELECT 
    name AS file_name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
    size * 8 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS free_mb
FROM sys.database_files;

-- TempDB contention check
SELECT 
    session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
    AND resource_description LIKE '2:%'  -- Database ID 2 is TempDB
ORDER BY wait_duration_ms DESC;

8. Best Practices and Tips

8.1 Creating a Baseline

Establish performance baselines by capturing DMV data during normal operations:

-- Create a baseline table
CREATE TABLE dbo.DMV_Baseline (
    capture_time DATETIME2 DEFAULT GETDATE(),
    metric_name NVARCHAR(100),
    metric_value DECIMAL(18,2),
    additional_info NVARCHAR(MAX)
);

-- Sample baseline capture
INSERT INTO dbo.DMV_Baseline (metric_name, metric_value, additional_info)
SELECT 
    'CPU_Percentage',
    AVG(CAST(100.0 * runnable_tasks_count / (current_tasks_count + 1) AS DECIMAL(5,2))),
    'Average CPU pressure across all schedulers'
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

8.2 Scheduled Monitoring Script

Create a comprehensive monitoring stored procedure:

CREATE OR ALTER PROCEDURE dbo.sp_MonitorSQLServerHealth
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. Check for blocking
    SELECT 'BLOCKING CHECK' AS check_type;
    SELECT blocking_session_id, COUNT(*) AS blocked_sessions
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0
    GROUP BY blocking_session_id;

    -- 2. Top CPU queries
    SELECT 'TOP CPU QUERIES' AS check_type;
    SELECT TOP 5
        total_worker_time / execution_count / 1000.0 AS avg_cpu_ms,
        execution_count,
        SUBSTRING(qt.text, 1, 100) AS query_sample
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    ORDER BY total_worker_time / execution_count DESC;

    -- 3. Wait statistics summary
    SELECT 'WAIT STATISTICS' AS check_type;
    SELECT TOP 5
        wait_type,
        wait_time_ms / 1000.0 AS wait_time_sec,
        waiting_tasks_count
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT LIKE 'SLEEP%'
        AND wait_type NOT LIKE 'BROKER%'
    ORDER BY wait_time_ms DESC;

    -- 4. Memory usage
    SELECT 'MEMORY USAGE' AS check_type;
    SELECT 
        total_physical_memory_kb / 1024 AS total_memory_mb,
        available_physical_memory_kb / 1024 AS available_memory_mb,
        system_memory_state_desc
    FROM sys.dm_os_sys_memory;
END;
GO

8.3 Regular Maintenance Tasks

💡 Recommended Maintenance Schedule:

  • Daily: Monitor blocking, CPU usage, wait statistics
  • Weekly: Review slow queries, check index usage, analyze I/O performance
  • Monthly: Evaluate missing indexes, identify unused indexes, review growth patterns
  • Quarterly: Baseline comparison, capacity planning review

8.4 Resetting DMV Statistics

In some scenarios, you may need to clear DMV statistics to start fresh monitoring:

-- Clear wait statistics
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Clear procedure cache (use with caution!)
-- This will clear query statistics and execution plans
-- DBCC FREEPROCCACHE;

⚠️ Warning: Clearing the procedure cache (DBCC FREEPROCCACHE) forces SQL Server to recompile all queries and can cause temporary performance degradation. Only use this in controlled environments or during maintenance windows.

Conclusion

Dynamic Management Views are essential tools for every SQL Server DBA. By mastering these queries, you can proactively monitor performance, quickly diagnose issues, and optimize your SQL Server instances effectively. Remember to:

  • Establish baselines for comparison
  • Monitor trends over time, not just point-in-time snapshots
  • Combine multiple DMVs for comprehensive analysis
  • Document your findings and optimizations
  • Test changes in non-production environments first

These DMV queries form the foundation of effective SQL Server performance monitoring. As you become more familiar with them, you’ll be able to customize and combine them to address your specific monitoring needs.