A few days back one of the regular readers of my blog $sammy (not sure how he wants to be called, I will call Dollar Sammy ;-)) saw continuous error messages on one of his SQL Server 2000 instances which read
Process ID 61:924 owns resources that are blocking processes on Scheduler 2
This messages is one of the newly introduced features in SQL Server 2000 SP3. This indicates problem with the Scheduler. This article on MS site explains it better.
To find out the root cause, I checked the following.
- Whether SPID 61 was causing any blockings on the other transactions. No, it was not
- There were no parallel queries being executed at the time of this error message.
Then went on to check the locks held by this process. This process was holding a whopping 312 locks on different objects on that database! The pattern that I observed was that 25 worker threads were waiting for one worker thread to release the lock. There were 12 such locks held on different objects (25 + 1) * 12. The locks were acquired as soon as this query was fired.
Obviously these worker threads had put pressure on the Logical Schedulers. Once the query got executed successfully, this error message ceased to appear in the error log. I am yet to look into the query which in question, for sure it requires some tuning.
$sammy, I hope this answers your question !