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
-- 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
alter database snapshottest set allow_snapshot_isolation ON
-- check the snapshot state of the database and it will
-- show that both SI and RCSI have been enabled.
--create a table with each row > 1000 bytes
create table t_version (c1 int, c2 char(1000))
--Load 50 rows. Note,I start the transaction but did not
--commit it yet.
declare @i int
select @i = 0
while (@i < 50)
insert into t_version values (@i, 'hello')
set @i = @i + 1
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
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
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
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.
PingBack from http://www.codedstyle.com/managing-tempdb-in-sql-server-tempdb-basics-version-store-simple-example/
I believe you need a COMMIT just before the UPDATE statement to get the behavior you mentioned, otherwise the UPDATE is in an EXPLICIT tran with the INSERT.
SunilA has a great set of posts out on the SQL Server Storage Engine blog about managing TEMPDB. It's
Akelly: thanks. I forgot to put the commit...I updated the blog. Thanks for your interest and feedback.
Just okay ...