Freigeben über

Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)

  • Artikel

In order to understand the version store, let me start with an example. I will use a database that has RCSI (read committed snapshot) and SI (Snapshot Isolation) enabled as it provides more controlled environment to manage versions.

 

When a database is enabled for RCSI/SI, any update of a row will create a row version. This version stays in the version store as long as it is needed. Now, that is a tricky statement. How does SQL Server know when to reclaim the version? Some other questions that may cross your mind are: Is not reclaiming (i.e. deleting) a row version expensive? Does SQL reclaim row versions one at a time or a group of them? Each user table has different schema (i.e. different number of columns and their types) so does the SQL Server store the row versions from each table separately? What is the overhead of row versioning?

 

Let me first create a database and enable SI/RCSI.

 

create database snapshottest

go

-- Setting database for snapshot based isolations. AS you can

-- see that enabling SI/RCSI is a simple DDL operation.

alter database snapshottest set read_committed_snapshot ON

go

alter database snapshottest set allow_snapshot_isolation ON

go

 

-- check the snapshot state of the database and it will

-- show that both SI and RCSI have been enabled.

select  is_read_committed_snapshot_on,

   snapshot_isolation_state_desc,

        snapshot_isolation_state

from sys.databases

where name='snapshottest'

go

 

--create a table with each row > 1000 bytes

create table t_version (c1 int, c2 char(1000))

go

--Load 50 rows. Note,I start the transaction but did not

--commit it yet.

Begin tran

declare @i int

select @i = 0

while (@i < 50)

begin

insert into t_version values (@i, 'hello')

set @i = @i + 1

end

 

Now you can use the following DMV to look at the version store. You will notice that it does not show any rows in the version store. This is the first observation I want you to make. The INSERT operation does not cause a row version to be generated because there is really no previous version of the row being inserted. There is a special when an insert causes a row version to be created but I will cover that later to keep this topic simple. This means that when you do Bulk Import into a SI/RCSI enabled database, SQL Server is not creating any row versions.

 

select COUNT(*) from sys.dm_tran_version_store

-- Now commit the transaction

commit

 

 Now let us update all the 50 rows in the table. Now, since the row is being updated, SQL Server will copy the older version of the row into the version store. So, we will expect 50 row versions

 -- update all the rows in the table

update t_version set c2 ='test10'

 

-- the following statement returns the count of versioned row.

-- And, for the case here, it will return 50

select COUNT(*) from sys.dm_tran_version_store

Note, that the UPDATE statement was not run in an explicit transaction so the above statement was the only statement in the “implicit” transaction. Also, since there was no other concurrent transactions that would be interested in the row versions just created, these versions are garbage collected by SQL Server. If you run the following query after minute or so, you will realize that row versions have been garbage collected.

 

-- the following statement returns 0 if run after a minute or so

select COUNT(*) from sys.dm_tran_version_store

Minimal condition for a version row to be garbage collected is when SQL Server determines, based on the transactional states, that this version is no longer needed. In the worst case, if you have a long running transaction that has either created the row version or needs it, the version row cannot be removed and this can cause version store to grow and it can potentially use up all the space in TempDB just like a long running transaction can cause transaction log to fill up. I will describe this in more details later.

 

Just like UPDATE, when a row is deleted, a row version is created. So both DELETE and UPDATE operations lead to row versions.

 

In the next blog, I will describe the version store layout.

Thanks

Sunil Agarwal

Comments

  • Anonymous
    December 30, 2008
    PingBack from http://www.codedstyle.com/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example/

  • Anonymous
    December 31, 2008
    The comment has been removed

  • Anonymous
    December 31, 2008
    SunilA has a great set of posts out on the SQL Server Storage Engine blog about managing TEMPDB. It's

  • Anonymous
    December 31, 2008
    Akelly: thanks. I forgot to put the commit...I updated the blog. Thanks for your interest and feedback.

  • Anonymous
    September 04, 2012
    Just okay ...

  • Anonymous
    November 30, 2016
    The comment has been removed