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

not requested by the SELECT statement. The Read Committed Snapshot isolation level can be configured for the
connection using the SET statement.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED SNAPSHOT;
GO
BEGIN TRAN User2
SELECT C2
FROM dbo.BlockTest
WHERE C1 = 11;
COMMIT
GO
--Back to default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

This example shows the utility of reducing the isolation level. Using this SNAPSHOT isolation is radically preferred
over using any of the methods that produce dirty reads that could lead to incorrect data or missing or extra rows.

Partition the Contended Data
When dealing with large data sets or data that can be discretely stored, it is possible to apply table partitioning to
the data. Partitioned data is split horizontally, that is, by certain values (such as splitting sales data up by month, for
example). This allows the transactions to execute concurrently on the individual partitions, without blocking each
other. These separate partitions are treated as a single unit for querying, updating, and inserting; only the storage
and access are separated by SQL Server. It should be noted that partitioning is available only in the Developer and
Enterprise editions of SQL Server.
In the preceding blocking scenario, the data could be separated by date. This would entail setting up multiple
filegroups if you're concerned with performance (or just put everything on PRIMARY if you're worried about
management) and splitting the data per a defined rule. Once the UPDATE statement gets a WHERE clause, then it and the
original SELECT statement will be able to execute concurrently on two separate partitions. This does require that the
WHERE clause filters only on the partition key column. As soon as you get other conditions in the mix, you're unlikely to
benefit from partition elimination, which means performance could be much worse, not better.

■ partitioning, if done properly, can improve both performance and concurrency on large data sets. But, partition-
ing is primarily a data management solution, not a performance tuning option.

Note

In a blocking scenario, you should analyze whether the query of the blocking or the blocked process can be fully
satisfied using a covering index. If the query of one of the processes can be satisfied using a covering index, then it will
prevent the process from requesting locks on the contended resource. Also, if the other process doesn't need a lock
on the covering index (to maintain data integrity), then both processes will be able to execute concurrently without
blocking each other.
For instance, in the preceding blocking scenario, the SELECT statement by the blocked process can be fully
satisfied by a covering index on the C1 and C2 columns.

CREATE NONCLUSTERED INDEX iAvoidBlocking ON dbo.BlockTest(C1, C2) ;