I came across the following question today:
“sys.dm_os_performance_counters has a counter name called ‘DTC calls’, we have been monitoring this counter on sql servers where MSDTC is set up and linked servers are in use. We find the cntr_value is always 0. Is this counter accurate? Where do I find information about such as when MSDTC is called, and how often, by who, etc…”
Can you please shed more light on this?
By looking into the source code, I could find that this counter basically exposes information about waits on the DTC wait type (one of the many wait types exposed through sys.dm_os_wait_stats on which SQL Server can wait at one point or another.
This DTC wait type is the one type SQL Server waits on while it waits for the Transaction Manager (TM) to call the ITransactionResourceAsync ::CommitRequest method in a two-phase commit or the ITransactionResourceAsync ::PrepareRequest method in a single-phase commit after SQL, the Resource Manager (RM), has called the ITransaction::Commit method on the TM proxy. If the RM decides to call the ITransaction::Abort method on the TM proxy instead, because it has decided to rollback the transaction, then this counters will not be increased either.
Basically, when a Resource Manager lets the Transaction Manager know he wants to abort his part of the distributed transaction, and the TM acknowledges that call, the outcome of the global transaction is already known: it is aborted. For a distributed xact to be committed, all RMs involved must commit their part. While for the same xact to be aborted, having one the RMs aborting their part is sufficient. So, when SQL decides to abort his local part of a distributed transaction, it doesn’t have to wait for the TM to come back with any future notification of the outcome of the transaction which depends on other participants.
On the other hand, when a Resource Manager decides to commit its part of a distributed transaction, it has to wait for the Transaction Manager to let him know of the transaction’s outcome (which depends on whether or not all other participating RMs also commit it or not). All the time SQL spends on that wait is what is expressed through that DTC Calls performance counter.
So, if any of the local transactions initiated in an instance of SQL Server are not enlisted in distributed transactions, or if those transactions are always aborted (rollback), you will never see values higher than zero in any of the four instances for the referred performance counter.
Likewise, waiting_tasks_count for this “DTC” wait type will only be increased every time SQL Server commits a local transaction which is enlisted in a distributed transaction. And, it will not be increased when the local transaction is aborted.