Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Row Level Versioning Expand / Collapse
Author
Message
Posted Monday, April 5, 2010 2:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:49 PM
Points: 45, Visits: 345
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
Posted Tuesday, April 6, 2010 6:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:49 PM
Points: 45, Visits: 345
No one?
Post #897501
Posted Tuesday, April 6, 2010 7:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #897530
Posted Tuesday, April 6, 2010 7:36 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 9,923, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #897546
Posted Wednesday, April 7, 2010 1:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 8, 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
Posted Wednesday, April 7, 2010 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:49 PM
Points: 45, Visits: 345
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
Posted Wednesday, April 7, 2010 5:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 9,923, Visits: 11,169
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #899153
Posted Tuesday, April 20, 2010 12:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:49 PM
Points: 45, Visits: 345
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
Posted Wednesday, April 21, 2010 12:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #907464
Posted Wednesday, April 21, 2010 12:56 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 12:48 PM
Points: 9,923, Visits: 11,169
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 »

Add to briefcase 12»»

Permissions Expand / Collapse