Knowing Tempdb - SQL Server 2012 Internals and Troubleshooting - page 218

The 14-byte overhead doesn't reduce the maximum possible row size of 8,060 bytes, and it is added
the i rst time a row is modii ed or inserted in the following circumstances:
➤

You're using snapshot isolation.

➤

The underlying table has a trigger.

➤

You're using MARS.

➤

An online index rebuild is running on the table.

It is removed in these circumstances:

➤

Snapshot isolation is switched off.

➤

The trigger is removed.

➤

You stop using MARS.

➤

An online index rebuild is completed.

You should also be aware that creating the additional 14 bytes could cause page splits if the data
pages are full and will affect your disk space requirement.

Append-Only Stores
The row versions are written to an append-only store of
which there are two; index rebuilds have their own version
store and everything else uses the common version store. To
increase scalability, each CPU scheduler has its own page in
the version store to store rows, as illustrated in Figure 8-6
with a computer that has four CPU cores. See Chapter 5 for
more information about CPU cores and schedulers.

Append-Only Version Store

0 1 2 3

You can view the entire contents of the version store using
the sys . dm _ tran _ version _ store DMV, but use it with care
as it can be resource intensive to run.

Schedulers

FIGURE 8-6

For an example demonstrating how row versioning is used,
Figure 8-7 illustrates an example of multiple read and write transactions operating under snapshot
isolation.

Read3 (xsn-110)

Read2 (xsn-100)

Write1 (xsn-110 ) W rite2 (xs n-120)

Read1 (xsn-100)

0 10 20 30 40 45 50 55 60

FIGURE 8-7