Database Reference
In-Depth Information
Chapter 18
Troubleshooting Blocking Issues
Blocking is perhaps one of the most common problems encountered in systems. When blocking occurs, multiple
queries block each other, which increases the execution time of queries and introduces query timeouts. All of that
negatively affects the user experience with the system.
This chapter provides an overview how you can troubleshoot blocking issues in a system.
General Troubleshooting Approach
Blocking occurs when multiple sessions compete for the same resource. Even though in some cases this is the correct
and expected behavior (for example, multiple sessions cannot update a same row simultaneously) more often than
not it happens because of unnecessary scans due to non-optimized queries.
Some degree of the blocking always exists in systems, and it is completely normal. What is not normal, however,
is excessive blocking. From the end user's standpoint, excessive blocking masks itself as a general performance
problem. The system is slow, queries are timing out, and there are deadlocks. With the exception of deadlocks,
slow performance is not necessarily the sign of the blocking issues: there could easily be non-optimized queries by
themselves. However, blocking issues can definitely contribute to a general system slow down.
■
One of the easiest ways to find out if the system suffers from blocking is by looking at the lock waits in the wait
statistics. Chapter 27, “System Troubleshooting,” discusses how to do that.
Note
In a nutshell, to troubleshoot blocking issues, you must follow these steps:
1.
Detect the queries involved in the blocking.
2.
Find out why blocking occurs.
3.
Fix the root cause of the issue.
SQL Server provides you with several tools that can help troubleshoot blocking issues in a system. These tools
can be separated into two different categories. The first category consists of data management views that you can use
to troubleshoot what is happening in the system at present. These tools are useful when you have access to the system
at the time of blocking, and you want to perform real-time troubleshooting.
The second category of tools allows you to collect information about blocking problems that previously occurred
in the system (usually about cases of long-time blocking) and retain it for the further analysis. Let's look at both
categories in detail.
