SQL Server
How to Find What Is Running in SQL Server Right Now
A production-safe DMV query for active SQL Server requests, including statement text, identity, resource use, waits and blocking.
On this page
Short answer: query sys.dm_exec_requests joined to sys.dm_exec_sessions: every row is a request executing or waiting right now, with how long it has run, the CPU, reads and writes it has used, what it is waiting on, who started it and the statement it is running. Sort by elapsed time, CPU or reads to find the requests that deserve a closer look. If you already know the problem is blocking, go straight to Find Blocking.
When users say an application is slow, the most useful first snapshot answers two questions: what is executing now, and who initiated it? SQL Server exposes both through documented dynamic management views. The request identifies the current unit of work; the session identifies the connected client; the connection contributes network and connection context; and the SQL text function resolves the batch text.
For the broader production guide set, start at the SQL Server hub.
This is a live view, not history. A fast statement can finish between samples, and a completed statement will disappear from sys.dm_exec_requests. Capture the output with its timestamp before intervening.
Problem
Suppose users report slow reports from ReportingDb. You need to distinguish a busy report from a blocked request, an API spike, or unrelated maintenance. Activity Monitor may provide a quick view, but a query is repeatable, filterable, exportable, and explicit about which counters you are comparing.
Quick answer
DECLARE @SessionId int = NULL;
DECLARE @DatabaseName sysname = NULL;
DECLARE @LoginName sysname = NULL;
DECLARE @HostName nvarchar(128) = NULL;
DECLARE @ProgramName nvarchar(128) = NULL;
SELECT
SYSDATETIMEOFFSET() AS captured_at,
r.session_id,
r.request_id,
DB_NAME(r.database_id) AS database_name,
s.login_name,
s.original_login_name,
s.host_name,
s.program_name,
c.client_net_address,
s.status AS session_status,
r.status AS request_status,
r.command,
r.start_time,
r.total_elapsed_time AS elapsed_time_ms,
r.cpu_time AS cpu_time_ms,
r.logical_reads,
r.reads AS physical_reads,
r.writes,
r.wait_type,
r.wait_time AS wait_time_ms,
r.wait_resource,
NULLIF(r.blocking_session_id, 0) AS blocking_session_id,
SUBSTRING(
sql_text.text,
(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(sql_text.text)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1
) AS current_statement,
sql_text.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
LEFT JOIN sys.dm_exec_connections AS c
ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS sql_text
WHERE s.is_user_process = 1
AND r.session_id <> @@SPID
AND (@SessionId IS NULL OR r.session_id = @SessionId)
AND (@DatabaseName IS NULL OR DB_NAME(r.database_id) = @DatabaseName)
AND (@LoginName IS NULL OR s.login_name = @LoginName)
AND (@HostName IS NULL OR s.host_name = @HostName)
AND (@ProgramName IS NULL OR s.program_name = @ProgramName)
ORDER BY r.total_elapsed_time DESC, r.session_id, r.request_id;Notes
- lines 1–5Optional filters: leave them NULL to list every running request, or set one to narrow the list.
- lines 30–37The statement executing now, cut from the batch: the offsets are bytes into Unicode text, and -1 means the end of the batch.
- line 44OUTER APPLY keeps requests that have no SQL handle; CROSS APPLY would drop them.
Run it without filters once. Look for long elapsed time, a non-null blocking session, repeated wait types, high CPU or logical reads, and concentration by database or application. Then set one or more parameters to narrow the result; leaving every parameter NULL shows all user requests.
To see other sessions, the login needs server-state permission: VIEW SERVER STATE on SQL Server 2019 and earlier. SQL Server 2022 introduced the more granular VIEW SERVER PERFORMANCE STATE, which Microsoft documents for sys.dm_exec_sessions. Without it, a user sees only their own requests.
Read the first sample
Read blocking_session_id and wait_type first to spot a blocker or current wait. Then compare elapsed_time_ms with cpu_time_ms, and use logical_reads and writes to understand request work. Use the session, database, login, host and application columns to identify the source; client-supplied host and program names are clues, not proof.
Session ID versus request ID
A session_id identifies a client session, historically called a SPID. One pooled application connection may remain open for a long time and execute many requests over time. A request_id identifies a request currently executing inside that session. Most ordinary sessions have one active request at a time, but Multiple Active Result Sets can allow more than one. For a precise sample, record both values.
Do not treat session counters and request counters as interchangeable. Values on sys.dm_exec_requests, including elapsed time and request CPU, describe the current request. Session-level totals may cover multiple completed requests since the session was established.
How to read the result
| Field | What it is | Read it as |
|---|---|---|
session_id, request_id |
The client session and the request running inside it | Record both; one session runs many requests over its life |
start_time, elapsed_time_ms |
When the request began, and wall-clock time since | A long elapsed time is a question, not a verdict |
cpu_time_ms |
Processor time this request has used | Much lower than elapsed time means the request is mostly waiting |
logical_reads, physical_reads |
Pages read from the buffer pool; pages that needed physical I/O | Logical reads are often the best single measure of work |
writes |
Pages written by the request | Includes worktables, not only final table data |
wait_type, wait_time_ms, wait_resource |
What the request is waiting on at this instant, for how long, and on what | Changes quickly; sample more than once |
blocking_session_id |
The session currently blocking this request; NULL when none |
Non-null means a dependency, not slow code |
database_name |
The request’s database context | Concentration on one database narrows the search |
login_name, original_login_name |
Current security context; the login that opened the session | They differ after EXECUTE AS |
host_name, program_name, client_net_address |
Where the connection came from | Supplied by the client: useful hints, not proof of identity |
current_statement, batch_text |
The statement running now, and its surrounding batch | Offsets are byte positions in Unicode text, hence the division by two; -1 means to the end of the batch |
What the result may mean
High elapsed time with low CPU can indicate waiting, including blocking, I/O, memory grant, client consumption, or another resource. High CPU with high logical reads can indicate a large scan, repeated work, a poor join strategy, or simply a legitimately large request. Many similar requests from ApiService can indicate concurrency or retry pressure even if no single request looks extreme.
One row is rarely enough for intermittent issues. Take a few samples several seconds apart and compare. A request whose counters continue increasing is working; one whose wait and blocker remain unchanged needs investigation along that dependency chain.
What to investigate next
| Observation | What it may mean | Where to go next |
|---|---|---|
blocking_session_id is populated |
The request is waiting on another session’s locks | Find Blocking; the SQL Server Blocking Runbook if it is the active incident |
| High CPU, with elapsed time close to CPU time | The request is computing: a large scan, a poor join, repeated work | SQL Server Is Slow: first 15 minutes; High CPU Queries |
| High logical reads | The request touches many pages: predicates, estimates, missing access paths | Capture the execution plan; High Read Queries |
| Long elapsed time, low CPU, the same wait across samples | Waiting on I/O, a memory grant, the client or a lock | Wait Types |
| Many similar requests from one program or host | Concurrency, retries or a batch spike | Login, Host and Application Activity |
| A query that used to be fast is slow now | A possible plan regression | Query Store regressions |
| One session needs a closer look | Its identity, open transaction or last batch matters | Get a SQL Server session ID |
| The problem has already ended | The request is no longer in the DMVs | Query Store and the history options in real-time workload monitoring |
Common mistakes
- Querying only
sys.dm_exec_sessionsand assuming every connected session is executing. - Treating sleeping sessions as harmless without checking for an open transaction.
- Sorting only by duration and labelling every long request bad.
- Trusting client-supplied host and program names as an authentication boundary.
- Running the query once and assuming the snapshot represents the entire incident.
- Using
NOLOCKor clearing the cache as a response to evidence not yet understood.
Production notes
The query reads lightweight DMVs, but frequent polling and retaining full batch text for every request still has a cost. Use an interval appropriate to the incident, timestamp every capture, and store samples outside the affected database when possible. Redact SQL text before sharing it because batches can contain sensitive literal values.
This query deliberately shows active user requests. Sleeping sessions do not appear because they have no row in sys.dm_exec_requests. That is correct for “what is running now,” but it means a separate session or transaction query is required to find a sleeping connection that left a transaction open.
A sleeping session that still holds locks is the classic head blocker; Find Blocking shows how to find it.
Continue the performance path
For a broad “SQL Server is slow” report, start with the first 15 minutes of triage, then return to the performance tuning workflow. Inspect one connection with the session ID/SPID guide, or follow a blocker with How to Find Blocking. For completed activity, move to Query Store regressions; for a wider live picture, use real-time workload monitoring.
Quick references: Running Queries, Session Details, Wait Types, and Performance Troubleshooting.
Sources
- Microsoft Learn: sys.dm_exec_requests, sys.dm_exec_sessions (permissions by version), sys.dm_exec_connections, sys.dm_exec_sql_text
Checked November 2026.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetFind What Is Running in SQL ServerQuickly identify active SQL Server requests, what they are executing, how long they have been running, what resources they are consuming, and whether they are waiting or blocked.Open cheat sheet
- RoadmapSQL Server Troubleshooting & DiagnosticsA production-safe DMV query for active SQL Server requests, including statement text, identity, resource use, waits and blocking.View roadmap
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.