You might encounter a situation, under a very heavy workload, where processing for certain transactions starts out very efficiently, but over time becomes much less efficient. 

 

A good place to start troubleshooting is, of course, PerfMon.  But PerfMon can fail to reveal the culprit, since the CPU can remain stable and moderate while still experiencing this problem.  It’s time to pull out a more granular troubleshooting tool, the SQL Server 2005 DMV wait states.  Of course, you won’t encounter this exact problem every time, but sometimes you might find that you have ever increasing SOS_SCHEDULER_YIELD waits occurring.  BOL provides this illuminating description of the wait:

 

"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."

 

Now, I can count the number of times after a hard day’s work that I want to get my quantum renewed.  You?  Anyway, the description doesn’t explain much. 

 

That’s why you need to learn more about %signal waits in this blog post:

 

http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx

 

Thanks to the SQL CAT team for figuring out this problem and thanks to Bill Graziano and Linchi Shea for pointing out the issue.