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;

score 17 · Answer 1 · answered Feb 15 '12 at 12:50

17

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

answered Feb 15 '12 at 12:50

Martin Smith

438,706
87
741
845

Cool, that seems to work great! (For other people wanting this, change/remove the WHERE-line.) – cederlof Feb 15 '12 at 13:19
Its helpful to add a line WHERE session_id != < this session id > – Gabriel McAdams Dec 02 '13 at 19:06
@GabrielMcAdams - In the case of the question they are specifically interested in session id `65` (from `DBCC Inputbuffer (65)`) – Martin Smith Dec 02 '13 at 20:43
1

@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. – Gabriel McAdams Dec 02 '13 at 21:46

score 2 · Accepted Answer · edited Nov 13 '18 at 23:11

2

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

edited Nov 13 '18 at 23:11

Community

1
1

answered Feb 15 '12 at 12:53

Evgeny Lukashevich

1,500
14
26

score 1 · Answer 3 · answered Feb 15 '12 at 13:00

1

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.

answered Feb 15 '12 at 13:00

Pondlife

15,992
6
37
51

1

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 Feb 15 '12 at 13:12
2

@cederlof - You would need to capture the `SP:StmtCompleted` and `SP:StmtStarting` events in Profiler. – Martin Smith Feb 15 '12 at 13:21

Currently running query inside a stored procedure

3 Answers3

Linked