Michael J. Swart

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

• TDS specification
• Data Access Tracing or BID Tracing
• SqlCommand.CommandTimeout Property