Below feature in SQL Server use the version store in tempdb.
- Snapshot isolation , RCSI
- Online index rebuild
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
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 )
( 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
create table t1 (c1 int, c2 char(7000) not null)
set nocount on
declare @x int
set @x = 0
while (@x < 10000)
insert into t1 values (@x, 'I like daddy')
set @x = @x + 1
set nocount off
Update the table
update t1 set c1 = 5
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.