1

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:

SQL Server Memory Configuration

The overall memory consumption looks like this:

Overall Memory consumption

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

RAM map output

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".

DBCC MEMORYSTATUS output

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...

3
  • The actual problem is that the "Max memory" settings says 20480MB, but the SQL Server consumes actually over 27000MB. I am able to track what consumes the 20480MB portion, but not able to track what consumes the additional memory up to 27000GB. At some point it turns into to the OS memory pressure. I know that the "Max memory" settings does not cover all the memory that can be consumed by SQL Server, but has never encountered so big difference and so I would like to understand what exactly consumes it, so that I can decide what to do next. Commented Mar 14 at 11:28
  • What I would like to know/understand is what exactly consumes this "modified memory". Why? Because I have never encountered such a situation on any of several dozens even much bigger instances. On all of them the "modified memory" portion is negligible and so I would like to understand why just this one is different. I have not found an answer to this here so far. Maybe just try to check some of yours instances to see what is the "usual" modified memory consumption there. I know the memory can go above MSM but each time I encountered it has been shown as "in use memory", not modified. Commented Mar 14 at 15:07
  • About the memory pressure, there is actually the pressure on the server. It is the VM with 32GB of RAM. The MSM is set to 20GB (much less that any "best practices" recommendations for this configuration). There is nothing else running on the box. However, the overall used system memory goes to 98% due to the "modified memory" consumption. This is the reason for my interest. Just one additional point. If I decrease the MSM even lower, in a few days I am back on 98% memory utilization just with higher "modified memory" value.... Commented Mar 14 at 15:12

2 Answers 2

3

[...] 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:

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

enter image description here enter image description here

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?

enter image description here

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.

1

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:

enter image description here

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

enter image description here

There were events like this:

enter image description here enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.