Databases Reference
In-Depth Information
memory. An I/O request is scheduled and a
PAGIOLATCH_EX latch
is taken (assume granted) on a
BUF structure to allow the page to be read into the data page for the buffer. The task that initiated
the request will then submit an SH latch to read the data. However, this can appear as being blocked
by the EX latch if there is a lag retrieving the page from disk.
SUPERLATCHES/SUBLATCHES
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 7-17 and Figure 7-18) from
http://blogs.msdn.com/b/
FIGURE 7-17
Search WWH ::
Custom Search