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)