Database Reference
In-Depth Information
<blocking-process>
<process status="sleeping" spid="52" sbid="0" ecid="0" priority="0" trancount="1"
lastbatchstarted="2014-03-26T20:31:36.567" lastbatchcompleted="2014-03-26T20:31:36.567"
lastattention="1900-01-01T00:00:00.567" clientapp="Microsoft SQL Server Management Studio -
Query" hostname="WIN-TUTJRM1T45J" hostpid="2324" loginname="WIN-TUTJRM1T45J\Administrator"
isolationlevel="read committed (2)" xactid="255368" currentdb="5" lockTimeout="4294967295"
clientoption1="671098976" clientoption2="390200">
<executionStack />
<inputbuf>
SET STATISTICS IO OFF SET STATISTICS TIME OFF </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
The elements are clear if you look through this XML.
<blocked-process>
shows information about the process
that was blocked, including familiar information such as the session ID (labeled with the old-fashioned SPID here),
the database ID, and so on. You can see the query in the
<inputbuf>
element. Details such as the
lockMode
are
available within the
<process>
element. Note that the XML doesn't include some of the other information that you
can easily get from T-SQL queries, such as the query string of the blocked and waiting processes. But with the SPID
available, you can get them from the cache, if available, or you can combine the Blocked Process report with other
events such as
rpc_starting
to show the query information. However, doing so will add to the overhead of using
those events long term within your database. If you know you have a blocking problem, this can be part of a short-
term monitoring project to capture the necessary blocking information.
Blocking Resolutions
Once you've analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few
techniques you can use to do this:
•
Optimize the queries executed by blocking and blocked SPIDs.
•
Decrease the isolation level.
•
Partition the contended data.
•
Use a covering index on the contended data.
■
a detailed list of recommendations to avoid blocking appears later in the chapter in the “recommendations to
reduce Blocking” section.
Note
To understand these resolution techniques, let's apply them in turn to the preceding blocking scenario.
