Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

Microsoft SQL Server 2012 : Knowing Tempdb - Overview and Usage (part 2) - The Version Store

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
12/21/2014 8:50:20 PM

Internal Temporary Objects

Internal temporary objects are objects used by SQL Server to store data temporarily during query processing. Operations such as sorts, spools, hash joins, and cursors all require space in tempdb to run.

To see how many pages have been allocated to internal objects for each session, look at the internal_object_alloc_page_count column in the sys.dm_db_session_space_usage DMV.

The Version Store

Many features in SQL Server 2012 require multiple versions of rows to be maintained, and the version store is used to store these different versions of index and data rows. The following features make use of the version store:

  • Triggers — These have used row versions since SQL Server 2005, rather than scan the transaction log as they did in SQL Server 2000.
  • Snapshot Isolation and Read-Committed Snapshot Isolation — Two new isolation levels based on versioning of rows, rather than locking.
  • Online Index Operations — Row versioning to support index updates during an index rebuild.
  • MARS (Multiple Active Result Sets) — Row versioning to support interleaving multiple batch requests across a single connection.

Version Store Overhead

The overhead of row versioning is 14 bytes per row, which consists of a transaction sequence number referred to as an XSN and a row identifier referred to as a RID. You can see this illustrated in Figure 5.

FIGURE 5

image

The XSN is used to chain together multiple versions of the same row; the RID is used to locate the row version in tempdb.

The 14-byte overhead doesn’t reduce the maximum possible row size of 8,060 bytes, and it is added the first time a row is modified 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 6 with a computer that has four CPU cores.

FIGURE 6

image

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.

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

FIGURE 7

image

Along the bottom of the diagram a timeline is represented from 0 to 60; the horizontal arrows represent the duration of a specific transaction. The sequence of events occurs like this:

1. At timeline 10 a transaction called Read1 starts and reads the row associated with XSN-100.
2. At 20 another transaction called Write1 starts, which wants to modify the row. Snapshot isolation guarantees a repeatable read for Read1 and ensures that any new readers can read committed data at the point a write starts. Therefore, it copies the rows associated with XSN-100 to the version store and allows Write1 to modify the row under XSN-110.
3. Read2 starts before Write1 has committed, so the version chain is traversed from XSN-110 to XSN-100 in the version store to get the last committed value.
4. Read3 starts after Write1 has committed and reads the value from XSN-110.
5. Write2 now starts and wants to modify the row. Read1 and Read2 still need the version under XSN-100 and Read3 needs the version under XSN-110, so a new version is created for XSN-120, and XSN-110 is moved to the version store in tempdb.
6. Write2 commits XSN-120.
7. Read1 completes, but XSN-100 is still being used by Read2.
8. Read2 completes and XSN-100 is now stale.
9. Read3 completes and XSN-110 is now stale.

A background thread removes stale versions of rows from tempdb every minute, so at that point only the result of the write operation carried out by transaction Write2 will be stored and no previous versions will be available or stored in tempdb.

Figure 8 represents the state of the row on the data page and the versions stored in tempdb at timeline 0. You can see that the only available result is the currently committed value as of XSN-100.

FIGURE 8

image

Figure 9 shows the state at timeline 45. Two versions are being maintained in tempdb to provide a repeatable read for the Read1, Read2, and Read3 transactions.

FIGURE 9

image

Figure 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.

FIGURE 10

image
Other -----------------
- Microsoft Lync Server 2013 : Windows Client - Navigating in the Client - Useful Lync Client Shortcuts
- Microsoft Lync Server 2013 : Windows Client - Navigating in the Client - Managing Contacts, Managing Groups, Status View
- Microsoft Lync Server 2013 : Windows Client - Navigating in the Client - Configuring Basic Options
- Microsoft Lync Server 2013 : Windows Client - Installing the Client
- Microsoft Exchange Server 2013: Configuring mailbox delivery restrictions, permissions, and storage limits (part 4) - Setting storage restrictions on mailbox and archives
- Microsoft Exchange Server 2013: Configuring mailbox delivery restrictions, permissions, and storage limits (part 3) - Permitting others to access a mailbox
- Microsoft Exchange Server 2013: Configuring mailbox delivery restrictions, permissions, and storage limits (part 2) - Setting message send and receive restrictions on individual mailboxes
- Microsoft Exchange Server 2013: Configuring mailbox delivery restrictions, permissions, and storage limits (part 1) - Setting message size restrictions on delivery to and from individual mailboxes
- Microsoft Exchange Server 2013: Moving mailboxes (part 3) - Performing on-premises mailbox moves
- Microsoft Exchange Server 2013: Moving mailboxes (part 2) - Performing on-premises mailbox moves and migrations
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer