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;

Answer 1

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

Answer 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 

Answer 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.