To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them, and post your findings.
Environment
I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled.
Test data
We are using the same test data as in my previous post:
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 uses XACT_ABORT ON to ensure atomicity of the transaction:
CREATE PROCEDURE dbo.Toggle10kTimesWithXactAbortOn
AS
BEGIN;
SET NOCOUNT ON ;
SET XACT_ABORT ON ;
DECLARE @i INT ;
SET @i = 0 ;
WHILE @i < 10000
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 ;
The second procedure uses a ROLLBACK in a CATCH block:
CREATE PROCEDURE dbo.Toggle10kTimesWithTryCatch
AS
BEGIN;
SET NOCOUNT ON ;
DECLARE @i INT ;
SET @i = 0 ;
WHILE @i < 10000
BEGIN ;
BEGIN TRY ;
BEGIN TRAN ;
UPDATE dbo.Toggle1
SET i = 1 - i
WHERE id = 1 ;
UPDATE dbo.Toggle2
SET i = 1 - i
WHERE id = 1 ;
COMMIT ;
END TRY
BEGIN CATCH ;
ROLLBACK ;
END CATCH ;
SET @i = @i + 1 ;
END ;
END ;
Benchmarking
Let us run these two procedures several times:
EXEC dbo.Toggle10kTimesWithXactAbortOn ;
GO
EXEC dbo.Toggle10kTimesWithTryCatch ;
Typical results are as follows:
dbo.Toggle10kTimesWithXactAbortOn
CPU: 265-280
dbo.Toggle10kTimesWithTryCatch
CPU: 406-468
Conclusion
As we have seen, sometimes using XACT_ABORT ON may use less CPU as opposed to using TRY...CATCH.
I am and not making any blanket statements here - you are encouraged to run your own benchmarks and see if you are observing this effect in your situation. If you do so, can you do us all a favor and post your findings.