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.

image

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.

image

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 (3)

  1. sql user says:

    Isnt 2PC to guarantee success across machines

    DTC needs to be redone for DMZ and Azure anyway

  2. RDORR says:

    2PC will make sure either all is committed or aborted properly.

  3. sql user says:

    We know, we just would like it to work to/from DMZ machines, to Azure and not on the RPC universal endpoint port of 135. which is used by many other MS services including old file shares, typically blocked on dmz firewalls

Skip to main content