Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-28
9
Medium Priority
?
1,041 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 1200 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 40

Assisted Solution

by:lcohan
lcohan earned 1200 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1200 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
 

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 40

Assisted Solution

by:lcohan
lcohan earned 1200 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 300 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

636 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