Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – SOS_SCHEDULER_YIELD and how to reduce it

09 April,2011 by Jack Vamvas

SQL Server Books online defines SOS_SCHEDULER_YIELD as “Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed “

What is happening when a SOS_SCHEDULER_YIELD is measured? The SQLOS worker has voluntarily yielded the CPU to another worker. This happens regularly and is part of the SQL Server CPU management system . At the point the thread yields it is SUSPENDED.

The point to become concerned is if the SOS_SCHEDULER_YIELD is a high percentage of overall wait time. This points to multiple concurrent requests on the CPU

A few  issues to consider and approached to minimizing SOS_SCHEDULER_YIELD

1)A high percentage SOS_SCHEDULER_YIELD does not always mean CPU pressure. Use perfmon counters to cross-check.

2)Use  the DMV – sys.dm_exec_query_stats by worker time

3)Check for high levels of recompilations.Look to Query Tuning

4)Consider redistributing database files onto other servers, to decrease CPU pressure

5)Assess response from sys.dm_os_schedulers  . From BOL “Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks”

SELECT scheduler_id, current_tasks_count, runnable_tasks_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255

 Related Posts

SQL Server Performance Checklist

Top 5 SQL Server DMV for Index Analysis


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer