1 Tempdb usage: Version store

Below feature in SQL Server use the version store in tempdb.

• Snapshot isolation , RCSI
• Online index rebuild
• Trigger
• MARS

Characteristic for version store

• Uniform extend
• No log
• Page type is 2 ( INDEX_PAGE)
• Buffer is hash
• Backup group async process to clean up the no used extend allocation

 

For online index rebuild, new version would be generate when the index page is being changed while the online index rebuild is in progress.

Create the test environment

use master

CREATE DATABASE [summer_vs] ON  PRIMARY 

( NAME = N'summer_vs_data', FILENAME = N'C:\DATA\summer_vs_data.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'summer_vs_log', FILENAME = N'C:\DATA\summer_vs_log.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);

alter database summer_vs set read_committed_snapshot on

go

use summer_vs

go

create table t1 (c1 int, c2 char(7000) not null)

go

set nocount on

go

declare @x int

set @x = 0

while (@x < 10000)

begin

insert into t1 values (@x, 'I like daddy')

set @x = @x + 1

end

go

set nocount off

go

 

Update the table

update t1 set c1 = 5

 

 

DMV: sys.dm_tran_version_store

Because the table has 10000 records and we are updating 10000 records, we can see there are 10000 versions being generated.

select * from sys.dm_tran_version_store

Perf mon counter to monitor the size

Peformon counter shows the version store size

 

When version is no longer needed, the async process would clean up the space. When it happened, we will get empty result from sys.dm_tran_version_store.

 

Reference

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/12/22/managing-tempdb-in-sql-server-tempdb-basics-verison-store.aspx