DevOps & Observability
Monitoring SQL Server Workloads in Real Time
Inspect active SQL Server requests, sessions and connections, then connect current state to retained Query Store history.
On this page
Short answer: real-time monitoring answers “what is SQL Server doing now, and where should I look next?” Take a one-glance pulse of the workload, classify what dominates (blocking, CPU, I/O or waits), and hand off to the narrower guide. As soon as the question is about the past, switch from live DMVs to Query Store, Extended Events or your monitoring history: point-in-time views cannot describe what already finished.
This is one step in the broader SQL Server performance tuning workflow. For a focused incident query, see how to find running queries.
Current state: requests, sessions, and connections
Three dynamic management views describe different parts of the live workload:
sys.dm_exec_requestscontains requests executing or waiting now, including CPU, elapsed time, reads, writes, waits, and blocking.sys.dm_exec_sessionsidentifies the connected session, login, host, program, and whether it is a user process.sys.dm_exec_connectionsadds connection context such as client address and the most recent SQL handle.
A session can exist without an active request. Monitoring only sys.dm_exec_sessions can make sleeping connections look like running work, while monitoring only sys.dm_exec_requests can miss a sleeping session that still owns an open transaction.
The monitoring loop
- ObserveWorkload pulse, active requests, current waits
- ClassifyWhat dominates right now?
- lock waitsBlockingFind Blocking, then the runbook
- high CPUCPU or one queryRunning queries, plan, Query Store
- I/O waitsI/OReads per request, storage latency
- waitsOther waitsWait deltas, memory grants
- already overHistorical regressionQuery Store, Extended Events
- Capture evidenceTimestamped samples, IDs, plans
- ValidateSame workload, measured again
A one-glance workload pulse
Before reading individual requests, get the shape of the workload. These read-only queries summarize the active user requests instead of listing them:
-- 1. One-row summary of the current user workload
SELECT
SYSDATETIMEOFFSET() AS sample_time,
COUNT(*) AS active_requests,
SUM(CASE WHEN r.blocking_session_id > 0 THEN 1 ELSE 0 END) AS blocked_requests,
SUM(CASE WHEN r.status = 'running' THEN 1 ELSE 0 END) AS running,
SUM(CASE WHEN r.status = 'runnable' THEN 1 ELSE 0 END) AS runnable,
SUM(CASE WHEN r.status = 'suspended' THEN 1 ELSE 0 END) AS suspended,
MAX(r.total_elapsed_time) / 1000 AS longest_request_seconds
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID;
-- 2. What active requests are waiting on at this instant
SELECT r.wait_type, COUNT(*) AS requests, MAX(r.wait_time) AS max_wait_ms
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID
AND r.wait_type IS NOT NULL
GROUP BY r.wait_type
ORDER BY requests DESC, max_wait_ms DESC;Read the first result as proportions. Many runnable requests mean work is waiting for CPU. Many suspended requests mean work is waiting on something else, and the second query says what. A non-zero blocked_requests count points to blocking. For the per-request detail (statement, CPU, reads, waits and who started it) use Find Running Queries, the canonical active-request query on this site.
Route by question
What is running now?
The pulse gives the shape; Find Running Queries gives every active request with its cost, wait, origin and statement.
Is work blocked?
A non-zero blocked_requests count, or LCK_M_* among current waits, means requests are waiting on locks. Trace the chain to its head blocker with Find Blocking. If users are affected now, follow the SQL Server Blocking Runbook.
What is SQL Server waiting on?
Current request waits describe this instant. Server-wide waits in sys.dm_os_wait_stats are cumulative since the last restart or manual clear, so a single read mostly describes history: take two samples a minute or so apart and compare the difference. Do not clear wait statistics to get a clean picture; that destroys the baseline others rely on. The Wait Types quick reference has the sampling queries and what each common wait means.
Is one query consuming the system?
Sort active requests by CPU or logical reads in Find Running Queries. If the same query keeps appearing, compare its runtime and plans across time in Query Store. Quick references: High CPU Queries and High Read Queries.
Is this a transient spike or a recurring pattern?
Live DMVs cannot answer that. Compare time windows in Query Store, check the system_health Extended Events session for long lock waits and deadlocks, and look at your monitoring history. For a broad “SQL Server is slow” report, the first 15 minutes of triage decides the scope first.
Do I need continuous evidence?
When problems come and go faster than you can sample, capture them as they happen: Query Store for query and plan history, an Extended Events session for specific events, or your monitoring platform. The blocked process report is off by default and is enabled with a server configuration change (blocked process threshold); treat it as a decision with an owner, not an incident reflex.
Point-in-time versus historical evidence
| Source | Kind | What it answers | How long it lasts |
|---|---|---|---|
| Request, session and connection DMVs | Point in time | What is running, waiting or blocked now | Until the request ends or the session closes |
sys.dm_os_wait_stats |
Cumulative counters | What the instance has waited on since its last restart | Until restart or a manual clear; compare two samples |
| Activity Monitor (SSMS) | Interactive live view | A quick look at processes, waits and expensive queries | Only while the window is open; not retained evidence |
| Query Store | Historical, per database | Which queries and plans changed, and when | Per its retention settings; on by default only for new databases from SQL Server 2022 |
system_health Extended Events session |
Historical, rolling, on by default | Deadlocks, lock waits over 30 seconds, other long waits | Until its files roll over |
| Your own Extended Events session or monitoring platform | Historical, designed | Whatever you chose to capture | Per your retention |
DMVs are the authority for “now” and the wrong source for “then”. A completed request disappears from sys.dm_exec_requests, a fast statement can begin and end between samples, and counters reset when SQL Server restarts or fails over. One snapshot cannot prove what happened ten minutes earlier or establish a normal baseline.
Activity Monitor is convenient for a first look, but it is a screen, not a record: query the DMVs and save timestamped results when you need evidence.
Sample safely over a short window
Periodic sampling can show whether a request is progressing, repeatedly waiting, or staying behind the same blocker. Capture a timestamp, session_id, request_id, database, status, CPU, elapsed time, reads, writes, wait, blocker, and a stable SQL or query identifier where available.
For an incident, running the pulse queries and the active-request query every 5–15 seconds for a bounded period is often enough. Save each result set outside the affected database. Avoid second-by-second polling by default, and do not retain full SQL text indefinitely when a hash, query ID, or restricted diagnostic record is sufficient.
Sampling DMVs is not a replacement for a monitoring platform. It is a practical bridge between a single live snapshot and retained history.
Hand off to Query Store for history
Use Query Store when the question changes from “what is active now?” to “what changed over time?” It is the better source for:
- duration, CPU, and logical-read trends across comparable windows;
- queries that became slower after a release or data change;
- multiple plans for the same query;
- a plan change that aligns with a regression.
Follow How to Find SQL Server Performance Regressions with Query Store for the historical workflow. The Query Store quick reference provides companion operational queries.
Query Store is enabled by default only for new databases from SQL Server 2022 onward; on SQL Server 2016, 2017 and 2019 it must be enabled per database, and it records nothing from before that point.
Production cautions
- Permissions: grant server-state visibility only to roles that need it; requirements vary by version and platform.
- Overhead: DMV queries are lightweight, not free. Select useful columns, use a measured interval, and stop polling when it is no longer needed.
- Sensitive text: SQL batches can contain personal data, customer identifiers, or literal secrets. Restrict, redact, and retain captured text appropriately.
- Client metadata:
host_nameandprogram_nameare useful hints but are supplied by the client and are not security identities. - Current versus historical evidence: do not use a live snapshot to make claims about a completed incident. Use Query Store, Extended Events, or existing monitoring history.
- Safe response: observing a request is not permission to kill it, clear caches, add an index, or change server settings. Validate the cause first.
Where to go next
| You need to… | Go to |
|---|---|
| See every active request and what it costs | Find Running Queries |
| Trace a blocking chain to its head blocker | Find Blocking |
| Respond to an active blocking incident | SQL Server Blocking Runbook |
| Triage a broad “SQL Server is slow” report | SQL Server Is Slow: the first 15 minutes |
| Inspect one session in depth | Get a SQL Server session ID |
| Investigate a regression that already happened | Query Store regressions |
| Follow the full investigation order | Performance tuning workflow and the performance tuning checklist |
Relevant quick references: Performance Troubleshooting Flow, Wait Types, High CPU Queries, High Read Queries and Query Store.
Sources
- Microsoft Learn: sys.dm_exec_requests, sys.dm_exec_sessions (permissions by version), sys.dm_os_wait_stats (cumulative since restart)
- Microsoft Learn: Monitor performance by using the Query Store (default state by version), the system_health session, Extended Events quickstart, Activity Monitor, blocked process threshold
Checked November 2026.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind Long-Running QueriesIdentify long-running SQL Server requests and historical slow queries, separate elapsed time from CPU time, and determine whether the delay comes from waits, blocking, reads, or plan regression.Open cheat sheet
- ArticleMonitoring a Data Platform: What Should You Actually Measure?A practical monitoring model connecting clients, applications, APIs, report engines, data platforms, compute and storage.Read article
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.