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.
0 comments:
Post a Comment