Does rowversion/timestamp affects performance significantly?

Question

I plan to add to most tables in my DB rowversion to track changes in those tables. I know that adding it will affect performance of queries.

Does anyone knows if it affect performance a little bit (few percent slower) or I should not to add rowversion to many tables, because it make DB much slower.

Answer 1

Performance difference of just adding a rowversion/timestamp column is that your rows are now 8 bytes wider.

The actual performance difference comes when you start actually using them for something. But as I point out in my answer to a similar question: RowVersion and Performance

If you're not going use the rowVersion field for checking for updated items and instead you're going to use it for consistency to ensure that the record isn't updated since you last read, then this is going to be a perfectly acceptable use and will not impact.

Such As:

UPDATE MyTable SET MyField = ' @myField
WHERE Key = @key AND rowVersion = @rowVersion

So performance when just checking the row to make sure that it's not been updated since the application last read is going to be trivial performance difference (it has to read the row to update it anyway).

But performance when trying to use a rowversion/timestamp column as a means to get all updated items since last time we checked is going to be very poor.

Answer 2

Of interest to me, the versioning we use is populated by trigger, so when I saw this I had to learn more about the performance. So, I set about setting up a test scenario. I wanted to compare what we currently use (trigger) versus a rowversion column versus another table with no versioning.

Not surprisingly, the trigger definitely performed worse than the rowversion. Rowversion was essentially identical to the update time on a table with no versioning; Some runs show the table with no versioning is faster, but about an equal # show rowversion to be faster. To me this means that there is so little overhead in using it, that random CPU and disk I/O hide the real performance difference.

SET NOCOUNT ON
GO

CREATE TABLE _TEST_BaseTest(myKey bigint PRIMARY KEY,myValue bigint,UselessColumn bigint)
CREATE TABLE _TEST_RowVersionTest(myKey bigint PRIMARY KEY,myValue bigint, RV rowversion)
CREATE TABLE _TEST_ModifiedVersionTest(myKey bigint PRIMARY KEY,myValue bigint, MV bigint)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo._TEST_ModifiedVersionTest_Trigger
   ON  dbo._TEST_ModifiedVersionTest
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    UPDATE tbl
    SET tbl.MV=tbl.MV+1
    FROM _TEST_ModifiedVersionTest tbl
    INNER JOIN inserted i on i.myKey=tbl.myKey
END
GO

INSERT INTO _TEST_BaseTest (myKey,myValue)
    SELECT TOP 50000 <FIELD1>,<FIELD2> FROM <SOME_TABLE>

INSERT INTO _TEST_RowVersionTest (myKey,myValue)
    SELECT myKey,myValue
    FROM _TEST_BaseTest

INSERT INTO _TEST_ModifiedVersionTest (myKey,myValue,MV)
    SELECT myKey,myValue,1
    FROM _TEST_BaseTest

DECLARE     @StartTimeBase DATETIME, @EndTimeBase DATETIME
        ,   @StartTimeRV DATETIME, @EndTimeRV DATETIME
        ,   @StartTimeMV DATETIME, @EndTimeMV DATETIME
        ,   @CNT INT=0, @Iterations INT=25

--BASE
    SET @StartTimeBase=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeBase=GETDATE()

--RV
    SET @StartTimeRV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_RowVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeRV=GETDATE()

--MV
    SET @StartTimeMV=GETDATE()
    SET @CNT=1
    WHILE @CNT<=@Iterations
    BEGIN
        UPDATE _TEST_ModifiedVersionTest SET myValue=myValue

        SET @CNT=@CNT+1
    END
    SET @EndTimeMV=GETDATE()

DECLARE @Rows INT
SELECT @Rows=COUNT(*) FROM _TEST_BaseTest

PRINT CONVERT(VARCHAR,@Rows) + ' rows updated ' + CONVERT(VARCHAR,@Iterations) + ' time(s)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeBase,@EndTimeBase)) + ' Base Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeRV,@EndTimeRV))     + ' Rv Time Elapsed (ms)'
PRINT CONVERT(VARCHAR,DATEDIFF(MS,@StartTimeMV,@EndTimeMV))     + ' Mv Time Elapsed (ms)'

drop TABLE _TEST_BaseTest
drop TABLE _TEST_RowVersionTest
drop table _TEST_ModifiedVersionTest