Is it possible to set a timeout for an SQL query on Microsoft SQL server

Question

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

User · Answer

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

User · Answer

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

User · Answer

enter image description here You can set Execution time-out in seconds

User · Answer

You can specify the connection timeout within the SQL connection string  when you connect to the database  like so   quot Data Source localhost Initial Catalog database Connect Timeout 15 quot   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Is it possible to set a timeout for an SQL query on Microsoft SQL server?

Examples related to sql-server

Examples related to timeout