Database Reference
In-Depth Information
As with real-time troubleshooting, you should analyze both blocking and blocked processes and find the root
cause of the problem. From the blocked process standpoint, the most important information is:
waittime
: The length of time the query is waiting, in milliseconds.
lockMode
: The type of lock being waited for.
isolationlevel
: The transaction isolation level.
executionStack and inputBuf
: The query and/or the execution stack. You will see how to
obtain the actual SQL statement involved in blocking in Listing 18-5.
From the blocking process standpoint, you must look at:
status
: Status is whether the process is
running
,
sleeping
or
suspended
. In the case in which
the process sleeping, there is an uncommitted transaction. When the process is suspended,
that process either waits for the resource (for example, page from the disk) or there is
a blocking chain involved. We will talk more about the SQL Server Execution Model in
Chapter 27, “System Troubleshooting.”
trancount
: a
trancount
value greater than 1 indicates nested transactions. If the process
status is
sleeping
at the same time, then there is a good chance that the client did not
commit the nested transactions correctly (for example, the number of
commit
statements is
less than the number of
begin tran
statements in the code).
executionStack and inputBuf
: As we already discussed, in some cases you need to analyze
what happens in the blocking process. Some common issues include run-away transactions
(for example, missing
commit
statements in the nested transactions); long running
transactions with, perhaps, some UI involved; excessive scans (for example, a missing index
on the referencing column in the detail table leads to scans during a referential integrity
check). Information about queries from the blocking session could be useful here.
Nevertheless, for the most part blocking occurs because of unnecessary scans due to non-optimized queries
and, in a large number of cases, you can detect it by analyzing blocked queries. So, the next logical step is to look at
the blocked query execution plan and detect inefficiencies. You can either run the query and check execution plan, or
use DMVs and obtain an execution plan from
sys.dm_exec_query_stats
based on the
sql_handle
,
stmtStart
, and
stmtEnd
elements from the execution stack. Listing 18-5 and Figure
18-6
show the code and query output.
Listing 18-5.
Obtaining Query Text and Execution Plan
declare
@H varbinary(max) =
/* Insert sql_handle from the top line of the execution stack */
,@S int =
/* Insert stmtStart from the top line of the execution stack */
,@E int =
/* Insert stmtEnd from the top line of the execution stack */
select
substring(
qt.text,
(qs.statement_start_offset / 2) + 1,
((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
