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

FIGURE 7-18
In the original scenario, there would be a single latch with a wait list of items trying to reach it.
When the latch is released, the single wait list can be examined for compatible latches again, and
the shared latch re-acquired. As a SuperLatch, the SuperLatch remains acquired, and each sublatch
is handled by a processor. The sublatch then resides inside a single-processor microcosm, in a cache
local to the CPU, sort of like the chair in your house when you're the only person home — so that
processor has much freer access to the page as long as the sublatch is shared.
The problem appears when a processor needs an exclusive latch. To do this, the SuperLatch must
coordinate with all the sublatches to ensure that they're all converted to exclusive latches when the
time is right. It's a lot more expensive than acquiring a regular exclusive latch; so if this happens
often enough, the SuperLatch is demoted to an ordinary latch. SuperLatches are useful, but only on
pages that are almost always read-only.
MONITORING LATCHES AND SPINLOCKS
In terms of monitoring latches, you've already been introduced to some of the DMVs. There's more
to monitoring latches than just DMVs, though. Performance Monitor also offers useful informa-
tion about latches, as do extended events, which also provide information about spinlocks. Another
option is to use memory dumps, but those are not covered here.
DMVs
The DMVs covered earlier are a useful point of reference. You should familiarize yourself with the
contents of sys.dm_os_wait_stats , sys.dm_os_latch_stats , and sys.dm_os_spinlock_stats ,