All we need is an easy explanation of the problem, so here it is.
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.
- 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)
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
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.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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)';
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂