0

We run a SQL Server 2014 standard edition on a 94 GB RAM server with 82 GB RAM dedicated to SQL.

I know PLE is not the answer to all and we should not focus on it too much. But if you look at the image below, on one of our nodes, the PLE is very low, and stays low too. But we also have A LOT of free memory on that node. This seems to be the case for us on most days.

I feel like free memory is wasted memory, is that a correct assumption?

Why doesn't SQL Server not utilize this memory to store data pages in and drop them when it needs the memory for other applications?

Buffernode and memory counters

Results

select * from sys.dm_os_process_memory

dm_os_process_memory results

select * from sys.dm_os_memory_nodes

dm_os_memory_nodes

select @@version

Microsoft SQL Server 2014 (SP2-CU18) (KB4500180) - 12.0.5687.1 (X64) Jul 20 2019 21:27:07 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

exec sp_readerrorlog

sp_readerrorlog

select * from sys.dm_os_nodes

sys.dm_os_nodes

Update on 2022/04/26: We upgraded our SQL Server 2014 to SP3 CU 4 and for now, it seems to have resolved the issue.

Improve this question
6
  • Can you add the complete output of select * from sys.dm_os_process_memory select * from sys.dm_os_memory_nodesand select @@version in the question. Please also add first 100 lines of sp_readerrorlog in the question
    – Shanky
    Commented Mar 31, 2022 at 8:37
  • Thanks for the reply, I updated the question, I was unsure if you meant the actual first 100 lines of the error log or the last 100 lines so let me know if you need the last 100 lines.
    – KHP
    Commented Mar 31, 2022 at 8:55
  • Can you also add output of select * from Sys.dm_os_nodes. Its seems like one of the nodes is using more memory but let me check if there is actually NUMA configured physically or not
    – Shanky
    Commented Mar 31, 2022 at 9:20
  • Ok, I updated the question
    – KHP
    Commented Mar 31, 2022 at 9:39
  • How big is the database itself?
    – J.D.
    Commented Mar 31, 2022 at 11:21

1 Answer 1

Reset to default
0

Please dont treat this as answer, just trying to highlight few things. As per the Output of sys.dm_os_memory_nodes

I can see on both nodes VAS committed virtual_address_space_committed_kb is almost same 40 GB. But the reservation is significantly less on Node 1 which means SQL Server is not utilizing that node to its potential. Most of the workload is handled by Node 0. I have seen this in many NUMA nodes system but this is something either a bug or something relevant to how NUMA is configured. You have SP2 could you please move to SP3 CU 4 so that you are at least extended supported. may be, just may be this might resolve this behaviour.

Also note from output of sys.dm_os_nodes the active_worker_count is almost same. So SQL Server is reserving and committing more from Node 0. Really dont have idea why so may be a behaviour, may be a bug.

In such scenario since Node 1 is not loaded at all I would ignore PLE.

NOTE: Currently SQL Server does not have Locked pages in memory enabled, please enable it, it would require restart and then see if the behavior changes.

EDIT: Update from OP. Applying SQL Server 2014 to SP3 CU 4 seems to have resolved the issue.

Improve this answer
14
  • 1
    Unsure if this is the case. Maybe I should have included this in my question. I am 100% sure (but can't show it now) that we have days that the pages in memory is more spread out over nodes, as in, A LOT on 1 node and way less, but still a lot in the other node. Think 4000 on node 00 and 16000 on node 01. On other days, the pages are all situated on the other node, see this screenshot i.sstatic.net/xPTuj.png
    – KHP
    Commented Mar 31, 2022 at 12:05
  • @KPH. This is interesting, so this makes me believe what you are seeing is normal. SQL Server is internally deciding what node to use and send workloads to. One more Q did you played with CPU affinity mask ? for Node 1 its 240 which is nit odd
    – Shanky
    Commented Mar 31, 2022 at 12:31
  • @KPH Can you go to your SQL Server, right click and select properties. Go to processor tab select it on left side and paste the screenshot. Make sure you expand all the + button and then paste the screenshot
    – Shanky
    Commented Mar 31, 2022 at 12:39
  • I'd have to ask the external firm that helped set up the whole infrastructure. Internally, we have no idea who set this or why it was set to this. I will try to get this information as soon as possible. Here are the processor properties. imgur.com/a/u3mGCQG
    – KHP
    Commented Mar 31, 2022 at 12:42
  • 1
    We upgraded our SQL Server 2014 to SP3 CU 4 and for now, it seems to have resolved the issue. Thanks for the help!
    – KHP
    Commented Apr 26, 2022 at 9:29

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.