Currently executing SQL statements

Microsoft SQL Server introduced various dynamic management views and dynamic management functions to view the server and database state and it is very helpful for troubleshooting, performance tuning, monitoring and even more.

This post explores a dynamic management view and function which is used to find the currently executing queries in SQL server.

sys.dm_exec_requests

This view returns some information about requests that are executing within SQL Server. But it doesn’t show the actual query being executed instead, it returns the SQL handle of the statement or procedure or batch along with statement start and end offset. The start and end offset are used to extract the exact statement which is being executed.

sys.dm_exec_sql_text

This is table-valued function returns the text of SQL statements or procedures for the given SQL handle.

Query to return the currently executing statements

SELECT a.session_id, sql_statement
FROM sys.dm_exec_requests a CROSS APPLY
(
SELECT
SUBSTRING(text, (a.statement_start_offset/2)+1,
((
CASE a.statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE a.statement_end_offset
END - a.statement_start_offset)/2) + 1)  FROM sys.dm_exec_sql_text(a.sql_handle)
) c (sql_statement)

Leave a Reply

Your email address will not be published.