Latches and Spinlocks - SQL Server 2012 Internals and Troubleshooting - page 196

residing on the disk. However, multiple steps are involved in removing a page from the buffer cache,
as the SQL Server engine maintains a hash table that lists which pages are currently in memory (oth-
erwise, it wouldn't know the memory address of the page). The DT latch cannot be taken out if any
other kind of latch is on the page, which makes the KP latch much more signii cant. A page that is
needed but isn't yet being read or written would use a KP latch to prevent the DT latch from being
acquired.

Latch Compatibility
The i ve latch types (ignoring the internal NL latch) are compatible as shown in Table 7-1. Note how
much simpler it is than the lock compatibility equivalent.

TABLE 7-1: Latch Types

KP SH UP EX DT

KP Y Y Y Y N

SH Y Y Y N N

UP Y Y N N N

EX Y N N N N

DT N N N N N

A page that has an EX latch on it can have a KP latch applied, but not any other type. Similarly, the
only type of latch that can exist on a page that needs an EX latch applied is a KP latch. Unlike
the lock compatibility table, there are no surprises in the latch compatibility table.

Despite the simplicity of this table, be sure you feel comfortable with the various scenarios that are
possible. Consider the page with the shared latch that allows an update latch to be acquired on it
(for an internal process to make a change to non-user data), but not an exclusive latch (which would
mean that actual data was changing). Consider the page that is being destroyed and doesn't allow
anything else to come near it; and the update latch, which prevents other update latches.

Grant Order
In any system, particularly as the number of processor threads grows, a number of requests will be
queued for a particular page. For example, a number of pages might be inserting data into a table
while others are reading that data, and the data may need to be moved from disk, and so on.

For a page that has no latches on it, the i rst process that wants a latch will be granted one. That's
straightforward; but when more processes start coming along, the behavior is slightly different.
A KP latch will skip the queue completely — unless there is a DT latch on the page, a KP latch will
jump ahead and keep it alive.

Other latches will wait, joining the queue (even if there is compatibility between the two — another
slight difference between lock behavior and latch behavior). When the current latch is released, the