CODEWITHSUNDEEP
c#sql
sgtz
sgtz

Reputation: 9019

sensible SQL timeouts

When you execute a query against a database, what is a sensible timeout strategy. Normally I just leave the default in place, but wondered if there was a sensible rule of thumb? What would a DBA typically like to see?

Upvotes: 3

Views: 195

Answers (3)

user596075
user596075

Reputation:

This is one of those "it depends" answers. There are a lot of expensive queries on millions of rows of data that timeout with the default setting.

What a strategy I use is to set that to 0 (in other words, no timeout), and then use SQL Profiler (run a trace) to get the duration. I multiple that times 1.15 to give myself a 15% buffer so that I don't timeout due to system latency or just a "slow" execution. But in my opinion, if 115% of the time has passed when tested and it is not completed, 9 times out of 10 something went wrong and a timeout is in order.

Upvotes: 2

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I think this is really dependent on what you are doing. If we are talking about reporting, then you'll probably need a much higher timeout than if you are doing simple selects/inserts/deletes.

We normally only allow 30s on non-reporting queries and 3m on our reporting server.

Upvotes: 2

Daniel James Bryars
Daniel James Bryars

Reputation: 4621

If you are connecting to SQL Server using the SqlCommand and SqlConnection classes, then there are two timeouts you need to think about:

SqlConnection.ConnectionTimeout

The time needed to get a connection to the database, this defaults to 30 seconds which in my opinion is much too long. We reduce that to 5 seconds.

And

SqlCommand.CommandTimeout

Which obviously depends on the query you are running.

Upvotes: 1

Related Questions