Databases Reference
In-Depth Information
User Database
Tempdb
Append-Only Version Store
XSN-110
XSN-100
XSN-120
FIGURE 8-9
Figure 8-10 shows timeline 60. All transactions that required previous versions to maintain the
snapshot isolation level have now completed, so the stale versions stored in tempdb have been
cleaned up by a background thread.
NOTE
You'll i nd more in-depth information on snapshot isolation, including its
uses and its drawbacks, in Chapter 6.
User Database
Tempdb
Append-Only Version Store
XSN-120
FIGURE 8-10
TROUBLESHOOTING COMMON ISSUES
The unique nature of tempdb as a shared resource for temporary objects makes it more prone to
specii c performance problems than other databases. This section describes the most common issues
that tempdb is vulnerable to and how to troubleshoot or even avoid them.
Latch Contention
Compared to a normal database, tempdb's use as a temporary storage area makes the workload
pattern likely to contain a disproportionate amount of the creation and destruction of many small
objects. This type of workload can lead to latch contention on the pages required to allocate objects
in a database.
If you've read Chapter 7 on latches, then you know that a latch is a short-term synchronization lock
used by SQL Server to protect
physical
pages — it's covered only briel y here for the sake of context.
You can't inl uence latching behavior by changing the isolation level or by using “hints,” as you can
with normal locks; latches are used automatically behind the scenes to protect pages in memory from
being modii ed by another task while the content or structure is being changed or read from disk.
Search WWH ::
Custom Search