Sdílet prostřednictvím

There's no such thing as a query timeout...

  • Článek

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

  • Anonymous
    October 24, 2005
    The comment has been removed

  • Anonymous
    February 21, 2006
    Ken,

    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',
    @EvaluationID=@EvaluationID,
    @Return_Value=@Return_Value OUTPUT

    Thanks in advance.


    Stan

  • Anonymous
    July 12, 2006
    <a href="http://www.diyyourhome.com>diyyourhome.com</a>

  • Anonymous
    September 18, 2007
    To Stan Lin: Stan, 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.

  • Anonymous
    January 20, 2009
    PingBack from http://honeyvirus.wordpress.com/2009/01/21/query-timeouts-a-client-side-feature-only/