• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1051
  • Last Modified:

How expensive is it to enable MSDTC or is there a way to measure the cost?

We want to enable MSDTC for certain reasons, but we are a little bit worried about the performance.  I'm just wondering if there is a way we can measure the cost?  Thanks.
0
thomaszhwang
Asked:
thomaszhwang
  • 4
  • 4
5 Solutions
 
lcohanDatabase AnalystCommented:
I think that depends directly on your distributed transactions and here's how to monitor it:

"Monitor MS DTC Performance"
http://technet.microsoft.com/en-us/library/cc732972(v=ws.10).aspx
0
 
thomaszhwangAuthor Commented:
Actually we only need to coordinate with one SQL Server.  We are trying to enable it for SSIS.
0
 
lcohanDatabase AnalystCommented:
And please see below more details and very good analysis from MSDTC point of view as it is almost imposible to give you an estimate about the cost - everybody knows it is costly but by hou much...best is to use article I posted above and measure it in a stress test environment close to your real:

http://stackoverflow.com/questions/1564454/confusion-about-transactions-and-msdtc
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
lcohanDatabase AnalystCommented:
Do you know what in the SSIS requires it? Like what type of SSIS task you have that is failing if MSDTC is not enabled?
0
 
thomaszhwangAuthor Commented:
If I want to use the TransactionOption property for components that have this option, I will need MSDTC enabled.

Specially, I want one of my data flow tasks to either commit all or commit nothing.
0
 
lcohanDatabase AnalystCommented:
In that case as "The built-in transaction support in SSIS makes use of the Distributed Transaction Coordinator (MSDTC) service which must be running. " I your only option may be to either use explicit transactions in that step (which I would NOT recommend) OR to try and use TRY/CATCH for that matter before enabling MSDTC.


http://msdn.microsoft.com/en-us/library/ms175976.aspx
0
 
thomaszhwangAuthor Commented:
SSIS doesn't work like stored procedure.  You cannot simply apply a TRY/CATCH to it.
0
 
Scott PletcherSenior DBACommented:
You really can't worry too much about the overhead ... because if your task requires MSDTC, you simply must have it running, or risk a whole lot of other issues.
0
 
thomaszhwangAuthor Commented:
Thanks.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now