DTCTransaction Vs. SQLTransaction (Performance)

Can anyone pinpoint the differences between DTCTransaction and
SQLTransaction. I am specifically interested in terms of performance. I am
not sure if there is any performance penalties that you have to pay using
DTCTransaction versus SQLTransaction. If so how much e.g. is there something
like, DTCTransaction will be 2 times slower than SqlTransaction ?
Thanks in Advance. ArifI can't give specific benchmarks, but if you think about a dtc trans
versus a sql trans, you have to realize that a dtc trans has to be
coordinated by the app servers dtc, the sql servers dtc and sql server.
The added layers of complexity would make be tend to believe that the
dtc trans would have more overhead.
Not to mention there are 3 points of failure as opposed to one with sql
server.
Just my 2 cents...sorry I don't have a specific benchmarks.
John|||Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> Can anyone pinpoint the differences between DTCTransaction and
> SQLTransaction. I am specifically interested in terms of performance. I
> am not sure if there is any performance penalties that you have to pay
> using DTCTransaction versus SQLTransaction. If so how much e.g. is there
> something like, DTCTransaction will be 2 times slower than
> SqlTransaction ?
A DTCtransaction is a distributed transaction that you need to use, if
you connect to more than one server, and want the transaction to encompass
all servers. You should not use DTCtransaction if you are connecting to
a single server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland, In one of our applications we are debating over using Service
Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
which basically gives you the leverage to have multiple resource managers
controlled by an external transaction manager e.g. on windows 2000
distributed transaction coordinator (DTC).
From a logical persepctives if we are dealing with only one resource
manager, it's better to use local transaction and use resource manager as
transaction manager too e.g. using ADO.NET transaction etc.
What I am trying to understanding is how much extra penalty you have to pay
by using DTCTransaction Vs. SQLTransaction. I am basically looking for some
statistics or research done by anyone in comparing these two.
"Erland Sommarskog" wrote:

> Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> A DTCtransaction is a distributed transaction that you need to use, if
> you connect to more than one server, and want the transaction to encompass
> all servers. You should not use DTCtransaction if you are connecting to
> a single server.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Arif Khan (ArifKhan@.discussions.microsoft.com) writes:
> Erland, In one of our applications we are debating over using Service
> Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
> which basically gives you the leverage to have multiple resource managers
> controlled by an external transaction manager e.g. on windows 2000
> distributed transaction coordinator (DTC).
> From a logical persepctives if we are dealing with only one resource
> manager, it's better to use local transaction and use resource manager as
> transaction manager too e.g. using ADO.NET transaction etc.
> What I am trying to understanding is how much extra penalty you have to
> pay by using DTCTransaction Vs. SQLTransaction. I am basically looking
> for some statistics or research done by anyone in comparing these two.
I will have to admit that I don't have numbers to present, only a gut
feeling.
Since I'm an SQL Server person, I would probably prefer to handle
transactions by sending down BEGIN TRANSACTION myself, and not involve
a component that I don't really know what it's doing. But that's me.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you use DTC with a single resource manager DTC won't do a two phase
commit so the extra overhead is not all that great unless network roundtrips
to the database are very expensive but as Erland says, controlling your own
transactions in SQL Server is usually a better idea and if you complete
eliminate DTC, you don't have to worry about what happens if it's not
running - one few moving part is generally an advantage. On the other hand,
if you want COM+ to manage transactions for you so you can freely compose
transactional and non-transactional components and nest component calls
without worrying about transaction management, there might be a reason to
use DTC.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Arif Khan" <ArifKhan@.discussions.microsoft.com> wrote in message
news:02C8BD8D-757C-48EB-8D49-95E566DDA5C2@.microsoft.com...
> Erland, In one of our applications we are debating over using Service
> Components(COM+) for transactions. Now as we know COM+ uses DTCTransaction
> which basically gives you the leverage to have multiple resource managers
> controlled by an external transaction manager e.g. on windows 2000
> distributed transaction coordinator (DTC).
> From a logical persepctives if we are dealing with only one resource
> manager, it's better to use local transaction and use resource manager as
> transaction manager too e.g. using ADO.NET transaction etc.
> What I am trying to understanding is how much extra penalty you have to
> pay
> by using DTCTransaction Vs. SQLTransaction. I am basically looking for
> some
> statistics or research done by anyone in comparing these two.
>
> "Erland Sommarskog" wrote:
>