Edit

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

Applies to:   SQL Server 2016 and later versions on Windows

Symptoms

On a server hosting a SQL Server instance with a large amount of memory, SQL Server frequently allocates and frees memory. In this scenario, if you don't enable the Lock Pages in Memory option for the SQL Server service account, SQL Server might encounter a non-yielding scheduler condition and generate a memory dump file. The SQL Server error log displays this entry:

Process 0:0:0 (<ProcessID>) Worker <WorkerID> appears to be non-yielding on Scheduler 1. 
Thread creation time: 13246605454359. 
Approx Thread CPU Used: kernel 70203 ms, user 0 ms. Process Utilization 69%. System Idle 76%. Interval: 70213 ms.

Here is the top of stack:

sqldk!SOS_MemoryBlockAllocator::CommitBlockAndCheckNumaLocality
sqldk!SOS_MemoryBlockAllocator::AllocateBlock
sqldk!SOS_MemoryWorkSpace::AllocatePage
sqldk!MemoryNode::AllocateReservedPages
sqldk!MemoryClerkInternal::AllocateReservedPages

Cause

This problem occurs if large size or a large number of memory allocations or deallocations cause a long wait for those allocations and deallocations in the OS to complete.

Note

In the entry, the kernel time is almost the same as the interval time, which indicates a high amount of privileged CPU time.

Resolution

To fix this issue, enable the Lock Pages in Memory option for the SQL Server service account to keep data in physical memory. After this operation, the system uses the Address Windowing Extensions (AWE) API instead of the Virtual Memory Manager (VMM).

To enable the lock pages in memory option, follow these steps:

  1. Press Win+R, in the Open box, type gpedit.msc.

  2. On the Local Group Policy Editor console, expand Computer Configuration > Windows Settings.

  3. Expand Security Settings > Local Policies.

  4. Select the User Rights Assignment folder.

    The policies appear in the details pane.

  5. In the pane, double-click Lock pages in memory.

  6. In the Local Security Setting - Lock pages in memory Properties dialog box, select Add User or Group.

  7. In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.

  8. Restart the SQL Server Service for this setting to take effect.

The following screenshot shows the Local Group Policy Editor console.

Screenshot of Local Group Policy Editor console where the lock pages in memory option in the User Rights Assignment folder is selected.