Capturing Executing Queries Using sys.dm_exec_requests
by Joseph Sack

This is a two part excerpt from Joseph Sack's book, SQL Server 2005 T-SQL Recipes (Apress).  This book is considered to be a "no-fluff" desk reference that covers basic T-SQL data manipulation, the use of stored procedures, triggers and UDFs, and advanced T-SQL techniques for database security and maintenance. It also provides "how-to" answers to common SQL Server T-SQL questions, conceptual overviews, and highlights of new features introduced in SQL Server 2005.

Click here to read part 1 of this excerpt on "Capturing High Duration Queries Using SQL Server Profiler".

In addition to capturing queries in SQL Server Profiler, you can also capture the SQL for currently executing queries by querying the sys.dm_exec_requests dynamic management view, as this recipe demonstrates:

SELECT r.session_id, r.status, r.start_time, r.command, s.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.status = 'running'

This captures any queries that are currently being executed—even the current query used to
capture those queries:

session_id 	status		start_time 		command 	text
55 		running 	2005-10-16 13:53:52.670 SELECT SELECT 	r.session_id,
									r.status, r.start_time,
									r.command, s.text FROM
									sys.dm_exec_requests r
									CROSS APPLY
									sys.dm_exec_sql_text(r.sql_
									handle) s WHERE r.status =
									'running'

How It Works

The sys.dm_exec_requests dynamic management view returns information about all requests executing on a SQL Server instance.

The first line of the query selects the session ID, status of the query, start time, command type (for example SELECT, INSERT, UPDATE, DELETE), and actual SQL text:

SELECT r.session_id, r.status, r.start_time, r.command, s.text

In the FROM clause, the sys.dm_exec_requests dynamic management view is cross-applied against the sys.dm_exec_sql_text dynamic management function. This function takes the sql_handle from the sys.dm_exec_requests dynamic management view and returns the associated SQL text:

FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

The WHERE clause then designates that currently running processes be returned:

WHERE r.status = 'running'

Joe Sack is a senior consultant with Microsoft's Consulting for IT Operations team. He is the author of SQL Server 2000 Fast Answers (Apress), and the recently published SQL Server 2005 T-SQL Recipes (Apress).