Curbing Wasteful Memory Grants - MSSQL 2017

Question

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 resource pool these queries fall into to 5%, though I will likely be reducing this all the way down to the SQL 2017 minimum of 1% based on what I'm seeing.

My question is while I'm stuck at SQL 2017, is there anything else I can do to limit the amount of wasted RAM in my query execution plans in addition to further reducing the resource pool's REQUEST_MAX_MEMORY_GRANT_PERCENT value down to 1%?

Since this is a vendor app, query plan guides may be an option, but adjusting MAX_GRANT_PERCENT doesn't give me anything more than I'm getting from adjusting the REQUEST_MAX_MEMORY_GRANT_PERCENT value in the resource pool. Is there anything I can do within a plan guide to force batch mode execution in hopes this triggers the Batch Mode Memory Feedback feature which is available in SQL 2017? Again, since this is vendor application code I'm working with, I don't have the ability to change the queries so the standard tom foolery approaches will likely not work here.

Upgrading to SQL 2019 (or preferably SQL 2022) is the obvious answer here as it gives me access to a number of features I can employ in this situation such as Row Mode Memory Feedback and floating point values for REQUEST_MAX_MEMORY_GRANT_PERCENT, but are there any other options available with SQL 2017 I've not already touched on? If not, that's fine, I'm just trying to exhaust any outstanding options I've not thought of yet.

Answer 1

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)';