5 SQL Server DMVs Every DBA Should Know for Performance Tuning

Extended Events (previous lesson) capture what happened over a time window you chose to record. Dynamic Management Views answer a different, often more urgent question: what is the state of the server right now, and what has it accumulated since the last restart — no trace setup required, just a SELECT.

1. Top Queries by Logical Reads

SELECT TOP 10
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    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 query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_logical_reads DESC;

This DMV accumulates statistics since the plan was cached, across every execution — which makes it fundamentally different from a single execution plan (Lesson 3): it answers “which query costs the most in aggregate,” not “why is this one specific run slow.”

2. What’s Running Right Now

SELECT session_id, status, command, wait_type, wait_time, blocking_session_id, total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id > 50; -- excludes internal system sessions

A non-NULL blocking_session_id here is one of the most actionable single columns in this entire toolkit — it directly identifies which session is blocking which, the starting point for diagnosing the blocking scenarios covered fully in Chapter 9.

3. What the Server Is Waiting On

SELECT TOP 10 wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;

This is server-wide, cumulative since the last restart or manual reset — a genuinely powerful “what’s the bottleneck category, in general” question. High PAGEIOLATCH_* waits point toward disk I/O pressure; high CXPACKET/CXCONSUMER points toward parallelism; high LCK_M_* points toward blocking. This single query is often the very first thing a DBA runs when investigating “the server feels slow.”

4. Which Indexes Are Actually Used

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
    s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID();

The Quick Reference

A high-value pattern: user_updates high but user_seeks + user_scans + user_lookups near zero identifies an index that costs on every write but never helps a read — a strong candidate to drop. This single comparison is one of the most reliably useful index-cleanup queries a DBA runs, because it’s the exact opposite of the covering-index tuning from Lesson 2: a genuinely wasted index, paid for on every INSERT/UPDATE, that no query ever benefits from.