Performance problems when running trhough Com+ and DTC

You didn't post the statement so it is hard to say but you can either set
the MAXDOP at the server level or specify a hint inthe query to limit the
number of CPU's a single action uses.
Andrew J. Kelly SQL MVP
"Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
news:OIKIRdUbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Hi everyone,
> 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.
>
> Thanks in advance.
>
>
The number of threads used are always determined at run time based on a
number of factors. So even if one time it uses all the procs it can easily
use just one the next time around. But in this case I feel it is related to
how it is being called and something called parameter sniffing. You can get
two very different plans if they are not called identically and evaluate to
the same datatypes etc. Again it would help to see the real statement.
Andrew J. Kelly SQL MVP
"Anders Evensen" <anders.evensen@.millionhandshakes.com> wrote in message
news:efOOB7UbHHA.1508@.TK2MSFTNGP06.phx.gbl...
> Thanks. We will try this.
> However, I am very interesting in knowing if there is a logical
> explanation to why SQL Server processes uses totally different CPU
> resources when running the statement from COM+ in a DTC transaction
> compared to running it from Management Studio.
> -Anders
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>
|||On Fri, 23 Mar 2007 14:25:37 +0100, "Anders Evensen"
<anders.evensen@.millionhandshakes.com> wrote:

>Thanks. We will try this.
>However, I am very interesting in knowing if there is a logical explanation
>to why SQL Server processes uses totally different CPU resources when
>running the statement from COM+ in a DTC transaction compared to running it
>from Management Studio.
I believe COM+ often sets isolation level to repeatable read, which
could explain the situation - management studio doesn't do that.
J.

>-Anders
>"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>news:%23U4xx0UbHHA.4012@.TK2MSFTNGP03.phx.gbl...
>
|||Actually I think it used Serializable but am not 100% sure.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:fuj8039981uc8e6ipe83k51r7f3a9092s3@.4ax.com...
> On Fri, 23 Mar 2007 14:25:37 +0100, "Anders Evensen"
> <anders.evensen@.millionhandshakes.com> wrote:
>
> I believe COM+ often sets isolation level to repeatable read, which
> could explain the situation - management studio doesn't do that.
> J.
>
>
|||On Sat, 24 Mar 2007 09:40:55 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Yep, it is serializable per default.
Right, ... the point being he can try to replicate the behavior by
setting the isolation mode in management studio, I meant to point that
out too.
J.
|||Thanks. We are actually using read committed as the isolation level from
COM+, and the read commitet snapshot option is turned on for the database.
Management Studio is using read committed as well.
-A
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:321b03pup5h45fns2puit4buo90655cqso@.4ax.com...
> On Sat, 24 Mar 2007 09:40:55 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Right, ... the point being he can try to replicate the behavior by
> setting the isolation mode in management studio, I meant to point that
> out too.
> J.
>
|||On Sun, 25 Mar 2007 13:34:43 +0200, "Anders Evensen"
<anders.evensen@.millionhandshakes.com> wrote:

>Thanks. We are actually using read committed as the isolation level from
>COM+, and the read commitet snapshot option is turned on for the database.
>Management Studio is using read committed as well.
Then I guess I would ask, are you *sure* that when you run it through
COM+, nothing else is executing? You're running an INSERT statement,
does COM+ get the exact string you use in the MS or does it do a
prepared statement or somesuch? Have you run profiler to be clear on
this?
The COM+ connections might also prep with other random settings that
could be factors. Do they return exactly the same results either way?
You could use profiler to display the plans from executing from either
side, it wouldn't tell you *why* exactly, but it might give more
hints.
J.

>-A
>"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
>news:321b03pup5h45fns2puit4buo90655cqso@.4ax.com.. .
>