SQL Server Long-Running Queries: Find Active Sessions

calendar Apr 12, 2026 · 6 min read · sql server performance monitoring sql scripts sys.dm_exec_requests sys.dm_exec_sql_text blocking wait types database administration active sessions  ·
Share on: linkedin copy

Find Active Long-Running Queries in SQL Server

This script queries sys.dm_exec_requests and sys.dm_exec_sql_text to show all currently executing queries on the SQL Server instance, sorted by how long they have been running, along with CPU time, I/O activity, blocking status, and wait type information.

Purpose and Overview

A query that normally completes in seconds but has been running for many minutes is one of the most common performance problems a DBA investigates. The cause could be blocking from another session, a missing index, parameter sniffing producing a bad plan, or a sudden increase in data volume. The first step in diagnosing the problem is knowing what is running right now. SQL Server exposes this through the sys.dm_exec_requests dynamic management view, which holds one row for every active request currently executing on the instance. Combined with sys.dm_exec_sql_text, which returns the actual T-SQL text for a given SQL handle, this query gives you immediate visibility into what every session is doing, how long it has been running, and whether it is waiting on something.

Code Breakdown

The complete script is shown below, followed by an explanation of each part.

 1SELECT
 2    req.session_id,
 3    req.start_time,
 4    req.command,
 5    DB_NAME(req.database_id)    AS database_name,
 6    req.status,
 7    req.cpu_time,
 8    req.total_elapsed_time,
 9    req.logical_reads,
10    req.writes,
11    req.blocking_session_id,
12    req.wait_resource,
13    req.wait_type,
14    req.last_wait_type,
15    sqltext.text                AS query_text
16FROM sys.dm_exec_requests AS req
17CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
18WHERE req.session_id <> @@SPID
19-- AND req.database_id = DB_ID()
20ORDER BY total_elapsed_time DESC;

sys.dm_exec_requests

sys.dm_exec_requests is a dynamic management view that returns one row per currently active request. It is real-time — each execution of this query reflects the state of the server at that moment. Key columns used in this script:

  • session_id — the SPID (server process ID) of the session running the request
  • start_time — when the request began executing
  • command — the type of command, such as SELECT, UPDATE, BACKUP DATABASE, or DBCC
  • database_id — the ID of the database the request is running against; wrapped in DB_NAME() to return the human-readable name
  • status — the current request state: running, sleeping, suspended, or background
  • cpu_time — cumulative CPU milliseconds consumed by this request so far
  • total_elapsed_time — wall-clock milliseconds since the request started; this is the primary sort column and the most useful indicator of how long a query has been running
  • logical_reads — number of logical read operations performed so far; high values often indicate missing indexes or large table scans
  • writes — number of write operations; elevated values point to sort spills to tempdb or large data modifications
  • blocking_session_id — if non-zero, this is the SPID of another session that is holding a lock this request is waiting for
  • wait_resource — the specific resource (page, key, object) the request is waiting on
  • wait_type — the current wait type, such as LCK_M_S (shared lock wait) or PAGEIOLATCH_SH (I/O wait)
  • last_wait_type — the most recent wait type, useful when the current wait_type is blank but the request was recently waiting

CROSS APPLY sys.dm_exec_sql_text

sys.dm_exec_sql_text is a table-valued function that accepts a sql_handle and returns the full batch text of the SQL statement. Using CROSS APPLY calls it once per row in sys.dm_exec_requests, returning the text column which holds the T-SQL being executed. Requests with a NULL sql_handle — such as some background tasks — are excluded by the CROSS APPLY rather than returning a NULL row.

Filtering and Ordering

WHERE req.session_id <> @@SPID removes the monitoring query itself from the result set. Without this filter, the script would always show itself as an active request.

The commented-out line -- AND req.database_id = DB_ID() is a useful optional filter. Uncomment it to limit results to the database you are currently connected to, which helps focus attention on a specific application database when the server has many active sessions across multiple databases.

ORDER BY total_elapsed_time DESC puts the longest-running queries at the top, making it easy to spot the most time-consuming sessions immediately.

Key Benefits and Use Cases

  • Instantly shows which queries have been running longest on the instance
  • Identifies blocked sessions via blocking_session_id — a non-zero value means the session is waiting on another
  • Wait type columns explain why a query is not progressing — lock waits, I/O waits, memory waits, and more
  • CPU time and logical reads columns reveal resource-intensive queries without needing a profiler or extended events session
  • The command column distinguishes SELECT, DML, backup, DBCC, and other operation types at a glance
  • No configuration required — reads directly from a built-in DMV available on all SQL Server editions and versions

Performance Considerations

  • Read-only on DMVs: This query reads dynamic management views, which are in-memory and have no I/O cost against user databases. It is safe to run during production incidents.
  • Snapshot isolation: DMV data reflects the state at the moment of the query execution. On a busy server, run the query multiple times a few seconds apart to see whether a session is progressing or stuck.
  • sys.dm_exec_query_stats for history: sys.dm_exec_requests only shows currently running queries. For historical query performance — finding the top CPU consumers over the past hour — use sys.dm_exec_query_stats, which retains cumulative stats for cached query plans.
  • Extended Events: For continuous monitoring or capturing queries that complete quickly, an Extended Events session targeting sql_statement_completed or rpc_completed provides much better coverage than polling this DMV.
  • Required permissions: Viewing all sessions requires the VIEW SERVER STATE permission. Without it, users see only their own sessions.

Practical Tips

To also see estimated completion time and percent complete — useful for long-running operations like DBCC CHECKDB, index rebuilds, or RESTORE — extend the query with columns from a second join:

 1SELECT
 2    req.session_id,
 3    req.start_time,
 4    req.command,
 5    DB_NAME(req.database_id)                                        AS database_name,
 6    req.status,
 7    req.percent_complete,
 8    DATEADD(MILLISECOND, req.estimated_completion_time, GETDATE())  AS estimated_finish_time,
 9    req.cpu_time,
10    req.total_elapsed_time,
11    req.logical_reads,
12    req.blocking_session_id,
13    req.wait_type,
14    SUBSTRING(t.text, req.statement_start_offset / 2,
15        COALESCE(NULLIF(req.statement_end_offset, -1) / 2, 2147483647))
16                                                                    AS current_statement,
17    s.login_name,
18    s.host_name,
19    c.client_net_address
20FROM sys.dm_exec_requests AS req
21INNER JOIN sys.dm_exec_sessions AS s
22    ON req.session_id = s.session_id
23INNER JOIN sys.dm_exec_connections AS c
24    ON req.session_id = c.session_id
25CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS t
26WHERE req.session_id <> @@SPID
27ORDER BY req.total_elapsed_time DESC;

This extended version adds login_name, host_name, and client_net_address from sys.dm_exec_sessions and sys.dm_exec_connections, which helps identify which application or user is responsible for a long-running query. The SUBSTRING expression extracts just the specific statement within a batch that is currently executing, rather than the full batch text.

To kill a specific long-running session after identifying it, use KILL <session_id>. Always verify the session belongs to a query that is safe to terminate before doing so.

Conclusion

Querying sys.dm_exec_requests is the fastest way to see what SQL Server is executing right now. Sorted by elapsed time and joined to sys.dm_exec_sql_text, this script gives you the query text, resource consumption, blocking status, and wait type for every active session in a few seconds. It requires no setup, works on all editions, and is safe to run during a live performance incident. Use the extended version with sys.dm_exec_sessions and sys.dm_exec_connections when you also need to identify the application, login, or host responsible for a slow query.

References

Posts in this series