SQL SERVER – Troubleshooting Non-Yielding Scheduler Issues

SQL SERVER - Troubleshooting Non-Yielding Scheduler Issues yield-800x584 As a database performance consultant, I recently had the opportunity to work with a client on a Comprehensive Database Performance Health Check. During the engagement, I encountered a critical issue related to non-yielding schedulers in their SQL Server environment. In this blog post, I will share my findings and the solutions I provided to resolve the issue.

Real World Scenario

While conducting the performance health check, I came across a specific incident where the SQL Server instance experienced non-yielding scheduler problems. The error log indicated that multiple workers were non-yielding on different schedulers, and there were signs of memory pressure as the resource monitor was also affected. I analyzed a dump file generated during the incident to gain deeper insights.

Analyzing the Dump File

Upon examining the dump file, I traced the issue to memory allocation. The stack trace pointed to functions such as SOS_MemoryBlockAllocator and SOS_MemoryWorkSpace. This finding aligned with a known issue where the “Lock Pages in Memory” option was not enabled for the SQL Server service account.


Based on my analysis, I provided the client with the following solutions to address the non-yielding scheduler issue:

  1. Enable Lock Pages in Memory: To mitigate memory-related problems, I recommended enabling the “Lock Pages in Memory” option for the SQL Server service account. Enabling this option allows SQL Server to retain data in physical memory, reducing the likelihood of encountering non-yielding scheduler conditions. Here is how you can enable lock pages in memory.
  2. Apply the Latest Cumulative Update (CU): Keeping SQL Server up to date with the latest Cumulative Updates (CUs) is crucial for resolving known issues and enhancing system stability. I advised the client to apply the most recent CU available for SQL Server 2019. This proactive measure would help prevent potential non-yielding scheduler problems and other related issues.


During a Comprehensive Database Performance Health Check, I identified and addressed a critical issue with non-yielding schedulers in the client’s SQL Server environment. By enabling the “Lock Pages in Memory” option and applying the latest Cumulative Update (CU) for SQL Server 2019, we were able to mitigate the problem and enhance system stability. As a database performance consultant, it is crucial to proactively monitor and resolve such issues to ensure optimal performance and minimize disruptions in the SQL Server environment.

You can always reach out to me via YouTube Channel.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

CPU Scheduler, Lock Pages in Memory, SQL CPU, SQL Memory
Previous Post
SQL SERVER – Impact of Changing Database Compatibility Level on Cache
Next Post
SQL SERVER – Monitoring Database Autogrowth Settings

Related Posts

2 Comments. Leave new

  • Hello Pinal, firstly thank you for your this efficently post for me. You says below analizying a Dump file and deep trace until find it. Can you inform us that how you can? Do you write a post or already have? Thank you.

  • Is there any way to predict when this will happen? Got a high memory, high txn system that seems to get these every 30-40 days. Typically when a TLOG backup occurs. Dump points to IO only and seems completely random. Enable Lock Pages in Memory is on and its on the latest 2019 CU. Love your stuff man!


Leave a Reply