Post reply

Row Level Versioning

  • 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!

  • April 6, 2010 at 6:53 am

    #1146444

    No one?

  • April 6, 2010 at 7:23 am

    #1146472

    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;-)

  • April 6, 2010 at 7:36 am

    #1146487

    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

  • April 7, 2010 at 1:31 am

    #1147090

    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

  • April 7, 2010 at 7:44 am

    #1147333

    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

  • April 7, 2010 at 5:54 pm

    #1147918

    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

  • April 20, 2010 at 12:24 pm

    #1154855

    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.

  • April 21, 2010 at 12:23 am

    #1155118

    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;-)

  • April 21, 2010 at 12:56 am

    #1155127

    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

  • April 22, 2010 at 6:55 am

    #1156060

    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

  • April 22, 2010 at 7:14 am

    #1156081

    Managing Long-Running Transactions

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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