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

Figure 20-2. Output from sys.dm_tran_locks showing the key-level lock granted to the DELETE statement

When you are querying sys.dm_tran_locks , you will be able to retrieve the database identifier, resource_
database_id . You can also get information about what is being locked from resource_associated_entity_id ;
however, to get to the particular resource (in this case, the page on the key), you have to go to the resource_
description column for the value, which is 1:24117 . In this case, the Index ID of 1 is the clustered index on the
dbo.Test1 table. You also see the types of requests that are made: S, Sch-S, X, and so on. I cover these in more detail
in the upcoming “Lock Modes” section.

■ You'll learn about different values for the IndId column and how to determine the corresponding index name in
this chapter's “effect of indexes on locking” section.

Note

Like the row-level lock, the key-level lock provides very high concurrency.

Page-Level Lock
A page-level lock is maintained on a single page within a table or an index, and it is identified as a PAG lock. When a
query requests multiple rows within a page, the consistency of all the requested rows can be maintained by acquiring
either RID/KEY locks on the individual rows or a PAG lock on the entire page. From the query plan, the lock manager
determines the resource pressure of acquiring multiple RID/KEY locks, and if the pressure is found to be high, the lock
manager requests a PAG lock instead.
The resource locked by the PAG lock may be represented in the following format in the resource_description
column of sys.dm_tran_locks :

DatabaseID:FileID:PageID

The page-level lock can increase the performance of an individual query by reducing its locking overhead, but it
hurts the concurrency of the database by blocking access to all the rows in the page.

Extent-Level Lock
An extent-level lock is maintained on an extent (a group of eight contiguous data or index pages), and it is identified
as an EXT lock. This lock is used, for example, when an ALTER INDEX REBUILD command is executed on a table and
the pages of the table may be moved from an existing extent to a new extent. During this period, the integrity of the
extents is protected using EXT locks.