Recently I tried to figure out where a SQL operation was spending its time, so I navigated to Current Acticity in Enterprise Manager but only got a timeout message.
I found that the information I needed was stored in the sysprocesses table: SPID, waittype, and the statement causing the wait.
The nice part is that you can literally watch a trigger or a stored procedure being executing by interpreting the values of sql_handle, stmt_start and stmt_end.
The tricky part is how to interpret them. This article on fn_get_sql() was incredibly helpful.
Here’s the strategy:
- Iterate through all SPIDs with a waittype
- Retrieve sql_handle, stmt_start, stmt_end
- SELECT from fn_get_sql() with these parameters (the function returns a recordset)
- Retrieve SUBSTRING of the text column
With a little copy and pasting you can also display the preceding and following statements, which makes it easier to locate the current statement during debugging (line numbers are not part of the result).
