Answers

The SQL Database engine uses a non-preemptive scheduler internally to increase throuhput; Most threads are expected to yield often. Much more information on this topic can be found on http://blogs.msdn.com/slavao.

If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

You can run the DMV query to confirm that:

-- Check SQL Server Schedulers to see if they are waiting on CPUSELECT scheduler_id, current_tasks_count, runnable_tasks_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255

If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern!

All replies

The SQL Database engine uses a non-preemptive scheduler internally to increase throuhput; Most threads are expected to yield often. Much more information on this topic can be found on http://blogs.msdn.com/slavao.

If you are seeing lots of SOS_SCHEDULER_YIELD in your Wait States, that is a very stong indicator of CPU pressure.

You can run the DMV query to confirm that:

-- Check SQL Server Schedulers to see if they are waiting on CPUSELECT scheduler_id, current_tasks_count, runnable_tasks_countFROM sys.dm_os_schedulersWHERE scheduler_id < 255

If you see the runnable tasks count above zero, that is cause for concern, and if you see it in double digits for any length of time, that is cause for extreme concern!