Hi there,
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
Any help would be greatly appreciated.
Thanks.Did the blocking happen with the user tables or in tempdb?|||It happens on the user tables... Thanks.|||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.|||Since your job actually finishes, and it did not happened at night. My guess would be:
1. Some process running long update transaction, or
2.|||2. Your IO time slows down due to workload, that may increases chance of blocking too.|||If you cannot spot abnormalties in profiler, have a look at IO time.|||Thanks for all your replies. It was pointing to an application issue. Thanks again.
Friday, March 9, 2012
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment