TempDB Management in SQLServer (a version storage zone example)

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.