- Blocking and Deadlocks
- Common Causes of Blocking
- Troubleshooting Blocking
- Resolving Blocking Issues
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Resolving Blocking Issues
So far, this article has revealed to you what the blocking problems are, what causes them, and how to identify the roots of such problems. Resolving the blocking problems might take quite a few steps; we'll go over some of the more common ones in the next few paragraphs.
A quick way to resolve a blocking problem is to disconnect the connection blocking the rest of the users. All you have to do is ask your database administrator to execute the KILL statement. Obviously, this is not a permanent solution, and won't really help anything except take care of an immediate need.
If you deduce that the blocking is caused by a poorly written transaction, try to rewrite it. Often, a single transaction might bring the entire application to its knees. Other times, you'll have to review many (or all) stored procedures of your application before you can resolve problems.
If you see many table locks in your database, you might want to evaluate your indexing strategy. The blocking problems can often be resolved by adding appropriate indexes to the affected tables. The index study is a serious exercise, and it takes quite a bit of effort—so don't take it lightly.
If a problem is caused by poor database design, then you can try to rework the database model. If you experience blocking in the early stages of the application design, you're in luck because you might be able to afford reworking the design. If the problems did not crop up until the application is deployed to the users, reworking the design might not be an option. In such cases, you might wish to direct some of the transactions to another server, and use replication to keep two databases synchronized.
Finally, if you think the query hints are forcing the activity that you don't care to see, try rewriting your queries without such hints. Nine times out of ten, SQL Server can make the best locking decision on its own.