Memory Pressure Affecting Queries

When your query starts, it asks – begs, really – for a certain amount of memory to store its results, do sorts, join things together, and contemplate life. The amount of memory is determined by how much data will be pulled back from each table, how big the rows are, and so forth.

SQL Server may say, “I’m sorry Dave, I’m afraid I can’t do that,” in which case your query gets a much lower amount of memory to do its business. The rest of the space it needs will be taken from TempDB, the public toilet of SQL Server.

To find this, we check at sys.dm_exec_query_resource_semaphores and add up the forced_grant_counts – the number of times this has happened since a restart. (When this happens, you’ll probably also see RESOURCE_SEMAPHORE waits that have occurred.)

TO FIX THE PROBLEM

When you see a value > 0 for this, we typically recommend to monitor for times when the value is increasing, and capture what is executing at that time period. (Several times we have found the cause be execution plans that are asking for unreasonably large memory grants due to bad statistics – but it may be just a very large concurrent workload and not enough memory to service it at other times.)

You can also run:

To find queries getting large memory grants.

Scroll across to the right and look at Creation & Last Execution dates – when your server is under heavy memory pressure, it may not be able to cache plans for long.

Then, scroll to the Max Memory Grants columns – that’s how much the queries are getting granted – and also look at the memory used. When you see queries getting granted gigabytes (or tens or more), but only using a few megabytes, that’s a problem.