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
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
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
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