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