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

If you query sys.dm_os_wait_stats as follows (code i le Ch7LatchModes.sql ), you'll see the
different modes listed there. This query is looking at the PAGELATCH_ latches, but you could use
it for PAGEIOLATCH_ or LATCH_ instead and see the same latch modes. They are the two character
combinations following the underscore.
SELECT *
FROM sys.dm_os_wait_stats
where wait_type like 'PAGELATCH%';
Six latch modes are listed, usually in the following order: NL, KP, SH, UP, EX, DT. While there's no
guarantee they'll appear in this order if you don't specify an ORDER BY clause, this is the order you'll
likely see.
NL
NL is an internal Null latch. You don't need to consider it. It essentially means no latch is being
used, so it isn't even recorded under normal conditions.
KP
KP is a Keep latch, used to indicate that a particular page is needed for something and shouldn't be
destroyed.
SH
This refers to a Shared latch, which is needed to read the data from a page.
UP
This is an Update latch, which indicates that a page is being updated, but not the table data within
it. This is not related to the T-SQL UPDATE statement, which requires an Exclusive latch (the next
mode discussed). Update latches are more common for internal operations, such as maintaining PFS
pages or updating the checksum bits on a page. Because the type of data being updated is not needed
to service queries, it is compatible with a shared latch, but not another Update latch.
EX
When data is being explicitly changed or added, an Exclusive latch is required. This is the most
common type of latch for troubleshooting purposes, as two EX latches cannot be held on the same
page at the same time. While this is also true of UP latches, EX latches are the more common of the
two.
DT
The presence of this latch, the Destroy latch, means that the page is in the process of being removed
from memory. A page that is deleted picks up a DT latch from the lazywriter process while the
record of the page is removed. Bear in mind that this does not necessarily mean that the data is
being deleted — it may simply be removed from the buffer cache, with a copy of the data still