Too Much Free Memory

Sounds ridiculous, right? How can you have too much free memory?

Specifically we’re talking about internal memory – memory that SQL Server has claimed, but it’s not actually using for anything. Normally, SQL Server claims a bunch of memory and uses it to cache data, cache execution plans, and slice & dice your query results (called “query workspace memory”.)

But what if you have:

A query plan that goes horribly awry
SQL Server *thinks* it’s going to need a huge amount of memory to run it
In actuality, it hardly uses any memory at all, and runs fairly quickly

Users won’t complain because their query is quick – but there’s an awful side effect. SQL Server is constantly clearing out all this memory to run their query – and then not actually using the memory. This is memory that could have been used to cache data and reduce pressure on your storage subsystem.

We look at the Perfmon counter for Total Server Memory, and compare it to Free Memory. If Free Memory is >20GB, and >= 30% of Total Memory, we might have a problem.

TO FIX THE PROBLEM

Start by reading these posts:

Query Plans: Memory Grants and Row Estimates
SQL 2012 SP3 Adds Memory Grant Features – showing more details of grants in the execution plan, and try sorting by max_grant_kb descending
Look for the sp_Blitz® warning of “Memory Pressure Affecting Queries” – this means you’re really in trouble
Watch this video: RESOURCE_SEMAPHORE – your server may not be experiencing that poison wait yet, but it’s probably just a matter of time since queries are getting large memory grants

Then, check this from time to time, like every hour or two:

sp_BlitzCache @SortOrder = 'memory grant'

1	sp_BlitzCache @SortOrder = 'memory grant'

Look at the queries getting large memory grants, and tune them (via query or index changes) to use less memory. You may have to look several times through the week before you find the query involved – if your server’s under memory pressure, the plans involved may be disappearing from the cache quickly.