Query Execution Time outs because of lack of memory
Posted by decipherinfosys on November 22, 2008
We have discussed the different kind of timeouts before and have also covered how one can prevent a runaway query by using some simple options. You can read those posts here and here. In today’s post, we will cover the query execution time outs that you might have seen at times in busy systems which have an error like:
[State:42000 Error:8645] [Microsoft][SQL Native Client][SQL Server]A time out occurred while waiting for memory resources to execute the query. Rerun the query.
If you are new to SQL Server memory management and would like to get a good understanding of it, here is a MSDN post on that topic. If you remember from this post, the steps performed (at a very high level) when a query gets submitted to the engine are parsing, compilation and then execution. Prior to the parsing, if the execution plan is already in the cache, then the engine selects it. If it is not in the cache, then it parses it to prepare a sequence tree and that is then converted into algebras tree to generate the algebraic plan. The execution of the query depends upon whether enough memory is available from the buffer pool to satisfy the requirements of that query. If enough memory is not available, then the query is put into a queue with a timeout value and has to wait for the memory to become available. This timeout value is based off the estimated query cost – the higher the query cost, the higher the timeout value. So, if enough memory is not available prior to hitting that timeout value, that is when you will see the error message that is shown above.
When the error occurs, the query is also removed from the queue. This raises an interesting question which a client DBA had asked us – so, if there are already quite a few queries in the queue and a new query is submitted for which there is enough memory available for the execution, how does the queue work? Haven’t researched that completely yet but our understanding is that the ranking in the queue is a combination of the cost and the wait time. The lower the cost or the higher the wait time, the higher the ranking but am not sure about that – have to test that out or post a question on the MSDN forums.
However, while looking into the DMVs, we did see this DMV: sys.dm_exec_query_memory_grants which provides us with the information pertaining to the queries that have acquired a memory grant or are in a waiting status. The column “is_next_candidate” can be used to find out whether the query has already been granted memory or whether it is a candidate for the next memory grant. Here is a simple query on this DMV to get information on those queries which have not been granted memory and are waiting:
where is_next_candidate in (0,1)
order by is_next_candidate desc, queue_id, wait_order;
Also, we can use the CROSS APPLY operator to get this DMV joined to sys.dm_exec_query_plan in order to get the execution plan for the query as well as with sys.dm_exec_sql_text to get the exact query:
,db_name([dest].[dbid]) as database_name
,object_schema_name([dest].[objectid], [dest].[dbid]) as [schema_name]
,object_name([dest].[objectid], [dest].[dbid]) as [object_name]
FROM sys.dm_exec_query_memory_grants sdeq
CROSS APPLY sys.dm_exec_sql_text(sdeq.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(sdeq.plan_handle) deqp
where sdeq.is_next_candidate in (0,1)
order by is_next_candidate desc, queue_id, wait_order