Avoiding nested transactions might not improve performance.

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern:

BEGIN TRAN ;
-- (snip)
COMMIT ;

or a more complex one:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF @trancount = 0 BEGIN ;
   BEGIN TRAN ;
END ;
--(snip)
IF @trancount = 0 BEGIN ;
   COMMIT ;
END ;
 

the performance stays the same: I was not able to notice any difference. Here are my benchmarks.

Prerequisites

All we need is the following two tables:

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

Simple benchmarking

First, let us run a very simple case, without nested stored procedure calls:

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

CREATE PROCEDURE dbo.ToggleAvoidingNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                DECLARE @trancount INT ;
                SET @trancount = @@TRANCOUNT ;
                IF @trancount = 0 BEGIN ;
                   BEGIN TRAN ;
               END ;
                UPDATE  dbo.Toggle2
                SET     i = 1 - i
                WHERE   id = 1 ;
                IF @trancount = 0 BEGIN ;
                   COMMIT ;
               END ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO


CREATE PROCEDURE dbo.ToggleNoNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            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 ;
GO
EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;
GO

EXEC dbo.ToggleWithNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleAvoidingNestedTrans @NumIterations = 100000;

GO

EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO
EXEC dbo.ToggleNoNestedTrans @NumIterations = 100000;
GO  
 

In fact, real execution costs are the same for both approaches, and not different from then run without nested transactions at all.

Benchmarking with nested stored procedure calls

Let us benchmark another, possibly more realistic scenario, when the avoiding nested transaction logic is inside another, nested stored procedure. Here is the benchmarking script:

CREATE PROCEDURE dbo.Toggle2WithNestedTran
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        BEGIN TRAN ;
        UPDATE  dbo.Toggle2
        SET     i = 1 - i
        WHERE   id = 1 ;
        COMMIT ;
    END ;
GO

ALTER PROCEDURE dbo.ToggleWithNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                EXEC dbo.Toggle2WithNestedTran ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

CREATE PROCEDURE dbo.Toggle2AvoidingNestedTrans
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @trancount INT ;
        SET @trancount = @@TRANCOUNT ;
        IF @trancount = 0 BEGIN ;
           BEGIN TRAN ;
       END ;
        UPDATE  dbo.Toggle2
        SET     i = 1 - i
        WHERE   id = 1 ;
        IF @trancount = 0 BEGIN ;
           COMMIT ;
       END ;
    END ;
GO

ALTER PROCEDURE dbo.ToggleAvoidingNestedTrans
   @NumIterations INT
AS
    BEGIN;
        SET NOCOUNT ON ;
        SET XACT_ABORT ON ;
        DECLARE @i INT ;
        SET @i = 0 ;
        WHILE @i < @NumIterations
            BEGIN ;
                BEGIN TRAN ;
                UPDATE  dbo.Toggle1
                SET     i = 1 - i
                WHERE   id = 1 ;
                EXEC dbo.Toggle2AvoidingNestedTrans ;
                COMMIT ;
                SET @i = @i + 1 ;
            END ;
    END ;
GO

Again, I was not able to notice any difference.

Conclusion

Apparently the following logic does not affect performance at all:

DECLARE @trancount INT ;
SET @trancount = @@TRANCOUNT ;
IF @TRANCOUNT = 0 BEGIN ;
   BEGIN TRAN ;
END ;
--(snip)
IF @TRANCOUNT = 0 BEGIN ;
   COMMIT ;
END ;

As such, this pattern seems to be just an unnecessary complication.

What do you think - are you getting the same results when you benchmark on your system?

Are you using this pattern for other than performance? What are those?