Today, we have been working on a performance issue where our customer is running a stored procedure but we don't know exactly what is the part of this that is running.
Let me show you an example how to obtain this information:
- We have the following stored procedure definition:
CREATE PROCEDURE WhereIsTheQueryIsRunningNow
AS
BEGIN
SELECT 1
SELECT 2
WAITFOR DELAY '00:00:03'
SELECT 3
SELECT 4
END
- We run this stored procedure EXEC WhereIsTheQueryIsRunningNow
- Every time that we run the following query we obtain that the stored procedure is running but we don't know what is the exact line:
SELECT
req.session_id
, req.start_time
, cpu_time 'cpu_time_ms'
, object_name(st.objectid,st.dbid) 'ObjectName'
, ST.text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
- If we modify the query and we use the statement start and end offset we could find the exact query that our stored procedure is running at this time.
SELECT
req.session_id
, req.start_time
, cpu_time 'cpu_time_ms'
, object_name(st.objectid,st.dbid) 'ObjectName'
, substring
(REPLACE
(REPLACE
(SUBSTRING
(ST.text
, (req.statement_start_offset/2) + 1
, (
(CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END
- req.statement_start_offset)/2) + 1)
, CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
Enjoy!