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.
MAX_GRANT_PERCENT
allows a floating point value down to0.0
even on 2017 dbfiddle.uk/…