DTC Related Wait-types

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT wait_type, waiting_tasks_count

, wait_time_ms, max_wait_time_ms, signal_wait_time_ms

INTO #PreWorkWaitStats

FROM sys.dm_os_wait_stats

where wait_type in ('dtc_state', 'preemptive_transimport', 'preemptive_dtc_enlist')

WAITFOR DELAY '00:12:00'

SELECT wait_type, waiting_tasks_count

, wait_time_ms, max_wait_time_ms, signal_wait_time_ms

INTO #PostWorkWaitStats

FROM sys.dm_os_wait_stats

where wait_type in ('dtc_state', 'preemptive_transimport', 'preemptive_dtc_enlist')

SELECT

p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms

, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)

AS signal_wait_time_ms

, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))

- (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))

AS RealWait

, p2.wait_type

FROM #PreWorkWaitStats p1

RIGHT OUTER JOIN

#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)

WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0

AND p2.wait_type NOT LIKE '%SLEEP%'

AND p2.wait_type != 'WAITFOR' and p2.wait_type in ('dtc_state', 'preemptive_transimport', 'preemptive_dtc_enlist')

ORDER BY RealWait DESC