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

In short, without latching, data is lost. With latching, it's not. It's that simple. Regardless of what
kind of isolation level is being used by the transaction, SQL Server protects data with latches.
LATCH TYPES
As you learned in the preceding section, latches exist to protect in-memory data. There are hundreds
of different types of latch, most of which you are unlikely to encounter in any meaningful way when
you are working with SQL Server. While latch waits will occasionally show up in sys.dm_os_wait_
stats , you normally have to actively search for them. As a rule, they don't come to you.
Typically, latches are divided into two distinct categories in SQL Server. They either serve the buffer
pool, in which case they are known as BUF latches (showing up as PAGELATCH or PAGEIOLATCH in
sys.dm_os_wait_stats and aggregated into the BUFFER latch class in sys.dm_os_latch_stats) ,
or they don't, in which case they are grouped under the non-buffer ( Non-BUF ) heading. This is a
slight generalization, but it's adequate for our purposes here.
If you run the following query, you will get a list of more than 150 latch types (code i le
Ch7LatchTypes.sql ):
SELECT *
FROM sys.dm_os_latch_stats;
If you order this data by any of the three numeric columns, you'll see that by far the most common
latch type is BUFFER . If you look at the contents of sys.dm_os_wait_stats , you'll see latches that
are prei xed with LATCH _, PAGELATCH _ and PAGEIOLATCH _.
The LATCH _ waits are all for the Non-BUF types. There are many of these, ensuring that the
database engine can handle many of the operations it needs to perform. If you look through those
latch types in sys.dm_os_latch_stats , you will see things such as BACKUP _ FILE _ HANDLE latches,
SERVICE _ BROKER latches, and even VERSIONING latches, which may be involved in your transactions
depending on the isolation level.
The PAGELATCH _ latches are like those you saw in the example earlier. Data from a user object is
needed, and to ensure that it can be written or read consistently, a latch is acquired. These buffer
latches can be applied to all kinds of pages, including Page Free Space (PFS), Global Allocation Map
(GAM), Shared Global Allocation Map (SGAM), and Index Allocation Map (IAM) pages.
The PAGEIOLATCH _ latch types are used when data is being moved from disk into RAM. An I/O
operation is in play when a I/O latch is needed. In some ways, this is the easiest type latch wait to
troubleshoot, as high PAGEIOLATCH wait times imply that the I/O subsystem cannot keep up. If this
is the case, and you can't mitigate the problem through I/O reduction or increased RAM, you have a
nice argument for buying that faster storage you've been wanting.
LATCH MODES
Latch modes are far easier to contemplate than lock modes. I'm sure you remember from Chapter
6 that nasty big matrix of lock compatibilities. Latches have far fewer modes, and compatibility is
much more straightforward.