Do I really need to use DTC Transactions?

It is sometimes common practice to enable Distributed Transaction (DTC) behavior but it can be unnecessary, and adds unwanted overhead.  

DTC has the ability to determine single phase vs two phase commit requirements.  A DTC transaction involves resource managers (RMs) of which SQL Server can be one of them.  If a single resource manager is involved in the transaction there is no need to perform 2-phase commit.   DTC shortcuts the activity and performs a single-phase commit safely.   This reduces the communication between the DTC and RM managers.  However, the overhead of the DTC manager is still involved making the transaction slightly slower than a native TSQL transaction.

Single Phase

The following is a single phase DTC commit example.

begin distributed tran
go

update dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

Notice the trace output does not indicate a prepared state.  This is a direct indication of a single phase commit.

Two Phase

The following is a 2-phase commit example.

begin distributed tran
go

update MYREMOTESERVER.dbTest.dbo.tblTest set object_id = 100
go

commit tran
go

The transaction involved the local instance (RM=1) and a remote instance (RM=2).  With 2 RMs involved DTC commits the transaction under full, 2-phase commit protocol.   Notice the prepared state in the trace indicating full, 2-phase commit protocol is being used.

You may want to review the DTC transactions executing on your system, looking for prepared state.  If the DTC transactions running on your system are not using 2-phase commit protocol you should consider removing DTC from the transactions in order to improve performance.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

• Anonymous
  January 27, 2015
  The comment has been removed
• Anonymous
  January 28, 2015
  The comment has been removed
• Anonymous
  January 28, 2015
  The comment has been removed