Microsoft® SQL Server 2012 Unleashed

Index Locking

As with locks on data pages, SQL Server manages locks on index pages internally. There is the opportunity for greater locking contention in index pages than in data pages. Contention at the root page of the index is the highest because the root is the starting point for all searches via the index. Contention usually decreases as you move down the various levels of the B-tree, but it is still higher than contention at the data page level due to the typically greater number of index rows per index page than data rows per data page.

If locking contention in the index becomes an issue, you can use ALTER INDEX to manage the locking behavior at the index level. The syntax of this command is as follows:

Click here to view code image