October 21, 2013 at 5:24 pm
Hi All,
We are runnning sql 2008 standard.
We have been getting timeouts on one of our systems which we have been able to eliminate by implementing some indexes. Presently we are trying to work out the cause of the timeouts.
The thing is we originally had a web app that was timing out at 10 mins. We build a mirror program with a bit more error checking that was not dependent on iis (which had a 10 min timeout period) and the the process still timed out at 10 mins.
Now I have always understood that sql won't timeout for incoming queries (unless you set a lock timeout period which is set to infinite on our machine)
I am beginning to doubt that sql dbengine does not timeout anymore - is there any option you can use to make a query timeout?
I did notice there was an server option called "query waits" which is set to -1. Apparently this means the time-out is calculated as 25 times of the estimated query cost.
If that the case How would you work out the query cost to see what the timeout value is?
October 22, 2013 at 12:54 am
SQL Server has no concept of query timeouts for queries it's running. Timeout is a client application setting. The client application decides it's waited too long and tells SQL server to stop running the query.
As for how you fix them - generally tune your queries. 10 minutes is way too long for a query from a web site to take. No user is going to sit and wait ten minutes for something to happen, they'll be long gone way before that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 12:58 am
thanks.. database tuned and running much faster.
what is the query waits server setting about then?
Also when using -1 (default value) it is 25 * the query cost it takes to run - do you know how that is calculated (ie query cost)?
October 22, 2013 at 12:59 am
djordan 4543 (10/21/2013)
I am beginning to doubt that sql dbengine does not timeout anymore - is there any option you can use to make a query timeout?
No. The only query timeout SQL has is for when it is running a query on a remote server (linked servers)
I did notice there was an server option called "query waits" which is set to -1. Apparently this means the time-out is calculated as 25 times of the estimated query cost.
Nope.
Straight from Books Online:
Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.
eg, waiting for memory grants when there's insufficient workspace memory available. This is not a general query timeout and it has quite distinctive error messages that get thrown
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:31 am
will that get shown in profiler under user error messages ?
or perhap the errorlog?
October 22, 2013 at 1:33 am
Profiler it should do, otherwise catch the error in the application. Don't run profiler against a busy production server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2013 at 1:36 am
thanks!:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply