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)
-
Login Name
-
Login Time
-
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.
References
-
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.
SQLTwins by Nakul Vachhrajani 
SELECT r.cpu_time,
r.logical_reads,
r.session_id
INTO #temp
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)
ELSE Datalength(h.text)
END ))) AS text,
r.cpu_time – t.cpu_time AS CPUDiff,
r.logical_reads – t.logical_reads AS ReadDiff,
r.percent_complete,
r.wait_type,
r.wait_time,
r.last_wait_type,
r.wait_resource,
r.command,
r.database_id,
r.blocking_session_id,
r.granted_query_memory,
r.session_id,
r.reads,
r.writes,
r.row_count,
s.[host_name],
s.program_name,
s.login_name
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
LikeLike