Have you ever seen an error like:
Process 51:0 (dbc) UMS Context 0x018DA930 appears to be non-yielding on Scheduler 0.
Or in SQL Server 2005:
Process 51:0:0 (0xdbc) Worker 0x036BA0E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12764721496978. Approx Thread CPU Used: kernel 15 ms, user 171 ms. Process Utilization 0%. System Idle 99%. Interval: 325602683 ms.
If you really want to get into the guts of SQL Server (specifically the scheduling parts), there's a new whitepaper written by Robert Dorr in Product Support and Sameer Tejani in Product Development called ' How to Diagnose and Correct Errors 17883, 17884, 17887, and 17888 '. You can download the paper at http://www.microsoft.com/technet/prodtechnol/sql/2005/diagandcorrecterrs.mspx
It provides great insight into the logic we have to detect 'hang' situations and what you can do to resolve them (and it might make your brain hurt).
Enjoy
