CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-proceduresdbcc
cederlof
cederlof

Reputation: 7383

Currently running query inside a stored procedure

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;

Upvotes: 12

Views: 17213

Answers (3)

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 1

Evgeny Lukashevich
Evgeny Lukashevich

Reputation: 1517

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

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453328

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

Upvotes: 19

Related Questions