3

According to sys.dm_os_memory_clerks, the total RAM usage of my server was 30 GB. Using this information, I set max server memory (MB) to 31,000. I was shocked to then see sys.dm_os_memory_clerks report that the total RAM usage of my server was 20 GB. This is doubly confusing, given that the documentation says that max server memory (MB) is only for the buffer pool

The max server memory option only limits the size of the SQL Server buffer pool. The max server memory option doesn't limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs and EXEs.

Is sys.dm_os_memory_clerks a bad source of information for these decisions, or do I just need to wait on my buffer pool catching up?

1 Answer 1

6

bad news

Yeah, dm_os_memory_clerks is a pretty bad single-source to figure out what max server memory should be set to. It just shows you what's currently consuming memory, which can shift and bob and weave depending on what's going on with the server.

In most cases, max server memory should be set to a percentage of available physical memory, with allowances for the OS, and anything else running on the box (including stacked SQL instances or components, which is still a terrible idea). I'm not going to get into what the correct formula for that is, because it can often interfere with having a great weekend.

You haven't mentioned how much physical memory the server has, or which edition of SQL Server you're running (Standard v. Enterprise) so it's hard to tell you what you should set it to on the high end.

Since it seems like you have memory to spare, you will probably want to look at wait stats, particularly for PAGEIOLATCH_xx waits. You may find my stored procedure sp_PressureDetector useful for combining several important data points into one screen.

Aside from the wait stats section at the very top, there are three sections that break down memory usage:

NUTS

Some generalized indicators that you are able to reduce max server memory would be:

  • PAGEIOLATCH_xx waits being < 5% of server uptime
  • Total and Target server memory remaining even
  • No low memory indicators
  • Size of data is smaller than current max server memory

This is not an exhaustive list, and there are certainly other factors to consider, but this should be enough to answer your stated question about dm_os_memory_clerks being a bad source of information.

1
  • Oh yeah. I checked the wait stats ahead of time. Really no signs at all from those that the server is lacking memory. The total wait time for memory is about 30 seconds over the past day. Do you have a hyperlink with more details about "low memory indicators"? I'm unfamiliar.
    – J. Mini
    Commented Jun 14 at 21:44

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.