Why query execution timeout happens and find queries that have granted memory or waiting on memory?
Have you ever identified the reasons for why and how does a query execution timeout happens within your SQL Server environment. For the beginners I would like to explain that 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 rule and practice here 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 SQL engine will perform better 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. You have queries that are not properly optimized and therefore consume more resources than necessary and memory that you allocated to SQL Server is not big enough for the work load that you are running on SQL Server.
So the task is to identify the queries that are consuming or that will consume the most memory, general practice is to find them using SP_WHO or SP_WHO2 statement and you may decide to kill those queries that are consuming such an amount of memory. You must check or verify the execution plan for such long running queries before executing against the stressed SQL Server database. And 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 or not using required index or not. You can make use of DMVs to get plan_handle information by retrieving the showplan from sys.dm_exec_query_plan and sql_handle to retrieve the SQL text from sys.dm_exec_sql_text.
Also you might observe error 8645 within the SQL Server error log due to these memory-intensive queries, such as those involving sorting and hashing, are queued and are unable to obtain the requested memory before a time-out period. It is better to check the query wait configuration value which is by default set to -1. For any reason if you modify the query wait value to a non-negative number, SQL Server waits for the number of seconds that you specify in the value of the query wait option. Refer to this KBA309256 for further information to resolve this error.
For the current assessment to find the long running queries you can use the following query that 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
Then coming to find the queries that have granted memory for the queries and/or waiting on memory to grant you can run the following query using dm_exec_query_memory_grants DMV:
select text, query_plan, granted_memory_kb, used_memory_kb, wait_time_ms
from sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) q
Further you can take help of XML showplan which is very important to find the cause of large memory needs like hash joins and sorts within the quries that are troubling the performance.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.