Row Level Versioning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Row Level Versioning

12 posts, Page 1 of 2

1 2 »»

Row Level Versioning

Rate Topic

Display Mode

Topic Options

Author

Message

rafa.aborges

Posted Monday, April 05, 2010 2:32 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 PM
Points: 40, Visits: 218

Hey,

We're using SQL Server 2005 in our company and we have an application that may benefit by using the row level versioning.

My question is if it may cause an overhead that I'm not expecting or if it have bugs or issues.

I know that it adds 14 bytes to each row, that I need to monitor the space in tempdb and that some DDLs operations on my database may have the performance affected, but is there any extra cost? Something that I should be aware of?

Another question: does anybody know why Microsoft doesn't make this feature enabled by default?

Thanks in advance!

Post #897023

rafa.aborges

Posted Tuesday, April 06, 2010 6:53 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 PM
Points: 40, Visits: 218

No one?

Post #897501

Bhuvnesh

Posted Tuesday, April 06, 2010 7:23 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

rafa.aborges (4/5/2010)

is there any extra cost? Something that I should be aware of?

* Increases resource usage when modifying data since row versions are maintained in tempDB.
* Update and Delete transaction will use more resource since it has to create a snapshot
in the tempDB. This could cause higher IO, CPU and Memory usage.
* TempDB must have enough space to handle all the additional requirements.
* If there are long version chains then Data Read performance will be affected. [size="2"][/size]

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #897530

Paul White

Posted Tuesday, April 06, 2010 7:36 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

rafa.aborges (4/5/2010)

Another question: does anybody know why Microsoft doesn't make this feature enabled by default?

I read that the reason is the 'principle of least surprise' Smile

By not changing default behaviour, this principle avoids unexpected changes in behaviour, and allows people to learn about new facilities (like SQLCLR) before choosing to enable them.
All very sensible, I think.

For a full list of limitations and considerations, see:
http://msdn.microsoft.com/en-us/library/ms179599.aspx
http://msdn.microsoft.com/en-us/library/ms175492.aspx
http://msdn.microsoft.com/en-us/library/ms188277.aspx
http://msdn.microsoft.com/en-us/library/ms175095.aspx

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #897546

karthikeyan-146504

Posted Wednesday, April 07, 2010 1:31 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, April 08, 2010 11:35 PM
Points: 71, Visits: 196

Hi ,

1. keep tempdb database in separate drive
2. use performance monitor to monitor the tempdb size and activities
3. use the following DMV to monitor the tempdb.

sys.dm_db_file_space_usage
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage

Thanks

Kindest Regards,

karthik

Post #898239

rafa.aborges

Posted Wednesday, April 07, 2010 7:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 PM
Points: 40, Visits: 218

Thanks, guys! Great advices!

Another question: Do you know if I might have issues to set the row level versioning on even if the compatibility level is set to 80 (SQL Server 2000) on a SQL Server 2005 binaries?

Thanks

Post #898513

Paul White

Posted Wednesday, April 07, 2010 5:54 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

rafa.aborges (4/7/2010)

Row versioning is always available in 2005, regardless of the compatibility level.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #899153

rafa.aborges

Posted Tuesday, April 20, 2010 12:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:34 PM
Points: 40, Visits: 218

We turned on the row level versioning on our database. It's on for almost 15 days. But this morning the tempdb transaction log got full.

My question is if the overhead on the tempdb database is on its transaction log only not in the whole database.

Because we didn't see much change on the database itself, it's almost at the same size that were before we turned on the row versioning, but the transaction log increased absurdly until the transaction log disk got full.

Thanks in advance.

Post #907154

Bhuvnesh

Posted Wednesday, April 21, 2010 12:23 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

rafa.aborges (4/20/2010)

For this new functionality, there will be a cost as well. Let us look at the cost to conclude.

1 Increases resource usage when modifying data since row versions are maintained in tempDB.
2 Update and Delete transaction will use more resource since it has to create a snapshot in the tempDB. This could cause higher IO, CPU and Memory usage.
3 TempDB must have enough space to handle all the additional requirements.
4 14 Bytes will be added to the row in the database to keep track of the versions.
5 If there are long version chains then Data Read performance will be affected.

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #907464

Paul White

Posted Wednesday, April 21, 2010 12:56 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 10,990, Visits: 10,578

rafa.aborges (4/20/2010)

My question is if the overhead on the tempdb database is on its transaction log only not in the whole database.

Row versioning does not directly consume additional tempdb log space.
Additionally, when tempdb runs out of space, SQL Server attempts to shrink the version store.
Is your transaction log for tempdb stored on the same physical device as tempdb data?
Are your tempdb files set to auto-grow?
Did tempdb fill the available disk space?

Because we didn't see much change on the database itself, it's almost at the same size that were before we turned on the row versioning, but the transaction log increased absurdly until the transaction log disk got full.

SQL Server provides a number of ways to keep track of the version store in tempdb, including the sys.dm_tran_version_store DMV. You should follow the normal process to determine why the transaction log for tempdb grew so large. Typically, it will be due to a long-running user transaction.

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #907477

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions