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.

By JaviPublished Updated 12 min readIntermediate

Live SQL Server activity table comparing session, database, CPU, reads, and wait state

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

SQL query: Running requestsT-SQL
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;
T-SQL52 linesRead-only query display. It is not connected to a database.

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_sessions and 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 NOLOCK or 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

Checked November 2026.

Tags

  • SQL Server
  • Performance
  • Troubleshooting
  • DMV
  • Blocking