Tracking Down Application Timeout Errors in SQL Server

Question

SQL Server 2008 SP3

How do I track down these timeout errors ?

The errors are displayed on an intranet dashboard used specifically for error reporting in IIS. My suspicion is that there is a default timeout of 30 seconds in the web application and if a query takes more than thirty seconds, an exception is thrown. As there are many queries that take longer than 30 seconds on these SQL servers, I can't just filter in profiler based on duration.

Serving up the website being monitored by this dashboard are two IIS servers retrieving data from seven SQL Server instances.

Could I use the "User Error Message Event" and the "OLEDB Errors Event" to track these errors in SQL Server Profiler?

Answer 1

Aaron Bertrand put me on the right track with his comment

And I believe you should be able to filter on duration and error <> 0.

Created a server side trace using the tsql_duration profile template

1. Added the User Error Message Event

2. Added the following filters

   error <> 0

   error <> 1

   severity <> 10

This avoided capturing the USE DATABASE commands

The error message captured by profiler was 2 - Abort and the Event Class was 10 RPC:Completed.

Answer 2

You can utilize the Attention event in profiler with the events for capturing the T-SQL statements. It does not necessarily specifically state what the attention event is when I tested it so I guess the fact that it follows the event sequence you can estimate those queries that have an issue. I did not get a chance to full test it out with code and all.

I did however come across an exact example with Extended Events that can be used to find timeout queries, and this example is with SQL Server 2008. It is from Jonathan Kehayias: An XEvent a Day (9 of 31) – Targets Week – pair_matching

---

Timeout errors are client side and the error is coming from the provider (or client) being used with the database connection. SQL Server does not necessarily keep track or offer any intuitive method to track these down.

Using a trace, timeouts on SQL Server side are basically queries that have a start but no completion. Which I came across a very good video that walks through an example from Sean McCown Find query timeouts with Profiler. Now it is not rock solid evidence as Sean notes in the video there are other things that could cause a transaction to not have an ending.

A synopsis of the steps:

• Create a trace in Profiler that captures the start and completed events for Stored Procedures and TSQL
• Load that data into a table
• Query that data to find the start events and then end events.

As the example goes in the video for SP:Starting (44) and SP:Completed (43) once you get your trace data into a table:

SELECT *
INTO #TraceStart
FROM MyTraceData
WHERE EventClass = 44

SELECT *
INTO #TraceEnd
FROM MyTraceData
WHERE EventClass = 45

SELECT TextData
FROM #TraceStart
EXCEPT
SELECT TextData
FROM #TraceEnd

I would expect this might be easier to do with Extended Events but have never tried to transfer this method over to Extended Events. I am not sure if SQL Server 2008's version of Extended Events opened up access to client level errors as 2012 and higher does. The above is just a quick and dirty method that still works.

Answer 3

Are you also checking for blocking?

Blocking could certainly contribute to timeouts and can be fairly easily tracked. A timeout that is not related to a block has some other issue. The 30 second timeout is a common clientsetting, but it can be controlled by the command object. If you set it to 0 then the connection will not timeout.

To set up monitoring for blocking using Event Notifications and Service Broker read Tony Rogerson's post:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/04/06/event-notifications-monitoring-blocked-processes-and-other-events-end-to-end-how-to-set-it-up-and-make-it-work.aspx

In his example, he monitors for blocks of 10 seconds or longer (and for every period increment, 20, 30, 40 seconds, etc.) I monitor for every 25 seconds, which gives me a close to the timeout look at what is running. Both the blocked and the blocking processes will show up in the XML description of the block.

Keeping the information in a table also provides history that you can review over time.