Performance Tuning with SQL Server DMVs — A Deep Dive

Leave a Comment / Articles, DBA Checklist, Scripts / By

Summary

Dynamic Management Views (DMVs) are SQL Server’s diagnostic windows into performance internals. They show you where the server is waiting, which queries burn CPU and I/O, which indexes are helpful or wasteful, and how execution plans are being reused (or abused). DMVs are not magic dashboards and they’re not always perfectly accurate, but when used wisely they form the backbone of modern performance tuning.

This blog covers what DMVs are (and aren’t), why they matter, a ranked list of the most useful DMVs, practical example queries, and insights from SQL Server experts like Louis Davidson, Tim Ford, Brent Ozar, and Erik Darling.

What DMVs Are

  • System Views & Functions. They live under sys.dm_* and expose live engine data: waits, queries, cache, sessions, indexes, I/O, and more.
  • Scoped Permissions. You’ll need VIEW SERVER STATE (server-wide) or VIEW DATABASE STATE (per-database).
  • Introduced in 2005. They’ve grown into hundreds of views across categories, covering engine, query processor, storage, and memory.

What DMVs Are Not

  • Not gospel. As Brent Ozar and Erik Darling often stress, DMVs can reset after restarts, roll data up oddly, or even mislead. Kendra Little famously called them “DMVs that lie.”
  • Not a replacement for extended events or monitoring. They provide snapshots, not full historical traces.
  • Not fire-and-forget. They require context, correlation, and experienced interpretation.

Why Use DMVs?

  • Visibility: They expose data that Activity Monitor or sp_who2 can’t.
  • Scriptable: They’re T-SQL. You can automate baselines and monitoring.
  • Battle-tested: Davidson and Ford built a book around them, Ozar’s Blitz scripts rely on them, and Erik Darling teaches DMV-driven diagnostics in his query-tuning sessions.

Ranked DMV Cheat Sheet — Most Useful to Least

  1. sys.dm_exec_query_stats
    • Aggregates execution stats for cached query plans: CPU, reads, duration, execution counts.
    • Why: The go-to for finding expensive queries.
  2. sys.dm_exec_sql_text + sys.dm_exec_query_plan
    • Show the SQL text and estimated execution plan.
    • Why: Critical for understanding what queries really do. Darling uses these to diagnose parameter sniffing and spills.
  3. sys.dm_os_wait_stats
    • Server-level wait times since restart.
    • Why: Brent Ozar’s starting point — tells you if pain is CPU, I/O, or locking.
  4. sys.dm_exec_requests
    • Currently running requests, with blocking session IDs, reads, CPU time.
    • Why: Real-time snapshot of who’s active and hogging resources.
  5. sys.dm_os_waiting_tasks
    • Shows tasks actively waiting for resources.
    • Why: Complements wait_stats with what’s blocking right now.
  6. sys.dm_db_index_usage_stats
    • Tracks seeks, scans, lookups, and writes per index.
    • Why: Spot unused indexes (drop them) and critical ones (tune them).
  7. sys.dm_db_missing_index_details / sys.dm_db_missing_index_group_stats
    • Suggest missing indexes.
    • Why: Good hints, but Erik Darling warns they’re simplistic and don’t suggest covering indexes.
  8. sys.dm_io_virtual_file_stats
    • File-level read/write stats.
    • Why: Finds I/O bottlenecks — Ford highlights this for disk analysis.
  9. sys.dm_exec_cached_plans
    • Shows cached plans with use counts and memory size.
    • Why: Identifies cache bloat from ad-hoc queries.
  10. sys.dm_exec_sessions
    • Lists active sessions with login times and resource settings.
    • Why: Combine with requests to trace problems to users/apps.
  11. sys.dm_exec_input_buffer
    • Shows the last statement executed by a session.
    • Why: Handy for ad-hoc troubleshooting when you want to see what someone is running right now.
  12. sys.dm_exec_query_optimizer_info
    • Summarizes query optimizer activity.
    • Why: Rarely used daily, but useful for analyzing optimizer decisions at scale.
  13. sys.dm_db_partition_stats
    • Row counts and space usage for tables and indexes.
    • Why: Useful for quick table size checks when tuning.

Example DMV Queries

1. Cache Bloat from Ad-Hoc Queries

SELECT TOP 20
  cp.usecounts,
  cp.size_in_bytes / 1024 AS size_kb,
  st.text AS query_text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE cp.objtype = 'Adhoc'
  AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

👉 Darling’s classic: find junk ad-hoc queries and consider enabling OPTIMIZE FOR AD HOC WORKLOADS.

2. Wait Stats Baseline

SELECT TOP 10
  wait_type,
  waiting_tasks_count,
  total_wait_time_ms / 1000.0 AS total_wait_s,
  signal_wait_time_ms / 1000.0 AS signal_wait_s
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY total_wait_time_ms DESC;

👉 Ozar’s “step one” — tells you if the issue is CPU, memory, or disk.

3. Current Requests

SELECT
  r.session_id,
  r.status,
  r.command,
  r.cpu_time,
  r.total_elapsed_time,
  r.blocking_session_id,
  t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50; -- filter out system sessions
SELECT
  r.session_id,
  r.status,
  r.command,
  r.cpu_time,
  r.total_elapsed_time,
  r.blocking_session_id,
  t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50; -- filter out system sessions

👉 Great for “what’s slow right now” troubleshooting.

4. Expensive Queries by CPU

SELECT TOP 10
  qs.total_worker_time/1000 AS total_cpu_ms,
  qs.execution_count,
  qs.total_elapsed_time/1000 AS total_time_ms,
  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,
  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 total_cpu_ms DESC;

👉 Darling and Davidson both use this pattern to identify “bad neighbors.”

Expert Perspectives

  • Louis Davidson & Tim Ford: Their Redgate book remains the bible for systematic DMV use.
  • Brent Ozar: Blitz scripts rely heavily on DMVs for instant triage.
  • Erik Darling: Focuses on query-level DMVs (plans, cache, stats) and warns against misinterpreting them. His blog is full of DMV-driven examples.
  • SQLSkills (Paul Randal, Jonathan Kehayias): Deep-dive DMV analysis for waits, I/O, and memory.

Final Thoughts

DMVs aren’t optional — they’re how modern DBAs tune SQL Server. But you must interpret them wisely. Start broad (wait stats, requests), then dive into query stats, plans, and index usage. Build a baseline library of DMV queries and revisit them often.

Follow Davidson & Ford for structured frameworks, Ozar for triage, Darling for plan/cache insight, and SQLSkills for deep engine internals. Use DMVs not just to put out fires, but to spot patterns before they become outages.

Reference Guide

Workshop: DMV-Driven Performance Diagnostics

Objective:
Walk through a hands-on exercise using DMVs to identify slow queries, blocking sessions, and missing indexes — then visualize findings for a daily health report.

Step 1: Preparation

  • Ensure you have VIEW SERVER STATE permission.
  • Use a non-production SQL Server instance (or restore a sample DB like AdventureWorks).
  • Clear the cache to simulate fresh workload data:
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

Step 2: Generate Some Activity

Run a few queries to produce workload noise:

USE AdventureWorks2022;
SELECT * FROM Sales.SalesOrderHeader;
SELECT TOP 1000 * FROM Person.Person ORDER BY LastName;
SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE ProductID > 500;

Step 3: Identify Expensive Queries

Run:

SELECT TOP 5
  total_worker_time/1000 AS total_cpu_ms,
  execution_count,
  total_elapsed_time/1000 AS total_time_ms,
  SUBSTRING(text, statement_start_offset/2 + 1,
    (CASE WHEN statement_end_offset = -1
      THEN DATALENGTH(text)
      ELSE statement_end_offset END - statement_start_offset)/2 + 1) AS query_text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY total_worker_time DESC;

➡ Find your top CPU-heavy queries. Note which tables and indexes they touch.

Step 4: Check for Wait Bottlenecks

Run the baseline query:

SELECT TOP 10 wait_type, waiting_tasks_count, total_wait_time_ms / 1000 AS total_wait_s
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
ORDER BY total_wait_time_ms DESC;

➡ Identify if you’re CPU, I/O, or memory bound.

  • CXPACKET / CXCONSUMER → parallelism
  • PAGEIOLATCH_ → I/O pressure
  • LCK_ → locking

Step 5: Find Active Blockers

SELECT
  r.session_id,
  r.status,
  r.blocking_session_id,
  t.text AS running_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

➡ See which sessions are blocking others and what commands they’re executing.

Step 6: Index Health Check

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

➡ Look for indexes with zero seeks but high writes — candidates for removal.

Step 7: Capture Missing Indexes

➡ Note that these are hints, not gospel. Validate before creating indexes.

Step 8: Build a Daily Health Snapshot

Combine findings into a stored procedure:

CREATE OR ALTER PROC dbo.DailyDMVReport AS
BEGIN
  SELECT TOP 5 * FROM sys.dm_os_wait_stats ORDER BY total_wait_time_ms DESC;
  SELECT TOP 5 * FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;
  SELECT TOP 5 * FROM sys.dm_db_missing_index_group_stats ORDER BY avg_total_user_cost DESC;
END;

Schedule this as a SQL Agent job and export results to a table for trend tracking.

Step 9: Visualize in Power BI or Excel

Connect to the DMV output table and create charts for:

  • Top Wait Types Over Time
  • Top CPU Queries by Database
  • Index Usage Heatmap

Outcome:
By completing this workshop, you’ll understand how to use DMVs for real-time and historical diagnostics, identify performance bottlenecks, and automate daily health reports — the same workflow used by Brent Ozar and SQLSkills for production tuning.

Discover more from SQLyard

Subscribe to get the latest posts sent to your email.

Related Posts

Leave a Reply

Contact us
Contact us

Discover more from SQLyard

Subscribe now to keep reading and get access to the full archive.

Continue reading