SQL Server Blocking

Common Causes of Blocking and What to Do

Insufficient indexes: run the Index Tuning Wizard for a quick start on tuning.
No clustered index on large tables: every table should have a clustered index on a narrow, rarely updated key.
Transactions not committed or rolled back, possibly due to errors not properly handled or code improperly branched out of: handle all errors and disconnects.
Using "implicit transactions on" and then not explicitly closing EVERY transaction even on errors: avoid setting implicit transactions on.
Statistics are not up to date: periodically run UPDATE STATISTICS, periodically run UPDATE STATISTICS WITH FULLSCAN.
Poorly written queries: use Profiler to find slow running queries and see if they can be rewritten or if indexes can be added to support them.
A large amount of data is returned, perhaps not handled fast enough by the client to keep up with the rate at which SQL sends it.
Assuming that terminating a query is equivalent to a rollback: it's not -- any time you exit from a query you should check the @@trancount to see if you are at the expected transaction level.
Client did not fetch all rows to completion: always fetch all rows to completion.
Orphaned connections: see the "Orphaned Connections" topic in SQL Server 7.0 Books Online.
Using cursors when a set-based operation is possible: set based operations are almost always faster than cursors.
Non-default SQL Server configurations, especially turning on "priority boost", "lightweight pooling", or "set working set size": leave all configuration settings at the default values unless there is a specific and well-tested reason for doing so.
Hardware errors and/or database corruption: run periodic hardware diagnostics and database DBCCs and check the output.
Back to Top

Blocking References

Q224453 INF: Resolving SQL Server 7.0 or 2000 Blocking Problem
Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks
Q75722 INF: Reducing Lock Contention in SQL Server

Deadlock References

Search Books Online for "Deadlock"
Q215520 INFO: COM+ and MTS Always Use SERIALIZABLE Transaction Isolation
Q46435 INF: Deadlocks and Timeouts
Q43199 INF: Concurrency and Consistency and SQL Server Alternatives
Q75722 INF: Reducing Lock Contention in SQL Server
Q58534 INF: Specifying Priority for Deadlock Victim Selection
Q47162 INF: Deadlock Scenarios and Avoidance in SQL
Q239753 BUG: 1205 - Deadlock Situation Not Detected by SQL Server
Q48712 INF: Handling Timeouts Correctly in DB-Library
Q259753 FIX: Deadlock in OLE DB Session Pooling in an STA COM Object

SQL Server Directory

How to Troubleshoot a Performance Problem
Troubleshooting Blocking
How to Move a Database
Get the Latest Service Pack
Misc. Tips and Tools
SQL Server Knowledge Base Articles
Common SQL Server Answers
SQL Server and Related Links