A configuration option in SQL Server that is often overlooked or misapplied is the Query Wait configuration option. This option determines how long a query will wait for a resource (memory) with the default value of -1. The default value means that a query will wait for approximately 25 times the estimated cost of the query before it times out while waiting for memory. An example would be a query with an estimated cost of 10 would wait for 250 seconds before timing out, slightly over four minutes.
Note: If the estimated cost is below 1 then the query will wait for 25 seconds before timing out
It is easy to determine what your value is for this option, chances are it is at the default and most people do not interact with this configuration option very often.
–Review the current query wait setting (2005 and 2008)
SELECT * FROM sys.configurations
WHERE configuration_id = 1541
–Review the current query wait setting (2000)
EXEC sp_configure ‘show advanced’,1
RECONFIGURE
GO
EXEC sp_configure ‘query wait’
So why would the option have a different setting or why would you need to set the option to a different value considering that most complex queries have estimated costs over 10 that would have then waiting for over four minutes for memory? Well sometimes a query timeout happens. Queries who are often large consumers of memory (queries with hashing and sorting) or queries being parallelized are perfect candidates to time out due to lack of memory resources.
How do we know that queries are timing out? Usually DBAs start getting indications of query timeouts by seeing the following error in their error log:
Error 8645: A time out occurred while waiting for memory resources to execute the query. Re-run the query.
You may often get other conditions due to query timeouts that are a little harder to track back to the query timeout. What are some of these conditions?
· New users receiving “login failed” error messages
· User are disconnected with various error messages
· You see very high CPU usage
· You notice processes with wait types of 0x40, 0x0040 and last wait types of RESOURCE_SEMAPHORE
· System Monitor object SQLServer:Memory Manager has non zero values for grants pending
· Profiler or SQL Trace reports “Execution Warnings” events which include “Wait For Memory” or “Wait For Memory Timeout” in the text
Since the first few conditions on that list are generic enough we probably will not think of looking for query timeouts when there are a 100 over things that cause those conditions as well and the last few conditions actually require us to be trapping something before it occurs, chances are we are only going to find out after getting the error message in our error logs. But what if you wanted to be pro-active and trap for the query timeout in case the error is not seen in the error log?
As listed above there are several ways to trap for query timeouts. The System Monitor (Perfmon to us non Microsoft folks) SQLServer:Memory Manager counters Memory Grants Pending and Memory Grants Outstanding are good choices to have in your performance monitoring solution. SQL Server Profiler or SQL Trace can be used by including the Execution Warnings event, this is found under the Errors and Warnings event category. Once you are capturing this, you will need to scan the text output of this event to search for “Wait For Memory” or “Wait For Memory Timeout” in the text.
Want a SQL Server method to find out if you have query timeouts without waiting for the error or running another application like System Monitor or Profiler? There are several ways to find these query timeouts using T-SQL and SQL Server’s catalog views and dynamic management views.
If you are using SQL Server 2000, you really only have one option. This is to review the output of the dbo.sysprocesses system table.
–Review output of dbo.sysprocesses
SELECT spid
,dbid
,uid
,status
,sql_handle
,waittype
,waittime
,lastwaittype
,cmd
,hostname
,program_name
,nt_domain
,nt_username
,loginame
FROM master.dbo.sysprocesses
WHERE waittype IN (0x040, 0x0040)
AND lastwaittype = ‘RESOURCE_SEMAPHORE’
–Review the text of each process that in the output list
SELECT * FROM ::fn_get_sql(0x0100010094BD2010C8F61E1E0000000000000000)
If you are on SQL Server 2005 or 2008, you can get more information by using a combination of system catalog views and dynamic management views and functions.
–Replacement for master.dbo.sysprocesses
SELECT r.session_id — new column for SPID
,r.database_id
,r.user_id
,r.status
,st.text
,r.wait_type
,r.wait_time
,r.last_wait_type
,r.command
,es.host_name
,es.program_name
,es.nt_domain
,es.nt_user_name
,es.login_name
,mg.dop –Degree of parallelism
,mg.request_time –Date and time when this query requested the memory grant.
,mg.grant_time –NULL means memory has not been granted
,mg.requested_memory_kb –Total requested amount of memory in kilobytes
,mg.granted_memory_kb –Total amount of memory actually granted in kilobytes. NULL if not granted
,mg.required_memory_kb –Minimum memory required to run this query in kilobytes.
,mg.query_cost –Estimated query cost.
,mg.timeout_sec –Time-out in seconds before this query gives up the memory grant request.
,mg.resource_semaphore_id –Nonunique ID of the resource semaphore on which this query is waiting.
,mg.wait_time_ms –Wait time in milliseconds. NULL if the memory is already granted.
,CASE mg.is_next_candidate –Is this process the next candidate for a memory grant
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
ELSE ‘Memory has been granted’
END AS ‘Next Candidate for Memory Grant’
,rs.target_memory_kb –Grant usage target in kilobytes.
,rs.max_target_memory_kb –Maximum potential target in kilobytes. NULL for the small-query resource semaphore.
,rs.total_memory_kb –Memory held by the resource semaphore in kilobytes.
,rs.available_memory_kb –Memory available for a new grant in kilobytes.
,rs.granted_memory_kb –Total granted memory in kilobytes.
,rs.used_memory_kb –Physically used part of granted memory in kilobytes.
,rs.grantee_count –Number of active queries that have their grants satisfied.
,rs.waiter_count –Number of queries waiting for grants to be satisfied.
,rs.timeout_error_count –Total number of time-out errors since server startup. NULL for the small-query resource semaphore.
,rs.forced_grant_count –Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions es
ON r.session_id = es.session_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON r.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)st
–Is anything timing out
SELECT * FROM sys.dm_exec_query_optimizer_info
WHERE counter = ‘timeout’
This combination of dynamic management views in SQL Server 2005 or 2008 can give you a much better description of what is going on and hoping help you troubleshoot your query timeouts.
