Query Execution Timeouts in SQL Server (Part 1 of 2)

This short article provides a checklist for query execution time out errors in Yukon. It does not touch the time out issues on optimization and connection. Before reading this article, you are recommended to read the following post to get familiar with SQL Server memory management architecture: https://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

Overview of query processing

When a query is submitted, SQL Server first checks if there is a plan cached. If yes, that plan will be selected. If not, the query statement is first parsed to generate sequence tree. The sequence tree is then bound and normalized and converted to algebras tree. Algebras tree is optimized to generate algebraic plan.

When an optimized plan is generated (or selected if it is cached), it will be executed if its memory requirement can be satisfied right away. If not, which happens quite often, the query is put into a queue and waiting for the memory.

How does a query execution time out happen?

Before executing a query, SQL Server estimates how much memory it needs to run and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds the query is executed immediately. If there is not enough memory readily available from the buffer pool, then the query is put into a queue with a timeout value, where the timeout value is guided by the query cost. The basic rule is: higher the estimated cost is, larger the time out value is. When the waiting time of this query exceeds the timeout value, a time out error is thrown and the query is removed from the queue. The following shows a sample error for time out:

[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 the memory is enough for a newly submitted query but there are queries in waiting queues, this query is put into a queue. Queries in waiting queues are “sorted” based on their cost and waiting time. Less cost or longer waiting time a query has, higher it is ranked. Note the ranking is dynamic and changes frequently. The query with the highest rank will run if there is enough free memory. If the memory is insufficient, then no other queries will run. It will NOT bother to check if the free memory is enough to run other queries. You can check which query is next to be picked up by running the following query. If the returns no rows, then there are no waiting queries. Note: the results from this query change with time.

select * from sys.dm_exec_query_memory_grants where is_next_candidate is not null

You can use the value in the plan_handle column to retrieve the showplan from sys.dm_exec_query_plan and the sql_handle column to retrieve the SQL text from sys.dm_exec_sql_text.

Note that not every query needs a memory reservation. Usually a query needs a memory reservation if its execution plan has sort, hash, or bitmap operators. Since an index build requires a sort, it always needs a memory reservation. If a query needs no memory reservation, it is immediately executed.

- Senqiang Zhou

Query Execution

Comments

• Anonymous
  November 28, 2006
  The comment has been removed
• Anonymous
  December 11, 2009
  The comment has been removed
• Anonymous
  December 22, 2009
  Short and good information. Thanks