TempDB - 1

When you change a value in a normal database, both the old value and the new value are stored in the transaction log. The old value is used in case you need to roll-back the transaction that made the change (undo), and the new value is used to roll-forward the change during recovery (redo) if it hadn’t made it to the data file before the restart. You still need to be able to undo a change in tempdb but you’ll never need to redo the change as everything is thrown away on restart. Therefore, tempdb doesn’t store the redo information, which can result in significant performance gains when making many changes to big columns compared to a user database. 

 

➤Tempdb is always set to “Simple” recovery mode, which, means that transaction log records for committed transactions are marked for reuse after every checkpoint. This means you don’t need to back up the transaction log for tempdb, and in fact, you can’t back up tempdb at all.   

➤Tempdb can only have one filegroup (the PRIMARY filegroup); you can’t add more.  

➤Tempdb is used to store three types of objects: user objects, internal objects, and the version store. 

 

Many features in SQL Server 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. You can search SQL Server Books Online for more information on this. 

 

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. The XSN is used to chain together multiple versions of the same row; the RID is used to locate the row version in tempdb.