0

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

Martin Karouš
  • 186
  • 1
  • 7
  • I'm not understanding what the actual problem is here? – Sean Gallardy Mar 14 '24 at 10:12
  • 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. – Martin Karouš Mar 14 '24 at 11:28
  • 1
    If you look at your DBCC MEMORYSTATUS the committed is actually your MSM. Not all memory is held under the MSM - modules, thread stacks, heap allocations, etc. so yes, by default it was always be able to go above MSM. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver16 – Sean Gallardy Mar 14 '24 at 14:00
  • 1
    Modified memory is a pointless metric here, it means nothing. Additionally, there are tons of questions here which are answered about this very topic. If somehow yours is different, please explain, otherwise my guess is this will be closed as a dupe. – Sean Gallardy Mar 14 '24 at 14:06
  • In short, what you're seeing is expected by design. There shouldn't really be any memory pressure, since SQL Server should be the only thing running on your server. – J.D. Mar 14 '24 at 14:56
  • 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. – Martin Karouš Mar 14 '24 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. – Martin Karouš Mar 14 '24 at 15:12
  • 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.... – Martin Karouš Mar 14 '24 at 15:22
  • @MartinKarouš Heads up, you need to tag someone using the @TheirUsername (like I just did to you) if you want them to be notified of your comment. I just coincidentally came back to this question. "About the memory pressure, there is actually the pressure on the server...However, the overall used system memory goes to 98% due to the "modified memory" consumption." - Why does it matter though?...where is the actual "pressure" and what is it affecting if SQL Server is the only thing running on your machine? – J.D. Mar 14 '24 at 21:48
  • @J.D. It matters to me, because I do not consider running the box on 98-99% of the memory utilization as a "healthy state" as then the OS itself is starving and it brings other issues (e.g. it is even hard to connect there, monitoring processes are falling on out-of-memory exceptions etc.). – Martin Karouš Mar 15 '24 at 07:57
  • 1
    @MartinKarouš that may be true of some server types, but not of SQL Server. It’s something admins not used to working with databases all eventually learn. – Erik Darling Mar 15 '24 at 12:05
  • @MartinKarouš "OS itself is starving" - The OS has nothing else it should be doing other than supporting your SQL Server instance, which it's doing by allowing it's available memory to be utilized by it. "it is even hard to connect there" - You should almost never need to directly connect to the server. "monitoring processes are falling on out-of-memory exceptions" - Those processes should not be running directly on that server and instead should be running from a separate monitoring server that connects to that server to monitor (typically via a database connection to the instance). – J.D. Mar 15 '24 at 12:47
  • @ErikDarling I understand what you are saying and don't want to argue with you. I agree that you usually shouldn't connect there using RDP (etc.) and that the high memory utilization is ok for MSSQL, but it has a limit. In my opinion, if it is not possible to connect using RDP or even PS remoting (because of lack of memory) it doesn't indicate a "healthy server". And so I would like to know what eats this "Modified" memory as on all our other SQL Servers it is not like this. Do you have any idea how I can find it out? I am suspicious it has something to do with the several basic AG there. – Martin Karouš Mar 15 '24 at 14:45
  • @MartinKarouš there’s an answer below, but it’s also worth noting that the default for min server memory is 0, and setting it to a higher value is not typically a good practice. It was bad advice that propagated in the early days of virtualization. – Erik Darling Mar 15 '24 at 15:18

1 Answers1

2

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

Erik Darling
  • 40,781
  • 14
  • 130
  • 456
Sean Gallardy
  • 32,288
  • 3
  • 40
  • 77