Query Memory Diagnostics

There are a number of different ways to get information on query memory usage on your SQL Server in addition to the DMVs already discussed at the beginning of the section. Performance Monitor provides the following counters, all of which are found within the instance’s Memory Manager:  

➤ Granted Workspace Memory (KB) — Total amount of query memory currently in use 

 ➤ Maximum Workspace Memory (KB) — Total amount of memory that SQL Server has marked for query memory  

➤ Memory Grants Pending — Number of memory grants waiting in the queue  

➤ Memory Grants Outstanding — Number of memory grants currently in use 

 

The RESOURCE_SEMAPHORE wait type is a wait on a memory grant, so if you see this near the top in your results from the sys.dm_os_wait_stats DMV, then your system is struggling to provide memory grants fast enough. You can also encounter performance issues other than just a query timing out while it waits for a memory grant.  

Within an execution plan or when analyzing a SQL trace, you may notice hash warning or sort warning messages if you have selected the relevant events. These occur when the memory grant was insufficient for a query’s requirements. A hash warning occurs when the hash build doesn’t fit in memory and must be spilled to disk (its actually written to tempdb). A sort warning occurs when a multi-pass sort is required because the granted memory was insufficient. Both warnings generally occur because the SQL Server Query Optimizer made the wrong choice, usually because of inaccurate statistics or a lack of useful statistics. 

 

SELECT max_workers_count FROM sys.dm_os_sys_info 

SELECT count(*) FROM sys.dm_os_workers. 

sys.dm_os_schedulers 

The Query Wait Option Queries can time out if they spend too much time waiting for a memory grant. The time-out duration is controlled by the Query Wait option, which can be modified either using sp_configure or on the Advanced page of Server Properties in Management Studio. The default value is -1, which equates to 25 times the cost of the query, in seconds. Any positive value for Query Wait will be used as the time-out value in seconds. It is possible for a transaction that contains a query waiting for a memory grant to hold locks open and cause a blocking problem before it times out. In this situation, a lower Query Wait value would reduce the impact on other tasks by causing the query to time out sooner.