Find currently running, blocked and blocking queries in SQL Server

To list all currently running queries in SQL Server, use this code:

SELECT r.session_id,
s.host_name,
s.login_name,
s.original_login_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
t.text as Query_Text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
GO

List of all currently running queries in SQL Server

The results show session ID, host name (value is NULL for internal sessions, client application provides the workstation name and can provide inaccurate data), login name under which the query is currently executing, original login name that created the session, status, command, CPU time in milliseconds, total elapsed time in milliseconds and the query text.

The following query returns couple of additional columns: the database name the requests are executing against and blocking session ID for blocked queries:

SELECT r.session_id,
r.blocking_session_id,
DB_NAME(r.database_id) AS Database_Name,
s.host_name,
s.login_name,
s.original_login_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
t.text as Query_Text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
GO

List of all currently running queries in SQL Server

If you want to see only the running queries that are blocked and session ID of blocking queries run the following code:

SELECT r.session_id,
r.blocking_session_id,
DB_NAME(r.database_id) AS Database_Name,
s.host_name,
s.login_name,
s.original_login_name,
r.status,
r.command,
r.cpu_time,
r.total_elapsed_time,
t.text as Query_Text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.blocking_session_id <> 0
GO

List of blocked and blocking queries

To see the last statement sent from a client to an SQL Server instance run:
(for example for the blocking session ID)

DBCC INPUTBUFFER(64)
GO

If the post helped you, please share it:
Pin It

3 comments to Find currently running, blocked and blocking queries in SQL Server

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">