THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Using XACT_ABORT ON may be faster than using TRY...CATCH

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.


Published Wednesday, February 01, 2012 5:25 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Shawn C (aka gbn) said:

I'd use both of course.

- SET XACT_ABORT for force rollback, suppress error 266 and deal with client timeouts (= SQL ABORT)

- TRY/CATCH to log the error

References:

- http://stackoverflow.com/a/2074139/27535

- http://dba.stackexchange.com/a/10929/630

February 2, 2012 3:35 AM
 

Alexander Kuznetsov said:

Shawn,

We are logging errors on the app servers/clients anyway. If we added logging in stored procedures, we'd have to have more code, and we'd have to look for errors in more than one place, which in our case would just slow us down. Of course, there could be other scenarios.

In this post I was concerned only with performance. In the simplest possible case, when both approaches get the job done, which is faster.

BTW, nice to know your name at last ;)

February 2, 2012 10:40 AM
 

AaronBertrand said:

FWIW I prefer to have my logging in the stored procedures. Why? Because different apps use the database, and in a lot of cases stored procedures are also called by jobs are other non-app processes.

February 5, 2012 9:15 AM
 

Alexander Kuznetsov said:

Aaron,

I am not arguing whether we should or should not do logging in stored procedures. I do not have a strong opinion myself, and decide how to log on case by case basis. Some, but not all, of my stored procedures log errors on the server.

However, I do have a strong opinion that we do need to understand the cost of our design choices, especially when we deal with frequently run modules. This is what this post is all about.

February 5, 2012 6:13 PM
 

Marian said:

Hi Alexander, I've made your tests on my laptop and the results are:

--test Toggle10kTimesWithXactAbortOn

SQL Server Execution Times:

CPU time = 1388 ms,  elapsed time = 3625 ms.

SQL Server Execution Times:

CPU time = 1280 ms,  elapsed time = 3955 ms.

SQL Server Execution Times:

CPU time = 1560 ms,  elapsed time = 3847 ms.

--test Toggle10kTimesWithTryCatch

SQL Server Execution Times:

 CPU time = 1747 ms,  elapsed time = 4633 ms.

SQL Server Execution Times:

 CPU time = 1794 ms,  elapsed time = 4266 ms.

SQL Server Execution Times:

 CPU time = 1669 ms,  elapsed time = 3913 ms.

Not such big difference as yours, but still it looks like there are some :). Nice catch. Tested also on 2008 R2.

February 7, 2012 7:15 AM
 

Alexander Kuznetsov said:

Hi Marian,

Thank you for posting the results.

February 7, 2012 9:05 AM
 

Driggers said:

Come visit my site, I tried implementing a bit of search

engine optimisation but it didn't work well though

Will definitely be returning, 'tis a great site!

October 2, 2012 9:14 AM
 

Brownell said:

Nowadays the SEO industry gets a bad rap from the amount

of cowboys and outsourced companies currently set up

Added a post on my Facebook, hope thats okay!

November 5, 2012 5:26 AM
 

KBittner said:

What I think Shawn is trying to say, is the conclusion I came to.

If you only want to rollback, then set xact_abort ON is sufficient for all error cases.

If you want to do anything else, then you need the TRY...CATCH - whether it's logging, custom errors, or some additional SQL for a retry or something.

The reason you might want both, is that IF the user cancels the query (or the app aborts), then the CATCH block will NOT be executed - all processing stops immediately, leading to a potential open transaction.

Thanks for the article!

June 26, 2013 1:32 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement