Curbing Wasteful Memory Grants - MSSQL 2017

I'm running an on-premise SQL Server 2017 Enterprise at the latest patch/CU level supporting vendor software, which will become important later on.

Server Stats:

• Virtual Machine (Memory is Pinned)

• Windows Server 2016 DataCenter

• 16 vCPU

• 192 GB RAM (179 GB Allocated to SQL Server)

• 30 GB Page File (I would rather this not be there, but it's not been a battle I've won)

• Hosts 5 user databases, 1 of which is approximately 4TB in size

• Vendor Required Server Default of 1 for max degree of parallelism

As stated above, this system supports a popular vendor platform and while the vendor is pretty good about custom indexes or other transparent data model adjustments, they tend to frown upon functionality changes that require adjustments to their application code (for obvious reasons as it would affect thousands of their clients).

The situation I'm experiencing is this vendor application runs long-running row mode queries that queue up a list of items which are processed item-by-item within their application code. These queries can (and do) run for days or even weeks if we let them (showing ASYNC_NETWORK_IO waits, as expected). When these queries are initiated they request huge amounts of SerialDesiredMemory in comparison to the SerialRequiredMemory they really need. The result is that Requested/Granted Memory in the MEMORYCLERK_SQLQERESERVATIONS memory clerk far exceed what is Used. For example:

-- What amount of query execution memory is asked for and used
SELECT    SUM(granted_memory_kb) / 1024 AS granted_memory_mb
        , SUM(requested_memory_kb) / 1024 AS requested_memory_mb
        , SUM(used_memory_kb) / 1024 AS used_memory_mb
        , (SUM(granted_memory_kb) - SUM(used_memory_kb)) / 1024 AS excess_memory_grant_mb
FROM    sys.dm_exec_query_memory_grants
OPTION (RECOMPILE)

Returns:

This is absolutely terrifying AND this is after enabling RESOURCE GOVERNOR and reducing the REQUEST_MAX_MEMORY_GRANT_PERCENT on the reso

As documented, the query hint MAX_GRANT_PERCENT allows a floating point value down to 0.0 even on SQL Server 2017, so that is still an option.

db<>fiddle

So you could create a plan guide for that
EXEC sp_create_plan_guide
@name = N'Guide1',
@stmt = N'SELECT Something
FROM Somewhere',
@type = N'SQL',
@hints = N'OPTION (MAX_GRANT_PERCENT = 0.5)';


StackExchange Database Administrators Q#313658, answer score: 2