Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
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
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 removedAnonymous
December 31, 2008
SunilA has a great set of posts out on the SQL Server Storage Engine blog about managing TEMPDB. It'sAnonymous
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