Michael J. Swart

November 6, 2011

Insights into “Timeout Expired”

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , , , — Michael J. Swart @ 8:53 pm
Timeout Expired. The timeout period elapsed prior to the completion 
of the operation or the server is not responding

Many of you readers will recognize this error message. This error message is telling you that a query took too long to execute and caused this exception to be thrown. And unless you’ve changed the CommandTimeout property on your SqlCommand to a different value, then “too long” means longer than 30 seconds.

Logically, this means that if a query takes longer than 30 seconds to execute, the client will do two things:

  1. Tell the server to cancel the SQL Batch and
  2. Throw an exception to be handled by the application.

So in a diagram, that looks something like this:

Logical sequence diagrams

But that’s not the whole picture. Specifically, it doesn’t help me answer these questions:

  • So if the query has 30 seconds, does the stopwatch start when I send the query to the server?
  • Does the  stopwatch stop when I get the first row?
  • What if the client spends time processing data instead of fetching rows from the server (like when saving data to disk). Is that “on the clock”?
  • Why do I sometimes see timeouts on queries after more than 30 seconds?
  • Why do I sometimes see queries not time out even though they take longer than 30 seconds?

Microsoft documentation clears some of this up in SqlCommand.CommandTimeout Property. They explain:

Note: This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

Using that note, we know that processing time is not “on the clock”. The timer is only running down when the client is waiting for a response from the server.

Tabular Data Stream (TDS)

We can answer the rest of the questions if we experiment a bit and if we understand exactly how the server and client communicate with eachother. Tabular Data Stream (TDS) is the conversation protocol that Microsoft uses to let database clients and servers communicate. It’s a set of messages and responses. TDS lets servers authenticate clients, it lets clients send SQL to the servers and it lets servers return data to clients. So if the previous diagram is more of a logical diagram, then here’s a diagram of the same simple query as a TDS conversation:

TDS Sequence Diagram

See how the row data response has been split into three packets? In testing I see that these packets can be 8 kilobytes in size so these packets usually contain several rows.

Now here’s a possible TDS conversation with a timeout:

TDS Sequence Diagram (with timeout)

Although the official docs say the time is cumulative, in practice, I only see timeouts when 30 seconds has elapsed between packets regardless of wait times for previous packets.

So based on this, what do we know?

  • The timeout stopwatch starts ticking when the query is sent to the server. It also restarts when the client needs data from the next packet of row data.
  • The stop watch stops when the client receives a packet from the server.
  • If the client takes a long time processing data sent from the server, this will not impact timeouts (I didn’t know this fact until recently).
  • Queries can take longer than the defined timeout period without timing out in situations like these:
    1. if either the client is processing data slowly or
    2. the server is responding slowly but steadily (i.e. without gaps between packets that exceed the timeout period).
  • In short, a timeout always represents server unresponsiveness as seen by the client.

Other Resources

7 Comments »

  1. [...] Insights into “Timeout Expired” - Michael J. Swart (Blog|Twitter) [...]

    Pingback by Something for the Weekend – SQL Server Links 11/11/11 — November 11, 2011 @ 10:20 am

  2. Thank you for the detail explanation and diagrams illustration!

    It certainly cleared my doubts on the CommandTimeout Property.

    Comment by Dan — March 22, 2012 @ 2:02 am

  3. I didn’t know it was possible to write an interesting blog post on a TDS sequence… but you’ve done it!

    Comment by Kendra Little — August 10, 2013 @ 12:08 pm

  4. Hi,

    This error seems to be running for a while without conclusion. So with this can we determine that there could be an issue with network delay. Kindly provide the solution for this.

    Comment by Manoj — November 18, 2013 @ 1:13 pm

  5. Hi Manoj,
    Don’t blame the network just yet. I would consider a lot of other reasons for timeouts before I would consider problems with the network.

    You didn’t provide enough information to let me provide a solution, but most often, a timeout means that a query is performing a lot of work and is simply long-running.

    I use sp_whoisactive on the server to find active long running queries.
    And I use sp_whoisactive @get_plans = 1; to learn about why a query is taking so long. That’s where I would start.

    But starting is just the first step. Learning to tune or avoid long running queries is an important skill, but it takes time. I wish you good luck.

    Comment by Michael J. Swart — November 18, 2013 @ 1:49 pm

  6. I didnt conclude network issue. Anyways thanks. One thing i noticed is, the SP runs fine when ran from SSMS, but fails from app end, Also i ran profiler to see whether the procedure hits the db. I could see SP started in DB server sent from app server, but then fails. So my colleague said paramter sniffing could be issue, i dont think so.
    Any thoughts………..?

    Comment by Manoj — November 18, 2013 @ 1:57 pm

  7. Hi Manoj,
    I think it’s important to get the query plan of the query (and not necessarily from SSMS).
    Some relevant links that might help:

    These links might not help you directly, but they might give you ideas.

    If you can reproduce the problem, or if you can capture the execution plan associated with poor performance, try asking on SQLPerformance.com

    If you have further problems, shoot me an email.

    Comment by Michael J. Swart — November 18, 2013 @ 2:11 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress