February 2012 - Posts

• 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 Read More... Posted Wednesday, February 08, 2012 1:09 PM by Alexander Kuznetsov | 10 Comments Filed under: Transact SQL, Transactions

• 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, Read More... Posted Wednesday, February 01, 2012 5:25 PM by Alexander Kuznetsov | 6 Comments Filed under: Concurrency, Data Integrity, Transact SQL, Transactions

• 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 Read More... Posted Wednesday, February 01, 2012 5:02 PM by Alexander Kuznetsov | 7 Comments Filed under: Concurrency, Data Integrity, Transact SQL