Unused Memory Grants

sp_BlitzCache warns about unused memory grants because they can harm concurrency and performance. If many queries ask for large memory grants and don’t use them, they still hold onto that memory until the query is complete.

Symptoms

You can end up seeing some nasty poison waits in sp_Blitz or sp_BlitzFirst like RESOURCE_SEMAPHORE, or RESOURCE_SEMAPHORE_QUERY_COMPILE, which can indicate serious memory pressure and make your server feel like absolutely nothing is running. When these strike, you can’t even run things like sp_WhoIsActive, and your monitoring may show blank spots.

Another issue they can cause is stealing memory from the Buffer Pool, where SQL Server caches data pages so it doesn’t have to read them from disk. You may see high waits on stuff like PAGEIOLATCH_** when this happens.

Causes

They most frequently happen when parameter sniffing occurs and a plan is in cache that expects lots of rows back that gets reused by queries that don’t bring a lot of rows back. They can also happen when cardinality estimates go awry, because of overly complicated WHERE clauses and JOINs, or if you’re writing non-SARGable queries that use functions in the WHERE or JOIN of your query.

Fixes

Start with RAM. If you need more, this is a cheap and easy fix, especially if your server is running with less than 64/128 GB of RAM.

Next, try tuning your queries. Break complicated code out into more easily optimized chunks, and make sure your queries are SARGable.

If you’re on SQL Server 2012 SP3, 2014 SP2, or 2016, you have the MIN_GRANT_PERCENT and MAX_GRANT_PERCENT hints to manage memory grants at the query level.

For earlier versions, you can try Resource Governor if you’re on Enterprise Edition.