Systems Engineering and RDBMS

Query Wait Setting

Posted by decipherinfosys on February 14, 2008

Sometime ago, we had posted on how to put cost limits on bad queries in order to prevent the runaway queries from hogging resources. In this post, we will cover another option called the Query Wait option which determines how long a query will wait for memory resource before timing out. It’s default value is -1 which means that it will wait 25 times (in seconds) the estimated cost of the query before it times out with the error 8645. If you run this query in SQL Server 2005:

SELECT * FROM sys.configurations WHERE name like ‘query%’

You can see the 2 records being for options:

query wait (s)
query governor cost limit

The second one we had covered before and the first one is what we are talking about in this post. In SQL Server 2000 (or even in 2005), you can also check the value for this option using sp_configure:

EXEC sp_configure ‘query wait’
GO

Also note that if the estimated cost is less than 1, then the max wait time would be 25 seconds. In order to simulate the error, I changed the query wait to a value of 1 and then executed a very complex query on large tables with no indexes and I got the error:

Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.

Because of query timeouts, you can get other error conditions as well like login failed error messages, very high CPU utilizations and the RESOURCE_SEMAPHORE wait events for the sessions. If you are tracing through profiler, then these will show up if you have the “Execution Warnings” event checked in the trace. The TextData column will show “Wait For Memory” messages. If you want to see whether anything is timing out in your environment, you can use this DMV in SQL Server 2005:

SELECT * FROM sys.dm_exec_query_optimizer_info WHERE counter = ‘timeout’

The occurence column will tell you the number of times that counter has been incremented and the value column is an internal column for this counter.

One Response to “Query Wait Setting”

  1. […] also set limits on bad queries (could be done in prior versions in a different way – see here and here) and put priority – either at the user level so that some users have a higher priority than others […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: