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 ...

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 ...