Performance problems when running trhough Com+ and DTC

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.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.
>
>|||You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
example:
SELECT Col1
FROM MyTable
OPTION (MAXDOP 1)
Depending on the particulars, the query might run a bit longer without
parallelism but will keep more CPU resources available to satisfy
concurrent queries. You might also consider changing the 'max degree of
parallelism' config option to less than the number of total processors:
EXEC sp_configure 'max degree of parallelism', 3
RECONFIGURE
GO
> This happens only for some queries. A minor change to the SELECT-part of
> the query may make the problem go away.
Parallel plans can be an indication that query/index tuning is needed.
Examine the execution plans of parallel queries to see if improvement is
possible.
--
Hope this helps.
Dan Guzman
SQL Server 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.
>
>|||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...
> 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.
>>
>|||why are you suggesting 3? I just came across this thread
For optimal performance of multi-processor installations, we recommend that
the MAXDOP setting remain equal to the number of physical processors that are
being used. For example, if the system is configured for two physical
processors and four logical processors, MAXDOP should be set to 2.
Any thoughts
http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
"Dan Guzman" wrote:
> You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
> example:
> SELECT Col1
> FROM MyTable
> OPTION (MAXDOP 1)
> Depending on the particulars, the query might run a bit longer without
> parallelism but will keep more CPU resources available to satisfy
> concurrent queries. You might also consider changing the 'max degree of
> parallelism' config option to less than the number of total processors:
> EXEC sp_configure 'max degree of parallelism', 3
> RECONFIGURE
> GO
> > This happens only for some queries. A minor change to the SELECT-part of
> > the query may make the problem go away.
> Parallel plans can be an indication that query/index tuning is needed.
> Examine the execution plans of parallel queries to see if improvement is
> possible.
> --
> Hope this helps.
> Dan Guzman
> SQL Server 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.
> >
> >
> >
>|||ignore the post above ...sorry wrong thread
"tcs" wrote:
> why are you suggesting 3? I just came across this thread
> For optimal performance of multi-processor installations, we recommend that
> the MAXDOP setting remain equal to the number of physical processors that are
> being used. For example, if the system is configured for two physical
> processors and four logical processors, MAXDOP should be set to 2.
> Any thoughts
>
> http://blogs.msdn.com/sqltips/archive/2005/09/14/466387.aspx
> "Dan Guzman" wrote:
> > You can specify a MAXDOP 1 query hint to prevent a parallel query plan. For
> > example:
> >
> > SELECT Col1
> > FROM MyTable
> > OPTION (MAXDOP 1)
> >
> > Depending on the particulars, the query might run a bit longer without
> > parallelism but will keep more CPU resources available to satisfy
> > concurrent queries. You might also consider changing the 'max degree of
> > parallelism' config option to less than the number of total processors:
> >
> > EXEC sp_configure 'max degree of parallelism', 3
> > RECONFIGURE
> > GO
> >
> > > This happens only for some queries. A minor change to the SELECT-part of
> > > the query may make the problem go away.
> >
> > Parallel plans can be an indication that query/index tuning is needed.
> > Examine the execution plans of parallel queries to see if improvement is
> > possible.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server 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...
>> 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.
>>
>>
>|||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...
>> 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.
>>
>>
>|||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:
>>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...
>> 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.
>>
>>
>>
>|||Yep, it is serializable per default.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23$0H1babHHA.1220@.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:
>>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...
>> 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.
>>
>>
>>
>|||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:
>>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.
>|||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...
>> 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.
>