Here’s the thing nobody tells you when you’re starting out with Dynamic Management Views: you don’t need all 200+ of them. You need about 30 that you reach for so often they become muscle memory — the ones you’d write half-asleep during a 2 AM incident because you’ve run them a thousand times before.
This is that list. Grouped the way I actually think about them — live activity, query performance, Query Store, resource pressure, waits and blocking, indexing, and storage — with a real query for each, the permission you actually need to run it, and a real-world scenario for when you’d reach for it.
One quick note on permissions before we start: almost everything on this list needs the VIEW DATABASE STATE permission on the database (Azure SQL Database doesn’t have server-level access, so the old on-prem VIEW SERVER STATE habit doesn’t apply here — it’s database-scoped instead). Members of db_owner already have this implicitly. If you’re setting up a read-only monitoring account, granting VIEW DATABASE STATE explicitly is usually the one line you need:
GRANT VIEW DATABASE STATE TO [monitoring_user];
I’ll flag the handful of DMVs that need something different.
Live Activity & Sessions
1. sys.dm_exec_sessions
What it shows: Every currently connected session — login name, host, program, login time, status.
Permission needed: VIEW DATABASE STATE
When to use: Your “who’s actually connected right now” check.
SELECT session_id, login_name, host_name, program_name, status, login_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
ORDER BY login_time DESC;
Real-world example: A client once asked “is our reporting tool actually using a service account, or are people still connecting with personal logins?” This query answered it in ten seconds — program_name and login_name showed three different personal accounts still hitting production directly, which is exactly the kind of access-hygiene problem you want to catch before it becomes a security review finding.
2. sys.dm_exec_connections
What it shows: Network-level connection detail — client IP, protocol, encryption status.
Permission needed: VIEW DATABASE STATE
When to use: Troubleshooting an unfamiliar connection source or verifying encryption is actually enforced.
SELECT session_id, client_net_address, connect_time, encrypt_option
FROM sys.dm_exec_connections;
Real-world example: During a security audit, we needed to prove every connection was TLS-encrypted. encrypt_option gave us that evidence directly from the database, without needing network-level packet capture.
3. sys.dm_exec_requests
What it shows: Every currently executing request — status, wait type, blocking session, CPU time, elapsed time.
Permission needed: VIEW DATABASE STATE
When to use: The absolute first stop the moment someone says “the database is slow right now.”
SELECT r.session_id, r.status, r.command, r.wait_type, r.blocking_session_id,
r.cpu_time, r.total_elapsed_time, t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50
ORDER BY r.total_elapsed_time DESC;
Real-world example: “The app is frozen” turned out, via this query, to be one session sitting at wait_type = LCK_M_X for eleven minutes, blocked by a batch job someone forgot was still running from that morning. Found and killed in under a minute once we had this.
4. sys.dm_exec_sql_text (function)
What it shows: The actual query text behind a sql_handle.
Permission needed: VIEW DATABASE STATE
When to use: Always paired with dm_exec_requests or dm_exec_query_stats via CROSS APPLY — you’ll almost never call it alone.
5. sys.dm_exec_input_buffer (function)
What it shows: The last command a specific session sent.
Permission needed: VIEW DATABASE STATE
When to use: A session shows up blocked or idle in dm_exec_requests with no current statement — this tells you what it was doing.
SELECT * FROM sys.dm_exec_input_buffer(62, 0);
Real-world example: A session sat “idle in transaction” holding locks for twenty minutes. dm_exec_requests showed nothing running. This function revealed the last command was a BEGIN TRAN with no matching commit — a connection pooling bug in the app, not a database problem.
Query Performance & Plans
6. sys.dm_exec_query_stats
What it shows: Aggregated stats for every cached plan — total/average CPU, logical reads, execution count.
Permission needed: VIEW DATABASE STATE
When to use: “What are the most expensive queries right now” without waiting on Query Store.
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu,
qs.execution_count, t.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY avg_cpu DESC;
Real-world example: CPU was pegged at 90% with no single obviously runaway query in dm_exec_requests. This view showed the real cause: a moderately-priced query running 40,000 times an hour, dwarfing everything else in total consumption despite looking cheap per-execution.
7. sys.dm_exec_query_plan (function)
What it shows: The actual XML execution plan for a given plan_handle.
Permission needed: VIEW DATABASE STATE
When to use: Right after identifying an expensive query — click the result in SSMS to see the graphical plan.
8. sys.dm_exec_cached_plans
What it shows: What’s currently sitting in the plan cache — size, use count, object type.
Permission needed: VIEW DATABASE STATE
When to use: Suspected plan cache bloat from non-parameterized ad hoc queries.
SELECT objtype, COUNT(*) AS plan_count, SUM(size_in_bytes)/1024/1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;
Real-world example: A .NET app built with string-concatenated SQL instead of parameters was generating a fresh single-use plan on nearly every call. This query showed thousands of Adhoc plans consuming memory that should’ve gone to genuinely reused plans — the fix was sp_executesql parameterization, but this is what proved the diagnosis.
9. sys.dm_exec_procedure_stats
What it shows: Same as query stats, but aggregated per stored procedure as a whole.
Permission needed: VIEW DATABASE STATE
When to use: You want to know which procedure is the resource hog, not just which line inside it.
10. sys.dm_exec_query_memory_grants
What it shows: Queries currently waiting for or holding a memory grant.
Permission needed: VIEW DATABASE STATE (also needs VIEW SERVER STATE-equivalent granted at the appropriate scope for full detail in some configurations — but standard VIEW DATABASE STATE covers the typical case)
When to use: Chasing a RESOURCE_SEMAPHORE wait or a tempdb spill.
SELECT session_id, requested_memory_kb, granted_memory_kb, wait_time_ms
FROM sys.dm_exec_query_memory_grants;
Real-world example: A nightly ETL job started randomly timing out. This showed five concurrent sessions all requesting large memory grants simultaneously — the batch window had drifted to overlap with another team’s report refresh, and they were genuinely fighting over the same memory pool.
Query Store
11. sys.query_store_query
What it shows: Every distinct query Query Store has captured, with first-seen metadata.
Permission needed: VIEW DATABASE STATE
When to use: Your entry point into everything else Query Store tracks.
12. sys.query_store_runtime_stats
What it shows: Performance numbers over time per query per plan — duration, CPU, reads.
Permission needed: VIEW DATABASE STATE
When to use: Proving a query genuinely got slower and pinpointing exactly when.
SELECT q.query_id, rs.avg_duration, rs.avg_cpu_time, rs.runtime_stats_interval_start_time
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
WHERE rs.runtime_stats_interval_start_time > DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;
Real-world example: A developer swore “nothing changed” the day a report started timing out. This query showed the average duration jumping from 200ms to 4 seconds at 2:15 AM the previous night — right when an overnight index maintenance job had rebuilt an index and, as a side effect, triggered a statistics update that flipped the query’s plan.
13. sys.query_store_wait_stats
What it shows: Wait statistics tied to specific queries and time intervals.
Permission needed: VIEW DATABASE STATE
When to use: You need to know exactly which query is racking up a specific wait type, not just that the wait is happening somewhere on the server.
14. sys.dm_db_tuning_recommendations
What it shows: What Automatic Tuning has recommended or applied.
Permission needed: VIEW DATABASE STATE
When to use: Checking whether Automatic Tuning silently created or dropped an index recently.
SELECT name, type, state, reason
FROM sys.dm_db_tuning_recommendations;
Real-world example: A table’s write latency crept up over a month. This view showed Automatic Tuning had created three separate indexes on it over that period, each individually justified but collectively adding real write overhead nobody had reviewed holistically.
Resource Pressure (Azure-Specific)
15. sys.dm_db_resource_stats
What it shows: CPU%, data I/O%, log write%, and memory% at 15-second intervals for the last hour.
Permission needed: VIEW DATABASE STATE
When to use: The single most useful DMV for “was there actually resource pressure at 2 PM.” My genuine first move on almost every incident.
SELECT end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;
Real-world example: A client insisted their database “needed a bigger tier” during a slow period. This query showed CPU comfortably under 30% the entire time — the real bottleneck turned out to be blocking, not capacity, saving them an unnecessary scale-up.
16. sys.resource_stats
What it shows: The longer-history cousin of the above, at 5-minute intervals, held in master.
Permission needed: VIEW DATABASE STATE on the master database specifically
When to use: Trend analysis over weeks, not just the last hour.
Real-world example: A capacity planning conversation needed to show CPU trending upward over six weeks to justify a tier upgrade budget request — dm_db_resource_stats‘s one-hour window couldn’t show that trend, but sys.resource_stats could.
17. sys.dm_user_db_resource_governor
What it shows: The actual resource governance limits currently applied — CPU cap, IOPS, worker and session limits.
Permission needed: VIEW DATABASE STATE
When to use: Confirming a scale operation genuinely took effect, or checking exactly what ceiling you’re hitting.
SELECT * FROM sys.dm_user_db_resource_governor;
Real-world example: After a scale-up, performance didn’t improve. This view confirmed the new limits hadn’t actually applied yet — the operation had reported success in the portal but was still propagating.
18. sys.database_connection_stats
What it shows: Connection successes, failures, and terminations over rolling windows.
Permission needed: VIEW DATABASE STATE
When to use: The portal’s connection_failed metric is climbing and you want the breakdown of why.
Real-world example: Failed connections spiked overnight. This view’s failure reason breakdown pointed straight at throttling from hitting the session limit — not a firewall or credential issue, which saved a lot of wrong-direction troubleshooting.
Waits & Blocking
19. sys.dm_os_wait_stats
What it shows: Cumulative, database-wide wait statistics since the last restart.
Permission needed: VIEW DATABASE STATE
When to use: Your top-level “where is this database spending its time” view — check this before diving into anything query-specific.
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;
Real-world example: LOG_RATE_GOVERNOR sitting at the top of this list was the first clue that a nightly bulk load was being throttled against the service tier’s log throughput cap — an Azure-specific constraint the on-prem-trained DBA on the team had never encountered before.
20. sys.dm_exec_session_wait_stats
What it shows: The same idea as above, scoped to one specific session.
Permission needed: VIEW DATABASE STATE
When to use: Isolating exactly what one problematic session is waiting on, without server-wide noise.
21. sys.dm_os_waiting_tasks
What it shows: Tasks waiting right now, including the resource and the blocking session.
Permission needed: VIEW DATABASE STATE
When to use: The live, moment-in-time complement to cumulative wait stats.
SELECT session_id, wait_type, wait_duration_ms, blocking_session_id, resource_description
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;
Real-world example: Five sessions were all blocked, and four of them were blocked by each other in a chain. This query traced the chain back to one genuine head blocker — a long-running report someone kicked off without realizing the table was mid-update.
22. sys.dm_tran_locks
What it shows: Every currently held and requested lock — resource type, mode, holding session.
Permission needed: VIEW DATABASE STATE
When to use: Once you know who’s blocked, this shows exactly what they’re fighting over.
SELECT request_session_id, resource_type, resource_database_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';
Real-world example: A batch update was blocking reads across an entire table. This view showed the update had escalated to a table-level lock (not just row-level, as expected) because the batch size exceeded the lock escalation threshold — the actual fix was smaller batches, not a code review of the query logic itself.
Indexes & Statistics
23. sys.dm_db_index_usage_stats
What it shows: Seeks, scans, lookups, and updates per index since the stats were last reset.
Permission needed: VIEW DATABASE STATE
When to use: Any indexing cleanup effort — distinguishing indexes earning their write cost from ones just sitting there.
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 s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY s.user_updates DESC;
Real-world example: A table with fourteen indexes was struggling under write load. This query found six of them had zero seeks or scans in the observed window but substantial update counts — pure write overhead with no read benefit, and safe removal candidates.
24. sys.dm_db_index_physical_stats
What it shows: Fragmentation levels and page counts.
Permission needed: VIEW DATABASE STATE
When to use: Deciding between reorganize and rebuild.
SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;
Real-world example: A weekly maintenance job had quietly stopped running three months earlier (a credential expired). This query was how we discovered it — several indexes sitting above 60% fragmentation, explaining a slow, steady performance decline nobody had connected to a specific cause yet.
25. sys.dm_db_missing_index_details
What it shows: Queries that would have benefited from an index that doesn’t currently exist.
Permission needed: VIEW DATABASE STATE
When to use: Raw data behind SSMS’s and the portal’s missing-index suggestions.
SELECT d.statement AS table_name, d.equality_columns, d.inequality_columns, d.included_columns,
s.avg_user_impact, s.user_seeks
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact DESC;
Real-world example: A reporting query newly added to a dashboard was doing a full scan on a 40-million-row table every time it ran. This view flagged it with a 97% estimated impact score within a day of the dashboard going live — one covering index later, the dashboard loaded in under a second.
26. sys.dm_db_missing_index_group_stats
What it shows: The impact numbers behind missing-index suggestions.
Permission needed: VIEW DATABASE STATE
When to use: Prioritizing which suggestions are worth acting on, rather than blindly applying every one.
27. sys.dm_db_stats_properties (function)
What it shows: A statistics object’s last update time, rows sampled, and modification counter.
Permission needed: VIEW DATABASE STATE
When to use: A plan looks like it’s working off bad row estimates — check whether stale statistics are the actual culprit before assuming it’s an indexing problem.
SELECT s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');
Real-world example: A query’s estimated and actual row counts were wildly different. This confirmed the statistics hadn’t updated in three weeks despite heavy daily inserts — auto-update statistics hadn’t triggered yet because the modification threshold, on a very large table, simply hadn’t been crossed.
Storage & I/O
28. sys.dm_io_virtual_file_stats (function)
What it shows: I/O statistics per database file — reads, writes, stall time.
Permission needed: VIEW DATABASE STATE
When to use: Spotting which specific file (data versus log) is under I/O pressure, rather than relying on a blended metric.
SELECT DB_NAME(database_id) AS db_name, file_id, num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL);
Real-world example: Overall I/O looked fine in the portal metrics, but this view showed the log file specifically had disproportionate stall time — pointing at write-heavy batch activity rather than the read-heavy query load everyone had initially suspected.
29. sys.dm_db_partition_stats
What it shows: Row counts and space usage per partition or table.
Permission needed: VIEW DATABASE STATE
When to use: “Which tables are actually driving storage growth” without running sp_spaceused table by table.
SELECT OBJECT_NAME(object_id) AS table_name, SUM(row_count) AS total_rows,
SUM(used_page_count) * 8 / 1024 AS used_space_mb
FROM sys.dm_db_partition_stats
WHERE index_id IN (0,1)
GROUP BY object_id
ORDER BY used_space_mb DESC;
Real-world example: A database hit 85% of its max size unexpectedly. This query took two minutes to identify a single logging table nobody was actively monitoring had grown to consume 60% of total storage — a missing retention/archival job, not organic business data growth.
30. sys.dm_db_log_space_usage
What it shows: Current transaction log size and percent used, right now.
Permission needed: VIEW DATABASE STATE
When to use: A log_write_percent alert fires, or you suspect log growth is becoming a problem — fastest way to confirm from a query window.
SELECT total_log_size_in_bytes / 1024 / 1024 AS log_size_mb,
used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
Real-world example: A long-running data migration transaction was left uncommitted overnight. This showed the log at 92% used and climbing — caught with enough time to gracefully commit the transaction in batches rather than the log filling up and blocking every write on the database.
How I actually use this list day to day
If I had to compress this into a “first five minutes of an incident” routine:
sys.dm_db_resource_stats— is there genuine resource pressure, and on which dimension?sys.dm_exec_requests+sys.dm_exec_sql_text— what’s actually running right now?sys.dm_os_waiting_tasks+sys.dm_tran_locks— if something’s blocked, who’s the real head blocker?- Query Store’s runtime stats — has anything’s performance genuinely changed against its own baseline?
sys.dm_db_missing_index_detailsordm_db_index_usage_stats— only once the above points toward an indexing issue specifically, not as a first move.
That order matters more than any individual DMV — confirm the symptom with resource data first, localize it to a specific query or lock next, and only then start reasoning about a fix. Every DMV on this list earns its place because it answers one specific link in that chain, not because it’s impressive to have memorized.
For more SQL Server, Azure SQL, Performance Tuning, Security, and DBA-related articles, click the link below.
The Complete SQL Server DBA Morning Health Check Guide: 25 Daily Checks Every DBA Should Perform
What are DMVs in SQL Server and how to use them?
SQL Server Performance Troubleshooting with Query Store, DMVs & Extended Events
Top 50 Azure SQL Optimization Interview Questions and Answers (Beginner to Advanced)
Azure Monitor for Azure SQL: Complete Monitoring Guide for DBAs (DP-300 & Real-World)
Top 50 SQL DBA Problems with Practical Solutions | Complete Guide
Top SQL Performance Tuning Techniques Every DBA Should Know
For Interview Questions on SQL SQL Server, Azure SQL, Performance Tuning, Security, and DBA, click the link below:-
https://www.techmixing.com/interview-questions-2
Explore the Complete TechMixing Article Sitemap – Click the Link Below
https://www.techmixing.com/site-map
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
