Let's start with an example to learn about the version storage zone. In this example, the database enables RCSI (with the row version-controlled committed read isolation level) and SI (with the Snapshot isolation level ). When RCSI or SI is enabled for the database, a row version is created whenever the row is updated. This version is saved in version memory until it is not needed. When does SQLServer
Let's start with an example to learn about the version storage zone. In this example, the database enables RCSI (with the row version-controlled committed read isolation level) and SI (with the Snapshot isolation level ). When RCSI or SI is enabled for the database, a row version is created whenever the row is updated. This version is saved in version memory until it is not needed. When will the SQL Server
Let's start with an example to learn about the version storage zone. In this example, enable RCSI (using the row version-controlled committed read isolation level) and SI (Snapshot isolation level ).
When RCSI or SI is enabled for the database, a row version is created whenever the row is updated. This version is saved in version memory until it is not needed. When Will SQL Server no longer save this row version? You may have some related questions. For example, if you do not recycle (delete) the row version, what will happen? Is one version or a group of row versions recycled at a time? Each user table has a different structure. Does SQL Server store the row versions of each table separately? Is version control overhead big?
First, create a database and enable SI/RCSI
Createdatabasesnapshottest
Go
-- Setting databaseforsnapshot based isolations. AS you can
-- See that enabling SI/RCSI is a simple DDL operation.
Alterdatabase snapshottest setread_committed_snapshotON
Go
Alterdatabase snapshottest setallow_snapshot_isolationON
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
Fromsys. databases
Where name = 'snapshottest'
Go
-- Create a table with each row> 1000 bytes
Createtable 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
Insertinto t_version values (@ I, 'Hello ')
Set @ I = @ I + 1
End
Next, you can view the version storage area through DMV. You will find that the number of lines in the version storage area is 0. This is the first conclusion of our experiment. The Insert command will not generate a row version, because there is no previous version at all (there is a special case for inserting a row version, which we will discuss later ). This also means that batch import does not produce row versions.
SelectCOUNT (*) fromsys. dm_tran_version_store
-- Now commit the transaction
Commit
Next, let's update the first 50 rows in the table. Because the row data is updated, SQL Server copies the old version to the version storage area. Therefore, the version storage area will have 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
SelectCOUNT (*) fromsys. dm_tran_version_store
Note that explicit transactions are not used, so this Update statement is an implicit transaction and the only statement in this implicit transaction. At the same time, no concurrent transactions need to use the row version, so these row versions will be treated as spam data processing by SQL Server. If you run the following statement in a few minutes, you can release the current version to be recycled.
-- The following statement returns 0 if run after a minute or so
SelectCOUNT (*) fromsys. dm_tran_version_store
SQL Server determines whether the version is no longer needed based on the transaction status, and then spam the version. In the worst case, if a long transaction is running and the long transaction either creates a row version or needs to be used, the row version cannot be deleted, the version storage area will continue to grow and even use up TempDb space, just as long transactions can use up transaction log space. We will discuss this in detail later.
Like the UPDATE operation, a row version is also created if the row is deleted.