SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row Level Versioning


Row Level Versioning

Author
Message
rafa.aborges
rafa.aborges
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 479
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!
rafa.aborges
rafa.aborges
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 479
No one?
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13120 Visits: 4077
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35194 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
karthikeyan-146504
karthikeyan-146504
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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

rafa.aborges
rafa.aborges
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 479
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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35194 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
rafa.aborges
rafa.aborges
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 479
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
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13120 Visits: 4077
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
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35194 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search