Database Reference
In-Depth Information
where
TL1.request_status <> TL2.request_status and
(
TL1.resource_description = TL2.resource_description OR
(TL1.resource_description is null and
TL2.resource_description is null)
)
option (recompile)
Figure 18-3.
Blocked and blocking sessions
For the next step of the troubleshooting process, you should find the root cause of the blocking. You can analyze
the execution plan of the blocked query based on the data in the
query_plan
column of the output. Figure
18-4
shows
the execution plan.
Figure 18-4.
Execution plan for the blocked query
As you can see from the execution plan, the blocked query is scanning the entire table. The query uses a
READ
COMMITTED
transaction isolation level, and it acquires a shared (S) lock on the every row in the table. As a result, at some point
the query is blocked by another query that holds an exclusive (X) lock on one of the rows. You can resolve the problem by
optimizing the query and adding the index on the
OrderNum
column. (As a reminder, this query was shown in Figure
18-1
.)
Even though in many instances you can detect and resolve the root cause of the blocking by analyzing and
optimizing the blocked query, this is not always the case. Consider the situation where you have a session that updated
a large number of rows in a table and thus acquired and held a large number of exclusive (X) locks on those rows. Other
sessions that need to access those rows would be blocked; even in the case of efficient execution plans that do not
perform unnecessary scans. The root cause of the blocking in this case is the
blocking
rather than a
blocked
session.
Unfortunately, it is almost impossible to detect the statement that acquired the locks without involving a major
effort. The queries from Listings 18-1 and 18-2 provide you with the information about currently running statements in
blocking sessions, rather than intelligence about the statement that caused the blocking condition. Moreover, in some
cases where a client application has an error and keeps an uncommitted transaction idle, queries do not return any
information at all. You can see such a condition in Figures
18-2
and
18-3
. In such cases, you need to analyze what code in
the blocking session has caused the blocking. You can use the
sys.dm_exec_sessions
view to obtain information about
the host and application of the blocking session. When you know which statement the blocking session is currently
executing, you can analyze the client and T-SQL code to locate the transaction to which this statement belongs. One of
the previously executed statements in that transaction would be the one that caused the blocking condition.
Collecting Blocking Information for Further Analysis
Although DMVs can be very useful in providing information about the current state of the system, they would not help
much if you did not run them at the exact the same time the blocking occurred. Fortunately, SQL Server helps capture
the blocking information automatically via the
blocked process report
. This report provides information about the
blocking condition, which you may retain for further analysis.
