Currently using SQL Server 2000 (SP4). The following condition started occurring last week:
- Server has excessive blocking
- Majority of the processes are in runnable state
- Excessive blocking happens for a few mins. and repeats again during the day. Does not happen at night.
- Nothing on the server errorlog, profiler
- CPU averages 40 - 50% at that point of excessive blocking
This is probably a fault of the database application, not the server. Poorly designed schemas and bad code are the primary culprits of blocking, which may not occur until the application use scales beyond that which it was tested.
Try to figure out which sprocs or statements are causing the blocking, and then try to optimize them.
If it's not practically useful, then it's practically useless.