• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1082
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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