The problem
Last week, an application started reporting SQL timeouts. By default, SQL does not have a query timeout; it is the application that decides it doesn’t want to wait any longer. The question was: how do we find out which query causes the timeout? There was no error in the SQL log, so I decided to fire up SQL Profiler.
According to this MSDN article, there is a Profiler event class called Attention that should allow you to trace these timeouts:
The Attention event class indicates that an attention event, such as cancel, client-interrupt requests, or broken client connections, has occurred. Cancel operations can also be seen as part of implementing data access driver time-outs.
I had never used this event class before, so I decided to test whether I could actually find the offending query using this method.
The solution
Let’s replicate the scenario. For that, we need a query that takes longer than the application timeout. The default .Net timeout is 30 seconds, but I’m not the patient type. So I configured Management Studio with a timeout of 5 seconds:
After that, I ran the following query:
|
1 |
WAITFOR DELAY '00:00:10' |
And sure enough, after 5 seconds the query timed out:
Next, I fired up Profiler, and started a new trace.
Running the same query proved that this event class actually does trace timeouts:
To make the trace more efficient, I tried to do the same using Extended Events, since the overhead of Profiler is quite high. Unfortunately though, there is no event class Attention in SQL 2008 (while the test was done in SQL 2012, the actual problem was with a 2008R2 database). But at least I could now find the problem query.