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:

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

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.

Menu
{"cart_token":"","hash":"","cart_data":""}