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

Checklist for time out errors

 

Memory pressure: In most cases timeouts are caused by insufficient memory (i.e. memory pressure). There are different types of memory pressures and it is very important to identify the root cause. The following articles give a good start point on this issue:

 

https://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx

https://blogs.msdn.com/slavao/archive/2005/02/01/364523.aspx

https://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EWIAC (includes a link that explains DBCC MEMORYSTATUS)

Especially, we should pay attention to the size of buffer pool (since it is the source for query execution memory grant) and the size of memory held by query execution. You can use this simple query to get the size of buffer pool:

 

select sum(virtual_memory_committed_kb) from sys.dm_os_memory_clerks where type='MEMORYCLERK_SQLBUFFERPOOL'

The following query gives the size of memory held by query execution (available in SQL Server 2000 SP1 only):

 

select sum(total_memory_kb) from sys.dm_exec_query_resource_semaphores

 

Note: please be cautious when using sys.dm_exec_query_memory_grants and sys.dm_exec_query_resource_semaphores with an “order by” clause or a JOIN on a loaded system since this query may itself require a memory grant and it may experience a query execution time out. It is true even you use the DAC connection. DAC has a pre-committed memory for normal allocations, but not for memory grants. It will need to use regular resource semaphores for memory grants. The difference is: DAC query does not wait for memory grant and may force minimum grant. This will likely make OOM condition worse.

 

It is important to point out that more physical memory does not necessarily mean more memory for query execution. The memory that can be used by query execution is limited by process-addressable virtual address space, which is normally 2GB for 32-bit architectures. So on a 32-bit system, the maximum memory query execution can use is around 1.7G since the operating system and other SQL Server components (like optimization) need memory as well. Generally, you should expect around 1.2 GB of main memory for query execution since quite likely other components could require more memory in a loaded system. There is no such 2GB limit for a 64-bit system.

 

Option “min memory per query” (BOL link) . This option sets the minimum amount of memory (in kilobytes (KB)) that is allocated for the execution of a query. The default value is 1024 (KB) and the minimum allowed value is 512. Don’t make it too large if there are many ad hoc small queries: it simply wastes the memory since small queries won’t make full use of them.

Option “max server memory” (BOL link) . This option controls the maximum size of buffer pool, which is the source of query execution memory. If it is too small, there won’t be many queries running at the same time. Make sure this option is set to a reasonably large value.

 

Option “query wait” (BOL link) . This option specifies the time in seconds a query waits for memory before it times out. Check if it is set properly. We recommend leaving it as default, which is calculated as 25 times of the estimated query cost.

Update statistics. The amount of memory to be granted is mainly based on the cardinality estimation. So updating statistics could improve the accuracy of cardinality estimation and perhaphs reduce the waste on memory reservation. On the other hand, if statistics is out of date, AUTOSTAT can kick in during compilation, which typically uses big memory because it has to sort rows. If we cannot get grant for AUTOSTAT, we will use stale stats instead.

Identify the queries consuming (or that will consume) the most memory. If you ever decide to kill some queries to free up memory, it might be more efficient to kill queries that are using or will use a large amount of memory. Of course, executing a big query will make the situation worse. The following query shows the memory required by both running (non-null grant_time) and waiting queries (null grant_time).

select requested_memory_kb, grant_time, cost, plan_handle, sql_handle

from sys.dm_exec_query_memory_grants

Before you decide to kill a query, it is always recommended to check the showplan of that query. You should investigate if the plan cost and/or memory requirement exceed your expectation. You can use plan_handle to retrieve the showplan from sys.dm_exec_query_plan and sql_handle to retrieve the SQL text from sys.dm_exec_sql_text.

Comments

• Anonymous
  November 22, 2006
  The comment has been removed

• Anonymous
  December 18, 2006
  Slava, When you say "There is no such 2GB limit for a 64-bit system.", do you mean that there is no limit (except for the available memory) or is there still a certain treshold on 64-bit?

• Anonymous
  July 13, 2014
  Nice post