Wrapping related changes in a transaction may use less CPU.

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself.

Environment

I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled.

Test data

 After applying Occum's razor, all we need is two tables with one row in each:

 CREATE TABLE dbo.Toggle1
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle1
        ( id, i )
VALUES  ( 1, 0 ) ;
GO
CREATE TABLE dbo.Toggle2
    (
      id INT NOT NULL
             PRIMARY KEY ,
      i INT NOT NULL
    ) ;
GO
INSERT  INTO dbo.Toggle2
        ( id, i )
VALUES  ( 1, 0 ) ;
 

Benchmarking

The following two stored procedures run the same modifications 10K times. The first one does not use an explicit transaction:

 CREATE PROCEDURE dbo.Toggle50kTimesX3WithoutTransaction
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 50000
            BEGIN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                SET @i = @i + 1 ;
            END ;
    END ;

The second procedure does use an explicit transaction:

CREATE PROCEDURE dbo.Toggle50kTimesX3WithXactAbortOff
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT OFF ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < 50000
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;

Let's run the following script a few times:

EXEC dbo.Toggle50kTimesX3WithoutTransaction ;
GO
EXEC dbo.Toggle50kTimesX3WithXactAbortOff ;
 

Typical CPU and duration is as follows:

 dbo.Toggle50kTimesX3WithoutTransaction

CPU: 1900-2340 Duration:5900

dbo.Toggle50kTimesX3WithXactAbortOff

CPU: 1500-1700 Duration: 3200-3300

Conclusion

As we have seen, wrapping related changes in as transaction may improve performance.By related, I mean changes that should all succeed or all fail if we are ensuring atomicity.

However, we typically need to keep transactions short, so we should not bundle unrelated changes in transactions - that may hurt concurrency.

Also I am being very careful and not making any blanket statements here. I did not research this effect with due diligence. I noticed the effect in one case and came up with a repro that works in one particular case on one server, this is all.

I would really appreciate if you run the repro, tweak it as you see needed, and post the results here. TIA!

In general, we should not assume that larger transactions always mean better performance - the opposite is true in many cases. We should always benchmark the specific case at hand and decide on case by case basis.

Edit: I have simplified the scripts a little bit, and that changed the numbers a little bit.