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.

By JaviPublished Updated 13 min readIntermediate

SQL Server current-state monitoring connected to historical evidence

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_requests contains requests executing or waiting now, including CPU, elapsed time, reads, writes, waits, and blocking.
  • sys.dm_exec_sessions identifies the connected session, login, host, program, and whether it is a user process.
  • sys.dm_exec_connections adds 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

  1. ObserveWorkload pulse, active requests, current waits
  2. ClassifyWhat dominates right now?
    • lock waits
      BlockingFind Blocking, then the runbook
    • high CPU
      CPU or one queryRunning queries, plan, Query Store
    • I/O waits
      I/OReads per request, storage latency
    • waits
      Other waitsWait deltas, memory grants
    • already over
      Historical regressionQuery Store, Extended Events
  3. Capture evidenceTimestamped samples, IDs, plans
  4. ValidateSame workload, measured again
The real-time monitoring loop. Observe the whole workload, classify what dominates, drill into the matching guide, then capture evidence and validate before changing anything.

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:

SQL query: A one-glance workload pulseT-SQL
-- 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;
T-SQL23 linesRead-only query display. It is not connected to a database.

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_name and program_name are 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

Checked November 2026.

Tags

  • SQL Server
  • DMV
  • Monitoring
  • Observability
  • Performance
  • Blocking
  • Query Store