Database Reference
In-Depth Information
To understand how to analyze a blocking scenario and the relevant information provided by the blocker script,
consider the following example. First, create a test table.
IF (SELECT OBJECT_ID('dbo.BlockTest')
) IS NOT NULL
DROP TABLE dbo.BlockTest;
GO
CREATE TABLE dbo.BlockTest
(C1 INT,
C2 INT,
C3 DATETIME
);
INSERT INTO dbo.BlockTest
VALUES (11, 12, GETDATE()),
(21, 22, GETDATE());
Now open three connections and run the following two queries concurrently. Once you run them, use the
blocker script in the third connection. Execute the following code in one connection:
BEGIN TRAN User1
UPDATE dbo.BlockTest
SET C3 = GETDATE();
Next, execute this code while the User1 transaction is executing:
BEGIN TRAN User2
SELECT C2
FROM dbo.BlockTest
WHERE C1 = 11;
COMMIT
This creates a simple blocking scenario where the User1 transaction blocks the User2 transaction.
The output of the blocker script provides information immediately useful to begin resolving blocking issues.
First, you can identify the specific session information, including the session ID of both the blocking and waiting
sessions. You get an immediate resource description from the waiting resource, the wait type, and the length of time in
milliseconds that the process has been waiting. It's that value that allows you to provide a filter to eliminate short-term
blocks, which are part of normal processing.
The database name is supplied because blocking can occur anywhere in the system, not just in
AdventureWorks2012 . You'll want to identify it where it occurs. The resources and types from the basic locking
information are retrieved for the waiting process.
The blocking request type is displayed, and both the waiting T-SQL and blocking T-SQL, if available, are
displayed. Once you have the object where the block is occurring, having the T-SQL so that you can understand
exactly where and how the process is either blocking or being blocked is a vital part of the process of eliminating or
reducing the amount of blocking. All this information is available from one simple query. Figure 20-14 shows the
sample output from the earlier blocked process.
Figure 20-14. Output from the blocker script
Be sure to go back to Connection 1 and commit or roll back the transaction.
 
Search WWH ::




Custom Search