Solved

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

Posted on 2012-03-28
9
947 Views
Last Modified: 2012-04-13
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
Comment
Question by:thomaszhwang
  • 4
  • 4
9 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 400 total points
ID: 37778945
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
 

Author Comment

by:thomaszhwang
ID: 37778963
Actually we only need to coordinate with one SQL Server.  We are trying to enable it for SSIS.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37778967
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37778977
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:thomaszhwang
ID: 37779044
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 400 total points
ID: 37779123
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
 

Author Comment

by:thomaszhwang
ID: 37779167
SSIS doesn't work like stored procedure.  You cannot simply apply a TRY/CATCH to it.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 37783461
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
 

Author Closing Comment

by:thomaszhwang
ID: 37845656
Thanks.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now