0

I'm experiencing a strange issue on one of our SQL Server (SQL Server 2019 Enterprise CU18).
The server has 12 cores and 96GB physical memory and is also part of a failover cluster. Max memory has been set to 92GB (min memory is 0). The SQL Server service process has allocated all the 92GB.
In our monitoring we see that SQL Server always has approx 40GB of free memory (Memory Manager\Free Memory).

In the error log I see that all 12 cores are used:

SQL Server detected 1 sockets with 12 cores per socket and 12 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing.

All databases have their recovery interval set to 60 seconds.

This are the results of running EXEC sp_PressureDetector @what_to_check = 'memory';

enter image description here

enter image description here

As soons as Memory Manager\Free Memory drops below 40GB the lazy writer kicks in to evict pages from the bufferpool so that the free memory goes back to 40GB. After rebooting the server or failover, the same happens again.

Because there are 12 cores in one socket, SOFT-NUMA splits the 12 cores into 2 nodes of each 6 cores. I have a feeling that SQL Server is only using half of the memory due to the SOFT-NUMA but I can't prove this.

As a test I also lowered max memory with 20GB, expecting that Memory Manager\Free Memory would drop with the same amount but it only dropped with 7GB.

Some further digging around in the ring buffers showed me that there is 'pool level pressure' enter image description here
The timestamps of these entries match the start of the lazywriter.
Does this mean that there is pressure on the bufferpool or how do I need to interpret this?

Can somebody explain this strange behavior or point me in the right direction to troubleshoot this issue further?

Improve this question
2
  • You say the server has 92 GB of memory and SQL is set for 92GB... which leaves nothing for anything else, which means external memory pressure will cause lazy writes.
    – Sean Gallardy
    Commented May 10 at 13:51
  • @SeanGallardy: the server has 96GB physical memory (I've edited my question) but even when I drop max memory with 20GB the issue occurs.
    – Frederik Vanderhaegen
    Commented May 13 at 6:33

1 Answer 1

Reset to default
2

details

Since you're on Enterprise Edition, SQL Server isn't limited to a certain amount of memory, or a certain number of processors like it is in Standard Edition, unless you installed it with goofy licensing.

One thing you can do is look in the error log for startup messages that say something like using 20 logical processors based on SQL Server licensing. If you find that message, you'll likely need to do a SKU upgrade with the correct licensing enabled to use all cores and any memory attached to them. That is unlikely given the server configuration, but worth looking at.

Other settings you may want to check:

One final thing to check, assuming none of those things are causing you drama, is what might be using gobs of memory that isn't the buffer pool. An easy way to do that is to use my stored procedure, sp_PressureDetector, like so:

EXEC sp_PressureDetector
    @what_to_check = 'memory';

The section you'll want to focus on looks like this:

NUTS

Improve this answer

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.