22

I've got a scenario when sometimes a user selects the right parameters and makes a query which takes several minutes or more to execute. I cannot prevent him to select such a combination of parameters (it's quite legal), so I'd like to set a timeout on the query.

Note that I really want to stop the query execution itself and rollback any transactions, because otherwise it hogs up most of server resources. Add an impatient user who restarts the application and tries the combination again, and you've got a recipe for a disaster (read: SQL Server DoS).

Can this be done and how?

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Vilx-
  • 104,512
  • 87
  • 279
  • 422
  • Is there any reason why you cannot set this in code, on the connection object? – Oded Jun 22 '10 at 09:23
  • @Oded - Because I want it on the server level, not the client. I can set it on the client connection, but that will only terminate my connection. The query will continue to execute on the server until it completes. – Vilx- Jun 22 '10 at 09:28
  • as far as I know, there is no way to do this on a query by query basis. `sp_configure` allows setting timeouts, but I believe they are server scoped. – Oded Jun 22 '10 at 09:46
  • @Oded - That's unfortunate. :( Care to make it an answer so I can accept it? – Vilx- Jun 22 '10 at 09:53

8 Answers8

14

As far as I know, apart from setting the command or connection timeouts in the client, there is no way to change timeouts on a query by query basis in the server.

You can indeed change the default 600 seconds using sp_configure, but these are server scoped.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 13
    You should clarify what server scoped means. SQL Serve has a remote query timeout value that refers to its queries issued on over linked servers, not to queries issued by clients to it. I believe the query timeout is a client property, not a server property. The server runs the query indefinitely. There is such a thing as a query governor for addressing this issue which is disabled by default. – Paul-Sebastian Manole Nov 04 '15 at 15:33
9

Humm! did you try LOCK_TIMEOUT
Note down what it was orginally before running the query
set it for your query
after running your query set it back to original value

SET LOCK_TIMEOUT 1800;  
SELECT @@LOCK_TIMEOUT AS [Lock Timeout];
Talha
  • 1,546
  • 17
  • 15
  • 1
    if you are fetching data from sql server using c# then SqlCommand object is where you can set Timeout. – Talha Jun 19 '16 at 15:52
  • 1
    This is the right answer, at least for my requirement, I can see that management studio has a value of -1 (infinite), if I set it to 1000 it will timeout at 1 second. In my case, I have some rows that seems to be locked, but others don´t so I scripted a foreach row, and I set a small timeout to jump rows that are locked. – Yogurtu Dec 27 '17 at 19:31
  • If the implication is that running SET LOCK_TIMEOUT 1800 would cause any query that takes at least 1.8 seconds to run to error out then this didn't work for me. – influent Feb 12 '22 at 19:48
  • Also it's not a server or database setting: "At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection." – influent Feb 12 '22 at 19:52
4

I might suggest 2 things.

1) If your query takes a lot of time because it´s using several tables that might involve locks, a quite fast solution is to run your queries with the "NoLock" hint.

Simply add Select * from YourTable WITH (NOLOCK) in all your table references an that will prevent your query to block for concurrent transactions.

2) if you want to be sure that all of your queries runs in (let´s say) less than 5 seconds, then you could add what @talha proposed, that worked sweet for me

Just add at the top of your execution

SET LOCK_TIMEOUT 5000;   --5 seconds.

And that will cause that your query takes less than 5 or fail. Then you should catch the exception and rollback if needed.

Hope it helps.

Yogurtu
  • 2,656
  • 3
  • 23
  • 23
  • Well, this was more than 7 years ago, so I don't remember what the query was anymore. :) But I think it had to do with simply lots of data (several large tables getting multiplied together) rather than locks. – Vilx- Dec 28 '17 at 00:05
  • 1
    down voted for the 'with (nolock)' suggestion, without a big warning attached. NOLOCK allows many query errors to occur including : skipped rows that match, returned rows that don't match your query, and returning the same row twice. – Andrew Hill Dec 13 '18 at 23:25
  • 1
    I never saw noLock doing what you mentioned, NoLock just allow dirty read, if you have things not matching/matching or repeated data in your result, is a problem in your query, the only "suggestion" is that you might be reading data that might be actually changing in the same millisecond you run the query, but not what you said. – Yogurtu Dec 31 '18 at 18:55
4

In management studio you can set the timeout in seconds. menu Tools => Options set the field and then Ok

enter image description here

Bellash
  • 7,560
  • 6
  • 53
  • 86
  • My question was about doing this from code. Do you know _how_ Management Studio achieves its timeout? – Vilx- Dec 03 '19 at 13:17
  • Also, does the timeout actually terminate the query, or just disconnects while the query keeps running? – Vilx- Dec 03 '19 at 13:19
  • This is just for saving changes in designers. After the timeout, the query will terminate (and rollback if needed) – tim654321 Apr 13 '23 at 23:01
0

It sounds like more of an architectual issue, and any timeout/disconnect you can do would be more or less a band-aid. This has to be solved on SQL server side, by the way of read-only replica, transaction log shipping (to give you a read-only server to connect to), replication and such. Basically you give the DMZ sql server that heavy read can go to without killing stuff. This is very common. A well-designed SQL system won't be taken down by DDoS - that'd be like a car that dies if you step on the gas.

That said, if you are at the liberty to change the code, you could guesstimate if the query is too heavy and you could either reject or return only X rows in your stored procedure. If you are mated to some reporting tool and such and can't control the SELECT it generates, you could point it to a view and then do the safety valve in the view.

Also, if up-to-the-minute freshness isn't critical and you could compromise on that, like monthly sales data, then compiling a physical table of complex joins by job to avoid complex joins might do the trick - that way everything would be sub-second per query.

It entirely depends on what you are doing, but there is always a solution. Sometimes it takes extra coding to optimize it, sometimes it takes extra money to get you the secondary read-only DB, sometimes it needs time and attention in index tuning.

So it entirely depends, but I'd start with "what can I compromise? what can I change?" and go from there.

Jun Sato
  • 111
  • 2
0

You can set Execution time-out in seconds.

enter image description here

4b0
  • 21,981
  • 30
  • 95
  • 142
Matt Nis
  • 1
-1

If you have just one query I don't know how to set timeout on T-SQL level.

However if you have a few queries (i.e. collecting data into temporary tables) inside stored procedure you can just control time of execution with GETDATE(), DATEDIFF() and a few INT variables storing time of execution of each part.

Grzegorz Gierlik
  • 11,112
  • 4
  • 47
  • 55
  • I want it to be a generic mechanism which I incorporate in the framework, so this doesn't really work. :( – Vilx- Jun 22 '10 at 09:48
-3

You can specify the connection timeout within the SQL connection string, when you connect to the database, like so:

"Data Source=localhost;Initial Catalog=database;Connect Timeout=15"

On the server level, use MSSQLMS to view the server properties, and on the Connections page you can specify the default query timeout.

I'm not quite sure that queries keep on running after the client connection has closed. Queries should not take that long either, MSSQL can handle large databases, I've worked with GB's of data on it before. Run a performance profile on the queries, prehaps some well-placed indexes could speed it up, or rewriting the query could too.

Update: According to this list, SQL timeouts happen when waiting for attention acknowledgement from server:

Suppose you execute a command, then the command times out. When this happens the SqlClient driver sends a special 8 byte packet to the server called an attention packet. This tells the server to stop executing the current command. When we send the attention packet, we have to wait for the attention acknowledgement from the server and this can in theory take a long time and time out. You can also send this packet by calling SqlCommand.Cancel on an asynchronous SqlCommand object. This one is a special case where we use a 5 second timeout. In most cases you will never hit this one, the server is usually very responsive to attention packets because these are handled very low in the network layer.

So it seems that after the client connection times out, a signal is sent to the server to cancel the running query too.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
invert
  • 2,016
  • 14
  • 20
  • 5
    That is the connection timeout which is something completely different from what I want. This will timeout if it cannot connect to the server (or cannot get a connection from the pool). I want a timeout when I'm executing some long-running query. – Vilx- Jun 22 '10 at 09:42
  • Ah I see now, sorry I misunderstood. – invert Jun 22 '10 at 09:46