Many a times we encounter a situation wherein a stored procedure is taking a very long time to execute and the team would like to understand – whether the batch is making any progress or not and what statement is currently being executed by the database engine. I am often asked the question:
How do I know what T-SQL query statement is currently running on my SQL Server instance?
Today, I share a small query that would quickly return the following pieces of information:
T-SQL batch being executed
T-SQL Query statement being executed*
Currently encountered Wait type
Currently encountered Wait duration
T-SQL Command Type
Resources Request Status
Host Name (based on the connection string, as supplied by the calling application)
Program Name (based on the connection string)
Windows ProcessId for the process executing the query (on the host running the calling application)
Last Wait type encountered by the query
How to find the T-SQL Query statement being executed from a batch?
The perplexing question here is that a batch or a stored procedure may have multiple statements – but, getting to the exact statement that is being executed at a given point needs a bit of manipulation.
Basically, the sys.dm_exec_requests DMV returns us information about each request that is currently being executed by SQL Server . The sys.dm_exec_sql_text() returns the text of the SQL batch that is identified by the specified sql_handle. The statement_start_offset represents the number of bytes within the SQL batch indicated by the sql_handle at which the currently executing SQL Statement starts. Similarly, the statement_end_offset returns the number of bytes within the SQL batch where the currently executing SQL statement ends. Using these 2 values in a simple SUBSTRING function will return us the exact SQL Statement that is being executed by SQL Sever.
SELECT st.text AS CommandBeingExecuted, --Query being executed SUBSTRING(text, -- starting value for substring CASE WHEN ISNULL(statement_start_offset, 0) = 0 THEN 1 ELSE statement_start_offset/2 + 1 END, -- ending value for substring CASE WHEN ISNULL(NULLIF(statement_end_offset, -1), 0) = 0 THEN LEN(st.text) ELSE statement_end_offset/2 END - CASE WHEN ISNULL(statement_start_offset, 0) = 0 THEN 1 ELSE statement_start_offset/2 END + 1 ) AS SQLStatementBeingExecuted, sr.wait_type AS WaitType, sr.wait_time AS WaitDuration, sr.command AS SQLCommandType, sr.status AS RequestStatus, ses.host_name AS HostName, ses.program_name AS ProgramName, ses.login_name AS LoginName, ses.login_time AS LoginTime, ses.host_process_id AS WindowsProcessId, sr.last_wait_type AS LastWaitType FROM sys.dm_exec_requests AS sr INNER JOIN sys.dm_exec_sessions AS ses ON sr.session_id = ses.session_id CROSS APPLY sys.dm_exec_sql_text(sr.plan_handle) AS st;
If you have a similar query that you use, please share it in the comments area below.
sys.dm_exec_requests DMV [MSDN Link]
sys.dm_exec_sessions DMV [MSDN Link]
sys.dm_exec_sql_text [MSDN Link]
Until we meet next time,
Be courteous. Drive responsibly.
FROM sys.dm_exec_requests AS r
WAITFOR delay ’00:00:01′
SELECT Substring(h.text, ( r.statement_start_offset / 2 ) + 1,
(( CASE r.statement_end_offset
WHEN -1 THEN Datalength(h.text)
END ))) AS text,
r.cpu_time – t.cpu_time AS CPUDiff,
r.logical_reads – t.logical_reads AS ReadDiff,
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
ON s.session_id = r.session_id
AND s.last_request_start_time = r.start_time
LEFT JOIN #temp AS t
ON t.session_id = s.session_id
CROSS apply sys.Dm_exec_sql_text(r.sql_handle) h
WHERE is_user_process = 1
ORDER BY 3 DESC
DROP TABLE #temp