Slow Transactions with System.Transactions and SQL Server 2000

January 20, 2006
No Comments.

Url: http://blogs.msdn.com/florinlazar/archive/2005/…

Thanks to Bill Booth via the Windows Off Topic Mailing List, I was concerned that the DTC was being used for intra-database transactions with SQL Server 2000.  I was looking at timing and using System.Transactions with SQL 2000 is through the roof slower than using traditional client-side transactions.  Interestingly SQL 2005 doesn’t have this limitation.  Lazar Florin has a great blog post that explains what is happening here (found here).

Short story is that SQL 2000 can’t automatically use the Lightweight Transaction Coordinator inside of System.Transactions (SQL 2005 has promotable transactions and can use it fine).  Luckily it is not too hard to make SQL 2000 behave (as seen in Lazar’s blog entry).  Great find Bill!