Non-yielding scheduler error in large memory server if the lock pages in memory option is disabled



  • We are seeing a lot of Non-yielding scheduler errors on a SQL Server VM. Reading this https://docs.microsoft.com/en-us/troubleshoot/sql/admin/non-yielding-error-lock-pages-disable 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?



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2