Row Level Versioning

Question

Post reply

Row Level Versioning

rafa.aborges

Default port

Points: 1430
More actions
April 5, 2010 at 2:32 pm

#142431

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!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

rafa.aborges Default port Points: 1430 More actions · Answer 1

rafa.aborges

Default port

Points: 1430

April 6, 2010 at 6:53 am

#1146444

No one?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

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.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 3

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' 🙂

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

karthikeyan-146504 Ten Centuries Points: 1055 More actions · Answer 4

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

rafa.aborges Default port Points: 1430 More actions · Answer 5

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

Paul White SSC Guru Points: 150467 More actions · Answer 6

rafa.aborges (4/7/2010)
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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rafa.aborges Default port Points: 1430 More actions · Answer 7

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.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

rafa.aborges (4/20/2010)
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.

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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 9

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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

rafa.aborges Default port Points: 1430 More actions · Answer 10

Hi Paul, thanks for the reply.

Answering to your questions, the transaction log of tempdb is set on a different physical device as the tempdb data and both are set to autogrow. Only the tempdb transaction log disk filled all the available space. I am not sure what is really happening and I'm not comfortable in adding more space to the disk without knowing what's going on. I'm already monitoring through perfmon all the version stored in tempdb too.

What might be your suggestion to this case? If it's a long running transaction that is causing this issue, what's the best approach to solve this?

Thanks

Paul White SSC Guru Points: 150467 More actions · Answer 11

Managing Long-Running Transactions

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi