Performance problems when running through Com+ and DTC

We have a performance problem when running a relatively heavy INSERT
statement from a COM+ application against SQL Server 2005 (SP1). The query
takes up all CPU resources (4 CPUs) on the database server while processing
(about 15 minutes) and the database server does not respond to other
queries. The general response from the database server computer is also
poor, including its desktop and other user interactions.

When running the same statement from Managerment Studio, it takes about same
time to complete, but it only takes up 1 CPU and other queries can run at
the same time.

This happens only for some queries. A minor change to the SELECT-part of the
query may make the problem go away.

The SQL Server database is a clustered 64 bit installation. The SQL Server
has SP1 installed, but not SP2. Is it likely that this issue is fixed in
SP2?

More information: we are running in a DTC transaction from COM+. The database are using the read committed snapshot option.

Thanks in advance.

You should probably post your table schema (including indexes), and the query you are trying to run. As I recall, COM+ uses a fairly high transaction isolation level by default, so you might be seeing more locking/blocking because of that.

You might try adding OPTION (MAXDOP1) to the end of the query, and see if the query only uses one CPU.

sql