Process Monitoring in SQL Server

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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: