AzureFeeds

This article demonstrates how you can create a non-yielding scheduler scenario at will so you can examine the various diagnostic tools for non-yielding schedulers – error log, memory dump, nonyield_copiedstack_ring_buffer_recorded in system_health Xevents.

Do not do this on a production server as you will freeze your production workload. This is designed for learning and better understanding of how SQL Server works and can be troubleshot.

1. If you already have WinDbg or WinDbgX installed and symbols configured, skip to step 6 [Attach WinDbg to a SQL Server process]


2. Download and install WinDbg via Windows SDK – Windows app development | Microsoft Developer . Choose the Download the Installer button.


3. When you start the installation uncheck everything except Debugging Tools for Windows and press Install
   
   

    

   
   


4.  Start Windbg


5. 
   

   Define a symbol path so you can debug with call stacks that are human-readable

   
   
   
   
   1. File -> Symbol File Path
   
   
   2. Paste srv*c:PublicSymbols*https://msdl.microsoft.com/download/symbols 
   
   
   3. Click OK
   
   
   4. File -> Save Workspace
      
      

       

      
      
   
   
   
   


6. 
   

   Attach WinDbg to a SQL Server process

   
   
   
   
   1. File -> Attach to process
   
   
   2. Chose Sort -> By executable
   
   
   3. Locate your SQLServr.exe by expanding the properties of each process to identify the one you want
   
   
   4. Select the process and click OK
      
      

       

      
      
      
   
   
   
   


7.  Run a debugger command that causes the public symbols for many of the loaded modules to be downloaded in your local symbols folder. For example, the kc command will display the call stacks of all threads in SQL Server. It may take awhile to complete because Windbg is downloading symbol files locally in your defined symbol path. Wait for the debugger to return to the prompt
   
   ~*kc100
   

    

   
   


8. Now let the SQL Server process continue running by pressing typing g followed by Enter


9. Connect to SQL Server using SSMS or Sqlcmd and execute a very long-running query. Here is an example that works:
   
   SELECT COUNT_BIG (*)
   FROM sys.messages a, sys.messages b, sys.messages c
   

    

   
   


10. After the query has run for more than 2-3 seconds, go back the WinDbg and break into the debug session. Press either CTRL+Break or Alt+Del. This will place you back to a WinDbg prompt awaiting commands
    


11. Let’s re-run the command to display the call stacks for all threads. This time the command should complete faster than it did earlier, because most symbol files should already be downloaded.
    ~*kc100
    
    


12. Scroll to the top of the output and using CTRL+F, search for CSQLSource::Execute. You should find (at least) one thread that has this in the call stack.


13. Once you locate that thread, look at its WinDbg thread ID. For example, it could be 93 or 112. Here is an example where it’s 69:
    
    


14. Now, let’s freeze this thread on purpose inside WinDbg, in order to simulate a stalled, non-yielding scenario. This thread has actively executing code and thus likely using a SQL Scheduler. If the thread is frozen artificially, SQL Server Scheduler Monitor will consider it a non-yielding thread because it is not changing state and is not releasing control of the scheduler it was running on.
    
    

     

    
    
    
    
    1. To freeze your thread, use the ~Tf by where T is your identified thread id (“f” is for freeze)
       
       ~Tf
    
    
    2. Then hit Enter
    
    
    3. Now press g and Enter
       
       
       
       
       
    
    
    
    


15. Wait for a minute or two (a quick coffee break)


16. Now open the C:Program FilesMicrosoft SQL ServerMSSQLXXXXXXMSSQLLogErrorlog and look for an entry like this one:
    
    2025-06-06 15:03:55.77 Server *
    *******************************************************************************
    2025-06-06 15:03:55.77 Server *
    2025-06-06 15:03:55.77 Server * BEGIN STACK DUMP:
    2025-06-06 15:03:55.77 Server * 06/06/25 15:03:55 spid 5144
    2025-06-06 15:03:55.77 Server *
    2025-06-06 15:03:55.77 Server * Non-yielding Scheduler
    2025-06-06 15:03:55.77 Server *
    2025-06-06 15:03:55.77 Server *
    *******************************************************************************
    2025-06-06 15:03:55.77 Server CImageHelper::DoMiniDump entered. Dump attempts: 1. Exception record exists…
    2025-06-06 15:03:55.77 Server Stack Signature for the dump is 0x00000000000003D6qd
    

     

    
    


17. You have successfully simulated a Non-yielding scheduler


18. To unfreeze your thread, use the ~Tu by replacing T with your identified thread id (“u” is for unfreeze), then hit Enter. In this example:
    
    ~69u
    

     

    
    


19. To detach Windbg from SQL Server type the quit+detach (qd) command. After that you can close Windbg 
    
    qd


20. If you examine the lastes system_health XEL in your Log folder you can also find the nonyield_copiedstack_ring_buffer_recorded matching the non-yielding scheduler event and also extract the call stack from there. You can use this call stack with SQLCallStackResolver/README.md at main · microsoft/SQLCallStackResolver tool to identify the call stack. (this screenshot is from another debug session so ignore timestamps)
    
    
    

     

    
    


21. 
    

    After SQL Server 2022 another diagnostic capability: RING_BUFFER_NONYIELD_COPIEDSTACK in the sys.dm_os_ring_buffers. When the non-yielding scheduler issue occurs, you can query using this query:
    
    

    
    SELECT * FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = ‘RING_BUFFER_NONYIELD_COPIEDSTACK’
    

    
    The output may look like this, particularly if you let the non-yielding issue persist over 5 minutes or so.

    
    
    

    
    
    
    
    Happy debugging!