Can I set a timeout for a SQL query on a Microsoft SQL server?

Question

I have a scenario where sometimes the user selects the correct parameters and makes a request that takes several minutes or more to complete. I can’t stop him from choosing such a combination of parameters (this is completely legal), so I would like to set a timeout in the request.

Please note that I really want to stop the request and cancel any transactions, because otherwise it starts most of the server resources. Add an impatient user who will restart the application and try the combination again, and you have a recipe for disaster (read: SQL Server DoS).

Can this be done and how?

Answer 1

As far as I know, in addition to setting the timeouts of a command or connection in the client, there is no way to change the timeouts based on a request by request on the server.

You can really change 600 seconds by default with sp_configure , but they are server covered.

Answer 2

Hamm! you tried LOCK_TIMEOUT
Record what it was originally before starting the request
set it for your request
after executing the request, set it back to its original value

 SET LOCK_TIMEOUT 1800; SELECT @@LOCK_TIMEOUT AS [Lock Timeout]; 

Answer 3

I could offer 2 things.

1) If your request is time consuming because it uses multiple tables that may include locks, a fairly quick solution is to run your queries using the NoLock prompt.

Just add Select * from YourTable WITH (NOLOCK) to all your table references, which will not allow your request to block concurrent transactions.

2) if you want to be sure that all your requests are executed (let them say) for less than 5 seconds, then you can add what @talha suggested, which worked for me sweetly

Just add to the start of execution

 SET LOCK_TIMEOUT 5000; --5 seconds. 

And this will cause your request to take less than 5 or fail. Then you should catch the exception and rollback if necessary.

Hope this helps.

Answer 4

If you have only one query, I don’t know how to set the timeout at T-SQL level.

However, if you have several queries (i.e. data collection in temporary tables) inside the stored procedure, you can simply control the execution time with GETDATE() , DATEDIFF() and several INT variables the storage time of each part.

Answer 5

You can specify the connection timeout in the SQL connection string when connecting to the database as follows:

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

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

I'm not quite sure that requests continue to work after the client connection is closed. Requests should not last so long, MSSQL can process large databases, I worked with GB data on it before. Run a performance profile in queries, presumably some well-placed indexes may speed it up, or you might need to rewrite the query.

Update: In accordance with this list, SQL timeouts occur when waiting for confirmation from the server:

Suppose you execute a command, then the command is disconnected. When this happens, the SqlClient driver sends a special 8-byte packet, called the attention packet, to the server. This tells the server to stop executing the current command. When we send a packet of attention, we need to wait for confirmation from the server, and this could theoretically take a lot of time and time. You can also send this package by calling SqlCommand.Cancel on an asynchronous SqlCommand object. This is a special case when we use a 5 second timeout. In most cases, you will never come across this, the server is usually very sensitive to attention packets because they are very low at the network level.

Thus, it seems that after the connection time with the client has expired, a signal is sent to the server to cancel the executed request.