11

We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory.

I do not have much experience with Lock Pages in Memory, and I've read a lot of blog posts that express caution around turning it on.

Server details

  • Windows Server 2016
  • SQL Server 2017 CU27
  • 504 GB total RAM
  • 64 cores
  • 43GB page file
  • Max Server Memory is set to 460 GB
  • MaxDOP 16

I'm looking for advice from people with experience around Lock Pages in Memory, specifically on VMs with large memory. Or if there is another option I have not thought of?

0

1 Answer 1

21

We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM with 504 GB of RAM. Reading this link on Microsoft Docs, it's recommended we enable Lock Pages in Memory.

You're correct, also I was part of writing that document.

I do not have much experience with Lock Pages in Memory, and I know a lot of people advise against it.

I don't know any reason why people would be against it. Do you have some examples?

Options on the table.

  1. Enable Lock Pages in Memory

This will (essentially by way of indirectness) stop the NYS from occurring due to the mechanisms involved.

2.Decrease page file to 2GB

This will do nothing to help.

3.Increase Max Memory to 475 GB

This will only make the issue more prevalent.

I'm looking for advice from people with experience around Lock Pages in Memory, specifically on VMs with large memory.

You should do it.

Which of the above options would you consider in this scenario, if any?

Only the first one will do anything. There is another possibility not listed, upgrade to Windows Server 2022 which should have the Windows side enhancements to not run into the issue.

2
  • Thanks! Sean, is there any documentation you can link to that talks about the Windows-side enhancements in Server 2022 that help protect against this?
    – mwigdahl
    Oct 27, 2023 at 13:49
  • No, there is no line item for it. Each process has a memory workspace lock, IIRC (which is questionable) the lock type was changed and enhanced to be faster and more concurrent. If I get some time I'll take a look at WS2016/9 vs 2022 but not sure when that'd be. Oct 28, 2023 at 0:37

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.