CODEWITHSUNDEEP
sql-serverprofiler
Jim Horrod
Jim Horrod

Reputation: 11

SQL Server Profiler show RPCs that started but did not finish

I am troubleshooting some pefromance issues with Profiler and am looking at the duration of RPCs using RPC:Completed. However, I also want to see any RPCs that start but never complete. Is there a timeout event I can capture. Thanks.

Upvotes: 1

Views: 1027

Answers (2)

Yaniv Etrogi
Yaniv Etrogi

Reputation: 36

To see any RPCs that start but never complete you can trace for the rpc completed event with a status different than 0. 0 represents success and any other value (1 or 2) represents some form of a failure. Time-outs have a status of 2.

Here is the filter to be used with sql trace

    -- errors only

EXEC sp_trace_setfilter @TraceID, 31, 0, 1, 0; EXEC sp_trace_setfilter @TraceID, 31, 0, 1, NULL;

Here is a link to a stored procedure that captures only failures but in you case you should again capture the rpc + batch completed events and apply that filter. enter link description here

Upvotes: 0

Grant Fritchey
Grant Fritchey

Reputation: 2785

If you are working on SQL Server 2012 or greater, the best way to capture timeouts is using Extended Events. There are several ways to capture this. First, there's an event called "Attention" that will show disconnects from the client, whether through timeouts or cancelled transactions or whatever. You can also see batch completed and rpc completed events with a result of 0. Finally, you can use pair matching techniques to see rpc/batch starting that don't have a corresponding rpc completed. You can read about this on an older blog post of mine.

I'd pursue one of these techniques unless I was monitoring in 2008 or less. Then, you can use Profiler to do this, but it's harder. You have to capture the rpc/batch starts and completion events, then, load the data into a table and query for missing pairs.

Upvotes: 1

Related Questions