Alexander Kuznetsov : Transactions, Transact SQL

Avoiding nested transactions might not improve performance.

Alexander Kuznetsov — Wed, 08 Feb 2012 18:09:00 GMT

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...(read more)

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

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

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,...(read more)