Lesson Learned #119: What is the query that my stored procedure is running now?

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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

 

clipboard_image_0.png

 

  • 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

 

clipboard_image_1.png

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.