SQL Server Version stores

Version stores in SQL Server are used for row versioning in the tempdb database. When a modification is made to a row, a copy of the original row is stored in the version store. This allows other transactions to read the original version of the row while the modification is being made. The version store can have an impact on the tempdb database because it uses space in tempdb. If the version store grows too large, it can cause tempdb to fill up and potentially cause performance issues. Therefore, it is important to monitor the size of the version store and take steps to manage it if necessary.

Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions. Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

The tempdb version store in SQL Server is a system-managed area in the tempdb database used to store row versions for features that require them, like:

Ø   Read Committed Snapshot Isolation (RCSI)

Ø   Snapshot Isolation (SI)

Ø   Online Index Operations

Ø   Triggers (after triggers can use row versions)

Ø   MARS (Multiple Active Result Sets)

Let’s see the example

Creating database

create database VersionstoresDBDemo ; GO alter database VersionstoresDBDemo SET READ_COMMITTED_SNAPSHOT ON; alter database VersionstoresDBDemo SET ALLOW_SNAPSHOT_ISOLATION ON;

 

Now creating a table and inserting few records

CREATE TABLE Employee (     EmpId INT PRIMARY KEY,     Salary INT );   INSERT INTO Employee VALUES (1,50000),(2,60000);

See the records into this table

Now we are going to update the record in transaction and not yet committed.

BEGIN TRAN UPDATE Employee SET Salary = Salary + 10000 WHERE EmpId = 1;

Record is not yet updated. Row changed but NOT committed.

See the record in the other window.

Record is updated but still here we are not seeing the updated value. SQL Server now stores old copy of row into Version Store in TempDB.

SQL Server uses version store in tempdb to store old version of the row, not just IN_ROW_DATA, but also LOB_DATA and ROW_OVERFLOW_DATA allocation units. It is used for DELETE and UPDATE statements, but NOT for INSERT (since there is no “old” row to store when you insert a new one). Strangely, version store allocates twice the size of old (deleted/updated) version of the row. Therefore, beware of UPDATE and DELETEs of the LOBs in RCSI isolation, they might take a huge amount of version store space!