Blocking and Blocked Processes - SQL Server Query Performance Tuning - page 425

Optimize the Queries
Optimizing the queries executed by the blocking and blocked processes helps reduce the blocking duration. In the
blocking scenario, the queries executed by the processes participating in the blocking are as follows:

• Blocking process:

BEGIN TRAN User1
UPDATE dbo.BlockTest
SET C3 = GETDATE();

• Blocked process:

BEGIN TRAN User2
SELECT C2
FROM dbo.BlockTest
WHERE C1 = 11;
COMMIT
Next, let's analyze the individual SQL statements executed by the blocking and blocked SPIDs to optimize their
performance.

UPDATE statement of the blocking SPID accesses the data without a WHERE clause. This
makes the query inherently costly on a large table. If possible, break the action of the UPDATE
statement into multiple batches using appropriate WHERE clauses. Remember to try to use
set-based operations such as a TOP statement to limit the rows. If the individual UPDATE
statements of the batch are executed in separate transactions, then fewer locks will be held on
the resource within one transaction and for shorter time periods. This could also help reduce
or avoid lock escalation.

• The

SELECT statement executed by the blocked SPID has a WHERE clause on the C1 column.
From the index structure on the test table, you can see that there is no index on this column.
To optimize the SELECT statement, you could create a clustered index on the C1 column:

• The

CREATE CLUSTERED INDEX i1 ON dbo.BlockTest(C1);

■ since the example table fits within one page, adding the clustered index won't make much difference to the
query performance. however, as the number of rows in the table increases, the beneficial effect of the index will become
more pronounced.

Note

Optimizing the queries reduces the duration for which the locks are held by the processes. The query
optimization reduces the impact of blocking, but it doesn't prevent the blocking completely. However, as long as the
optimized queries execute within acceptable performance limits, a small amount of blocking may be ignored.

Decrease the Isolation Level
Another approach to resolve blocking can be to use a lower isolation level, if possible. The SELECT statement of the
User2 transaction gets blocked while requesting an (S) lock on the data row. The isolation level of this transaction
can be mitigated by taking advantage of SNAPSHOT isolation level Read Committed Snapshot so that the (S) lock is