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

■ Modifying locks like this should be a last resort after many other options have been tried. this could cause
significant locking overhead that would seriously impact the performance of the system.

Note

Figure 20-11 displays the output of sys.dm_tran_locks executed from a separate connection.

Figure 20-11. Output from sys.dm_tran_locks showing the effect of sp_index option on lock granularity

The only lock acquired by the transaction on the test table is an (X) lock on the table.
You can see from the new locking behavior that disabling the KEY lock escalates lock granularity to the table level.
This will block every concurrent access to the table or to the indexes on the table; consequently, it can seriously hurt
the database concurrency. However, if a nonclustered index becomes a point of contention in a blocking scenario,
then it may be beneficial to disable the PAG locks on the index, thereby allowing only KEY locks on the index.

■ Note Using this option can have serious side effects. You should use it only as a last resort.

Effect of a Clustered Index
Since for a clustered index the leaf pages of the index and the data pages of the table are the same, the clustered index
can be used to avoid the overhead of locking additional pages (leaf pages) and rows introduced by a nonclustered
index. To understand the locking overhead associated with a clustered index, convert the preceding nonclustered
index to a clustered index.

CREATE CLUSTERED INDEX iTest ON dbo.Test1(C1) WITH DROP_EXISTING;

If you run the locking script again and query sys.dm_tran_locks in a different connection, you should see the
resultant output for the LockBehavior transaction on iTest shown in Figure 20-12 .

Figure 20-12. Output from sys.dm_tran_locks showing the effect of a clustered index on locking behavior