Sunday, July 7, 2013

0 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

image

Perf mon counter to monitor the size

Peformon counter shows the version store size

image

 

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates