DMV Quick-Reference Cheat Sheet
Audit Tip: DMVs are cumulative since the last SQL restart. Always check uptime via
tempdb creation date before concluding a "Fire" is active.| Category | Primary DMV | DBA Duty / What to Look For |
|---|---|---|
| Wait Stats | sys.dm_os_wait_stats | Find the #1 bottleneck (CPU, Disk, or Network). Use DBCC SQLPERF to clear for current analysis. |
| Query Stats | sys.dm_exec_query_stats | Identify "Heavy Hitters." Cross-apply with sys.dm_exec_sql_text to see the exact code. |
| Missing Indexes | sys.dm_db_missing_index_details | Look for high "User Impact" scores. Equality columns should always be ordered before Inequality. |
| Fragmentation | sys.dm_db_index_physical_stats | Spot disorganized indexes (>30%). Rebuild for performance or Reorganize for maintenance. |
| IO Latency | sys.dm_io_virtual_file_stats | Expose bad hardware. Values >20ms indicate physical disks cannot keep up with VA workload. |
| TempDB | sys.dm_db_session_space_usage | Identify the specific SPID (Space Hog) filling the community scratchpad. |