Troubleshooting the SQL Server DTC wait type can be a frustrating undertaking because this wait type is not on the local system. When utilizing Microsoft Distributed Transaction Coordinator (MS-DTC), a single transaction is opened on multiple systems at the same time and the transaction cannot be concluded until it has been completed on all of the systems.
When getting a report of SQL Server wait type DTC, you'll need to dig into the process information to determine if you can find any other information about the transaction that is open.
Depending on which system started the transaction, you might see data about the transaction in the waitresource column of the sys.sysprocesses dynamic management view or the sys.dm_exec_requests dynamic management view.
You may also be able to find information about the current distributed transactions within the SQL Server Component Services MMC snap-in. Follow the path below and you'll see information about the distributed transactions currently running.
Console Root > Component Services > Computers > My Computer > Distributed Transaction Coordinator > Local DTC > Transaction List
(This is the path for Windows Server 2008 R2 and Windows 7; older OSs may have different paths to this information.)
Confio Ignite for SQL Server
Confio Ignite for SQL Server helps identify the impact of the DTC wait type on SQL Server database instances. Ignite for SQL Server helps DBAs maintain performance and availability within the SQL Server database. Ignite for SQL Server is the one SQL Server performance monitoring solution that identifies, analyzes and resolves all of the performance problems affecting applications and users depending on a Microsoft SQL Server instance.
Follow Us