Currently running query inside a stored procedure

Question

I have a stored procedure that is currently running, and seems to hang/lock on a specific query. How can i see which query? Preferably without modifying the proc.

Using

DBCC Inputbuffer (65)

gives me

Language Event 0 EXEC mySP;

Solution 1

There is an excellent stored procedure to get extended information on currently running queries. It's available to download from: http://whoisactive.com

Solution 2

SELECT SUBSTRING(st.text, ( r.statement_start_offset / 2 ) + 1, 
              ( ( CASE WHEN r.statement_end_offset <= 0
                       THEN DATALENGTH(st.text) 
              ELSE r.statement_end_offset END - 
       r.statement_start_offset ) / 2 ) + 1) AS statement_text 
FROM   sys.dm_exec_requests r 
       CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
WHERE  session_id = 65 

Solution 3

Use SQL Profiler; as the name suggests, it's the main profiling tool for SQL Server and it can show the execution time for each statement inside a procedure.

Comments

[cederlof]

That was my thought as well, but all it shows me is the "EXEC mySP" query used to trigger the SP. Or am I using incorrect filtering?

[cederlof]

Cool, that seems to work great! (For other people wanting this, change/remove the WHERE-line.)

[Martin Smith]

@cederlof - You would need to capture the SP:StmtCompleted and SP:StmtStarting events in Profiler.

[Gabriel McAdams]

Its helpful to add a line WHERE session_id != < this session id >

[Martin Smith]

@GabrielMcAdams - In the case of the question they are specifically interested in session id 65 (from DBCC Inputbuffer (65))

[Gabriel McAdams]

@MartinSmith - You're right, and I didn't mean that it would be helpful to the original poster - but to add to his comment here that mentions removing the where clause.