13

Let's say I have a query that is sent to my SQL-Server database, it takes more than 30 seconds, and my program throws an SQL Query Timeout exception. Is the query still chugging along on my database or does it get terminated as soon as the exception is thrown?

sooprise
  • 22,657
  • 67
  • 188
  • 276

5 Answers5

21

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.

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

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
4

When the client decides that the command has run long enough, it issues an "Abort". The query simply stops running in the database.

Any CATCH block won't be hit, transactions will be left open and locks can still remain allocated after this, even if the connection is closed because "close" means "return to connection pool".

If you expect a lot of Command Timeouts then consider using SET XACT_ABORT ON (and this too) that will release locks and rollback transactions. or fix the code...

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • You said "The query simply stops running in the database"; Martin Smith's answer said "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". Which is correct? – simonp Mar 22 '17 at 12:28
  • Same result, different language. My "stop'" may be delayed based on server load, in-flight IO, whatever WAITs are in play etc – gbn Mar 23 '17 at 13:30
1

Before executing a query, SQL Server estimates how much memory it needs to run and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds the query is executed immediately. If there is not enough memory readily available from the buffer pool, then the query is put into a queue with a timeout value, where the timeout value is guided by the query cost. The basic rule is: higher the estimated cost is, larger the time out value is. When the waiting time of this query exceeds the timeout value, a time out error is thrown and the query is removed from the queue.

Source

Richard Nienaber
  • 10,324
  • 6
  • 55
  • 66
  • The mention of 30 seconds in the question indicates that the OP is asking about command timeouts (from the client) not memory grant timeouts or lock timeouts. – Martin Smith Apr 27 '11 at 21:32
  • The memory grant timeout is a different thing from a ADO.Net client request timeout, which is the 30 seconds the OP is referring to – Remus Rusanu Apr 27 '11 at 22:32
0

If you get a SQL timeout then SQL has stopped, however web applications can time out and the SQL query can continue.

Dustin Laine
  • 37,935
  • 10
  • 86
  • 125
-3

Usually when it times out it means the connection has died, meaning the query has not been sent to the database, most database support Transactions where you can start a transaction, run your queries, and if your happy you can commit them.

Example:

BEGIN TRAN

UPDATE  authors
   SET  au_fname = 'John'
WHERE   au_id = '172-32-1176'

UPDATE  authors
   SET  au_fname = 'Marg'
WHERE   au_id = '213-46-8915'

COMMIT TRAN
RobertPitt
  • 56,863
  • 21
  • 114
  • 161
  • In this case, the connection is still open, but the database is under a heavy load, causing queries to take a long time. – sooprise Apr 27 '11 at 21:37
  • Are you talking about connection timeout or something? That is different from command timeout. – Martin Smith Apr 27 '11 at 21:48
  • My point is that if he doing connections to a server and he need's to make sure that they go threw to the actual database, using transactions will help prevent errors that can sometimes be caused by timeout's – RobertPitt Apr 27 '11 at 21:55
  • Your first sentence talks about "meaning the query has not been sent to the database" which would indicate some problem with the connection itself to me? Then when it stops trying to connect will be governed by the connection timeout property. The command timeout occurs when running a time consuming batch and is implemented by the client libraries. [They vary in how they handle open transactions](http://www.sommarskog.se/error-handling-II.html#timeout) – Martin Smith Apr 27 '11 at 22:03
  • 4
    -1 for utter confusion. A transaction will be left open on command timeout – gbn Apr 28 '11 at 04:46