If you think about what kinds
of pages would have latches applied to them frequently, it’s easy to
consider the exclusive latches on insert pages; but a far more commonly
latched page would be the root page of a frequently used index. Every
time a seek is performed on an index, the root page must be read to
help point the way to the page containing the rest of the data. Even
tables that are frequently written to have a lot of shared access (for
reading) on the root page of the indexes on those tables. The root
pages probably don’t need to change very often at all, but they need to
be read repeatedly.
The queuing method of accepting all compatible
latches each time the latching check is done only helps so far. It’s
still a lot of work to manage all this. Enter the SuperLatch (or
sublatch). SuperLatches improve the performance of systems with 32 or
more logical processors by promoting a single latch into an array of
sublatches, one for each CPU core. This way, each core can easily
acquire a sublatch without having to apply the shared latch to the
page, because it’s already taken out.
The PSS SQL blog site has some useful diagrams showing how this looks, which they have generously let us use here (see Figure 1 and Figure 2).
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.
