MS SQL Server - High "Modified Memory" consumption

Question

We operate an instance of the MS SQL Server 2022 (standard 16.0.4095.4) on Windows Server 2022 (virtual machine with 32GB RAM) and having issue with the "Modified Memory" consumed by the sqlservr.exe process.

The SQL Server memory configuration is as follows:

The overall memory consumption looks like this:

When I look at what consumes this modified memory, I can see it is the SQL Server:

This "Modified" portion of the memory is constantly growing and does not seem to go ever down (it has been keeping high and higher for days). The SQL Server perf stats do not indicate any performance issue at the moment.

Also the output from the DBCC MEMORYSTATUS does not gives me any clue about what this "Modified memory" portion could be. As I understand the output (see below) the memory consumption reported by the DBCC MEMORYSTATUS fits into the limit set in "Maximum server memory".

Do you have any idea, what could cause the kind of memory consumption far above the value set in "Maximum server memory" or how I can investigate further?

The issue does not occur on any other instances we have - the "Modified memory" is a minor portion of the memory consumption there. The only difference between this and other instances is that there is several basic availability groups setup on this box. There are not any other "suspicious features" allowed there (no columnstore indexes, no in-memory OLPT ...).

What I tried just for a curiosity is to decrease the "Maximum server memory" value. The only effect was that the "In use memory" freed by this has been then slowly but surely switched into the "Modified memory" in some time. I am considering increasing the VM memory, but I am afraid a bit that it may result just into a bigger "Modified memory" consumption...

Answer 1

[...] having issue with the "Modified Memory" consumed by the sqlservr.exe process

What I would like to know/understand is what exactly consumes this "modified memory"

Modified memory is essentially, without getting too deep, memory that has been modified and must be written to disk before it can be reused. SQL Server has a buffer pool (and various other internal and external memory items) whose job is to literally read and write data. SQL Server caches data in memory for this purpose. SQL Server will modify a ton of memory. You can feel free to use etw tracing to find changes to memory, dump those pages out and see what's modified but it's pointless. Let me give you the answer, someone ran a query, it needed data or made changes to data, the memory was modified. The max server memory is set too high for the configured server (with everything else it is running) and now you're seeing the modified memory counters.

Now that you have your answer how is this going to help? Much as I stated, it won't.

You have an XY problem. You're focused on the wrong thing and won't let go of it. It's time to let go, see the forest, stop going against what the people you've asked for help are telling you.

There are many resources on here explaining max server memory in addition to the link I gave:

• SQL Server Memory allocation and max server memory setting
• Why isn't SQL Server using the Max Server Memory setting?
• Does RAM taken up Shared Memory connection counts against Max Server Memory?
• Max Server Memory configured but it increases more of its configuration
• https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16

From your own screenshots, the max server memory is being as it was configured.

The assumption as I stated, is that SQL will only use 20 GB which is incorrect. Images, thread stacks, heap allocation, and a whole host of other things do not fall under max server memory, much as it is stated in the link I had previously given you. You take backups? Cool, that outside it too. I see SQL Agent is running, did you account for that? Also SSMS is running, what about that? I see lots of 3rd party items, and all that memory was accounted for as well? The thread stacks, images, 3rd party dlls and their heap allocation, etc., were all accounted for in this magical 32 GB of memory that was allotted?

Set the max server memory lower, remove any 1st or 3rd party dlls (linked servers, etc.), and don't run a ton of other things on the server.

Let the modified memory thing go, please, it's useless in this context as I previously stated.

Answer 2

Regardless being discouraged from this and as I do not consider the ever increasing amount of "Modified memory" locked by the sqlsvr.exe as standard and healthy behavior, I continued investigation and finally found the root cause for the issue in our environment.

Just recap the environment and the issue at the beginning:

• 2 Windows 2022 Servers in WFCS with MS SQL 2022 Standard (16.0.4095.4), 32GB RAM
• 20 databases in total, each of them in a Basic Availability Group.
• The total size of all databases is 46 GB.
• Regardless of the current workload the amount of the "Modified Memory" locked by the sqlsvr.exe was growing continuously and never went down. Even after hours when the server is just idling.
• The same behavior encountered after several VM restarts.
• None of all other SQL Servers we operate (more than 100) behaves like this. On all of them the amount of "Modified Memory" consumed is constantly negligible (units of MBs).
• The "Modified Memory" portion was shown in the Windows Resource Monitor (or utilities like RAM map) as locked by the sqlsrv.exe process but as per output from DBCC MEMORYSTATUS not consumed by any memory clerk.
• The "Modified memory" was increasing at the expense of remaining free VM memory and also the memory clerks allocations (until it hit the Min Server Memory configuration value). Sooner or later we always hit the 99% of the server memory utilization and the VM started becoming irresponsive.

When we looked to the "Modified memory" not from the perspective of the process to that it belongs but to the file, we discovered that the increasing allocation seemed to be held in an WFCS event log file as shown below:

There was a huge number of "Verbose level" events generated in a high cadency although the ClusterLogLevel was set to 3.

There were events like this:

After several attempts we were not able to force the verbose level events not to be logged.

In the end we decided to disable the entire FailoverClustering log in the Windows Event Log. Since that time the memory consumption behaves in a "normal way" with negligible amount of consumed "Modified Memory" as we are used to in other servers.

The issue seems to be somewhere in the WFCS and number of AGs on the serves as the amount of the AGs is in principle the only aspect this environment (2 servers) differs from all other environments we operate.