There’s no such thing as a query timeout…

...not at least as far as the database engine is concerned, anyway.  As frequently as the subject of query timeouts comes up with applications based on SQL Server, people are often surprised to learn that query timeouts are a client-side concept only.  Discounting situations in which SQL Server becomes a client to another server (e.g., with remote queries), the database engine itself knows nothing of query timeouts.  Like connection timeouts, query timeouts are a client-side concept only.

How, then, do query timeouts work?  A client signals a query timeout to the server using an attention event.  An attention event is simply a distinct type of TDS packet a SQL Server client can send to it.  In addition to connect/disconnect, T-SQL batch, and RPC events, a client can signal an attention to the server.  An attention tells the server to cancel the connection's currently executing query (if there is one) as soon as possible.  An attention doesn't rollback open transactions, and it doesn't stop the currently executing query on a dime -- the server aborts whatever it was doing for the connection at the next available opportunity.  Usually, this happens pretty quickly, but not always.

From a server standpoint, how do you know when a query timeout has occurred?  A tell-tale sign is a Profiler trace that shows the start of a language event or RPC execution, followed in sequence by an attention event (attentions generate a distinct type of Profiler trace event).  This by itself tells us that the query either timed out or was canceled by the client.  Next, look at the time signature of the attention event.  If the amount of time between the start of the preceding language event or RPC and the attention matches your query timeout setting on the client, you are likely seeing a query timeout from the server's perspective.

All of the client-side connection libraries for SQL Server support the notion of a query timeout setting.  A client app doesn't have to spawn a thread to watch the clock and forcibly issue an attention.  That's all transparent.  Depending on the connection library being used, the client either calls an API or sets a connection attribute in order to effect a query timeout setting.  Different apps expose this facility to end users in different ways.  Query Analyzer, for example, exposes its support of query timeouts via its Tools | Options | Connection dialog.  A timeout of zero indicates that there's no timeout at all.

Why do you care about query timeouts?  Because query timeouts can be indicators of performance problems.  If a query that normally takes five seconds to run times out at thirty, you know you have a problem, and you can tell this with nothing more than a simple Profiler trace collected during the execution of the query.  The underlying cause could be many things, but the presence of the attention event in the trace, along with its correlation with your client-side query timeout setting, is a red flag that should definitely get your attention. 

Usually, client apps indicate to the user when a query timeout occurs, but they don't have to.  I once debugged an app whose authors had intentionally hidden the fact that their poor-performing queries often timed out.  They'd constructed the app such that it simply resubmitted any timed-out queries.  This led to nasty locking and concurrency issues that were difficult to pin down until I noticed the attention events in the Profiler trace and their relationship to the client-side query timeout setting.

Comments (5)
  1. Bob D says:

    Is a kill command more aggressive than an attention event? Although we have a timeout set up in our VB.NET application, sometimes our DB server is running SQL that should have been killed by the timeout. Issuing a kill command will end the SQL statement, but when we have taken dumps of the client process we could not find any threads that were still connected to SQL.

    After reading this article I wondered if an attention event had been sent, but not headed.


  2. Stan Lin says:


    I have a SQL Profiler question that I can’t find answer anywhere: for the following SQL statement, is there anyway to find the actual SQL for @SQL , its CPU usage and Duration using SQL Profiler without changing the code?

    EXEC sp_executesql @SQL, N’@EvaluationID int, @Return_Value bit OUTPUT’,


    @Return_Value=@Return_Value OUTPUT

    Thanks in advance.


  3. Ravi says:

    To Stan Lin:


    Could you expalin how you can set an output parameter using sp_executeSQL. I thought the paremters declared as part of @params  in sp_executesql  can only be used as part of where clause in the @sql.

Comments are closed.

Skip to main content