Performance Tuning UPDATE Statement

Below is a simple UPDATE that I have to perform on a table that has
about 2.5 million rows (about 4 million in production) This query
runs for an enourmous amount of time (over 1 hour). Both the
ChangerRoleID and the ChangerID are indexed (not unique). Is there
any way to performance tune this?

Controlling the physical drive of the log file isn't possible at our
client sites (we don't have control) and the recovery model needs to
be set to "Full".

UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
ChangerRoleID IS NULL

Any Help would be greatly appreciated!On 4 Aug 2004 08:27:50 -0700, MAS wrote:

>Below is a simple UPDATE that I have to perform on a table that has
>about 2.5 million rows (about 4 million in production) This query
>runs for an enourmous amount of time (over 1 hour). Both the
>ChangerRoleID and the ChangerID are indexed (not unique). Is there
>any way to performance tune this?
>Controlling the physical drive of the log file isn't possible at our
>client sites (we don't have control) and the recovery model needs to
>be set to "Full".
>UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
>ChangerRoleID IS NULL
>Any Help would be greatly appreciated!

Hi MAS,

If you remove the non-unique index on ChangerRoleID before doing the
update and recreate it afterwards, you'll probably save some time. The
index could have been useful if only a few of all rows match the IS NULL
condition, but with over aan hour execution time, I think there are so
many matches that a full table scan will be quicker. Removing the index
before doing the update saves SQL Server the extra work of constantly
having to update the index to keep it in sync with the data. Of course,
this might affect other queries that execute during the update and would
have benefited from this index. The index on ChangerID will neither be
used nor cause extra work for this update.

Check if there's a trigger that gets fired by the update. If you can
safely disable that trigger during the update process, do so. Same for
constraints: are there any CHECK or REFERENCES (foreign key) constraints
defined for ChangerRoleID? If so, disable constraint checking (again, only
if it is safe, i.e. you have to be sure that this update won't cause
violation of the constraint *and* that no other person accessing the
database during the time constraint checking is disabled will be able to
cause violations of the constraint).

You state that the recovery model needs to be full; from that I conclude
that you can't lock other users out of the database during the update. Can
you at least take measures to prevent other users from using (updating,
but preferably reading as well) the CLIENTSHISTORY table?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

MAS (mas32677@.hotmail.com) writes:
> Below is a simple UPDATE that I have to perform on a table that has
> about 2.5 million rows (about 4 million in production) This query
> runs for an enourmous amount of time (over 1 hour). Both the
> ChangerRoleID and the ChangerID are indexed (not unique). Is there
> any way to performance tune this?
> Controlling the physical drive of the log file isn't possible at our
> client sites (we don't have control) and the recovery model needs to
> be set to "Full".
> UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHERE
> ChangerRoleID IS NULL
> Any Help would be greatly appreciated!

To add to what Hugo said, if that index on ChangerRoleID is clustered,
and many rows have a NULL value, then you are in for a problem.

It may help to do it batches:

DECLARE @.batch_size int, @.rowc int
SELECT @.batch_size = 50000
SELECT @.rowc = @.batch_size
SET ROWCOUNT @.batch_size
WHILE @.rowc = @.batch_size
BEGIN
UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID
WHERE ChangerRoleID IS NULL
AND ChangerID IS NOT NULL
SELECT @.rowc = @.@.rowcount
END
SET ROWCOUNT 0

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Performance Tuning for Row-by-Row Update Statement

hi

For an unavoidable reason, I have to use row-by-row processing
(update) on a temporary table to update a history table every day.
I have around 60,000 records in temporary table and about 2 million in
the history table.

Could any one please suggest different methods to imporve the runtime
of the query?

Would highly appreciate!Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||Is the row-by-row processing done in a cursor? Must you update exactly one
row at a time (if so, why?) or would it be acceptable to update 2,3 or 50
rows at a time?

You can use SET ROWCOUNT and a loop to fine-tune the batch size of rows to
be updated. Bigger batches should improve performance over updating single
rows.

SET ROWCOUNT 50

WHILE 1=1
BEGIN

UPDATE SomeTable
SET ...
WHERE /* row not already updated */

IF @.@.ROWCOUNT=0
BREAK

END

SET ROWCOUNT 0

--
David Portas
SQL Server MVP
--|||"Muzamil" <muzamil@.hotmail.com> wrote in message
news:5a998f78.0405211023.24b40513@.posting.google.c om...
> hi
> For an unavoidable reason, I have to use row-by-row processing
> (update) on a temporary table to update a history table every day.
> I have around 60,000 records in temporary table and about 2 million in
> the history table.

Not much you can do if you absolutely HAVE to do row-by-row updating.

You might want to post DDL, etc. so others can take a crack at it. I've
seen many times someone will say, "I have to use a cursor", "I have to
update one row at a time" and then someone posts a much better/faster
solution.

Also, how are you handling transactions? Explicitly or implicitely? If
you're doing them implicitely, are you wrapping each update in its own, or
can up batch say 20 updates?

Finally, where's your log files? Separate physical drives?

> Could any one please suggest different methods to imporve the runtime
> of the query?
> Would highly appreciate!|||Hi
Thanks for your reply.

The row-by-row update is mandatory becuase the leagacy system is
sending us the information such as "Add", "Modify" or "delete" and
this information HAS to be processed in the same order otherwise we'll
get the erroneous data.
I know it's a dumb way of doing things but this is what our and their
IT department has chosen to be correct way of action after several
meetings. Hence the batch idea will not work here.

I am not using Cursors, instead I am using the loop based on the
primary key.

The log files are on different drives.

I've also tried using "WITH (ROWLOCK)" in the update statement but
it's not helping much.

Can you please still throw in some idea? Would be great help!

Thanks

"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<tOxrc.234090$M3.65389@.twister.nyroc.rr.com>...
> "Muzamil" <muzamil@.hotmail.com> wrote in message
> news:5a998f78.0405211023.24b40513@.posting.google.c om...
> > hi
> > For an unavoidable reason, I have to use row-by-row processing
> > (update) on a temporary table to update a history table every day.
> > I have around 60,000 records in temporary table and about 2 million in
> > the history table.
> Not much you can do if you absolutely HAVE to do row-by-row updating.
> You might want to post DDL, etc. so others can take a crack at it. I've
> seen many times someone will say, "I have to use a cursor", "I have to
> update one row at a time" and then someone posts a much better/faster
> solution.
> Also, how are you handling transactions? Explicitly or implicitely? If
> you're doing them implicitely, are you wrapping each update in its own, or
> can up batch say 20 updates?
> Finally, where's your log files? Separate physical drives?
>
> > Could any one please suggest different methods to imporve the runtime
> > of the query?
> > Would highly appreciate!|||Muzamil (muzamil@.hotmail.com) writes:
> The row-by-row update is mandatory becuase the leagacy system is
> sending us the information such as "Add", "Modify" or "delete" and
> this information HAS to be processed in the same order otherwise we'll
> get the erroneous data.

Ouch. Life is cruel, sometimes.

I wonder what possibilities there could be to find parallel streams,
that is updates that could be performed independently. Maybe you
can modify 10 rows at a time then. But it does not sound like a very
easy thing to do.

Without knowing the details of the system, it is difficult to give
much advice. But any sort of pre-aggregation you can do, is probably
going to pay back.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Details of the system:
The leagcy system sends us records flagged with "Add", "modify" or
"delete".
The purpose of these flags is self-explnatory. But the fun began when
we noticed that within same file , legacy system sends us "Add" and
then "Modify". Thus, we were left with no other option except to do
row-by-row processing.
We came up with the following logic:

a)If records StatusFlag is A' and records key does not exist in
DataWareHouse's Table, then the record is inserted into
DataWareHouse's Table.

b)If records StatusFlag is A', but records key exists in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable..

c)If records StatusFlag is M' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated.

d)If records StatusFlag is M' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

e)If records StatusFlag is M' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

f)If records StatusFlag is D' and records key exists in
DataWareHouse's Table and record is active, then the corresponding
record in DataWareHouse's Table will be updated as inactive.

g)If records StatusFlag is D' and records key exists in
DataWareHouse's Table but record is inactive, then the record is
marked as invalid and will be inserted into InvalidTable.

h)If records StatusFlag is D' and records key does not exist in
DataWareHouse's Table, then the record is marked as invalid and will
be inserted into InvalidTable.

This logic takes care of ALL the anomalies we were facing before but
at the cost of long processing time.

I await your comments.

Thanks

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F53BF51111Yazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > The row-by-row update is mandatory becuase the leagacy system is
> > sending us the information such as "Add", "Modify" or "delete" and
> > this information HAS to be processed in the same order otherwise we'll
> > get the erroneous data.
> Ouch. Life is cruel, sometimes.
> I wonder what possibilities there could be to find parallel streams,
> that is updates that could be performed independently. Maybe you
> can modify 10 rows at a time then. But it does not sound like a very
> easy thing to do.
> Without knowing the details of the system, it is difficult to give
> much advice. But any sort of pre-aggregation you can do, is probably
> going to pay back.|||Muzamil (muzamil@.hotmail.com) writes:
> Details of the system:
> The leagcy system sends us records flagged with "Add", "modify" or
> "delete".
> The purpose of these flags is self-explnatory. But the fun began when
> we noticed that within same file , legacy system sends us "Add" and
> then "Modify". Thus, we were left with no other option except to do
> row-by-row processing.
> We came up with the following logic:

Hm, you might be missing a few cases. What if you get an Add, and record
exists in DW, but is marked inactive? With your current logic, the
input record moved to the Invalid table.

And could that feediug system be as weird as to send Add, Modify, Delete,
and Add again? Well, for a robust solution this is what we should assume.

It's a tricky problem, and I was about to defer the problem, when I
recalled a solution that colleague did for one of our stored procedures.
The secret word for tonight is bucketing! Assuming that there are
only a couple of input records for each key value, this should be
an excellent solution. You create buckets, so that each bucket has
at most one row per key value. Here is an example on how to do it:

UPDATE inputtbl
SET bucket = (SELECT count(*)
FROM inputtbl b
WHERE a.keyval = b.keyval
AND a.rownumber < b.rownumber) + 1
FROM inputtbl a

input.keyval is the keys for the records in the DW table. Rownumber
is a column which as describes the processing order. I assume that
you have such a column.

So now you can iterate over the buckets, and for each bucket, you can do
set- based processing. You still have to iterate, but instead over 60000
rows, only over a couple of buckets.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I think I was not articulate enough to convey the logic properly.
Anyways, thanks to everyone for your help.
By using the ROWLOCK and proper indexes, I was ale to reduce the time considerably.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94F6821D6ABYazorman@.127.0.0.1>...
> Muzamil (muzamil@.hotmail.com) writes:
> > Details of the system:
> > The leagcy system sends us records flagged with "Add", "modify" or
> > "delete".
> > The purpose of these flags is self-explnatory. But the fun began when
> > we noticed that within same file , legacy system sends us "Add" and
> > then "Modify". Thus, we were left with no other option except to do
> > row-by-row processing.
> > We came up with the following logic:
> Hm, you might be missing a few cases. What if you get an Add, and record
> exists in DW, but is marked inactive? With your current logic, the
> input record moved to the Invalid table.
> And could that feediug system be as weird as to send Add, Modify, Delete,
> and Add again? Well, for a robust solution this is what we should assume.
> It's a tricky problem, and I was about to defer the problem, when I
> recalled a solution that colleague did for one of our stored procedures.
> The secret word for tonight is bucketing! Assuming that there are
> only a couple of input records for each key value, this should be
> an excellent solution. You create buckets, so that each bucket has
> at most one row per key value. Here is an example on how to do it:
> UPDATE inputtbl
> SET bucket = (SELECT count(*)
> FROM inputtbl b
> WHERE a.keyval = b.keyval
> AND a.rownumber < b.rownumber) + 1
> FROM inputtbl a
> input.keyval is the keys for the records in the DW table. Rownumber
> is a column which as describes the processing order. I assume that
> you have such a column.
> So now you can iterate over the buckets, and for each bucket, you can do
> set- based processing. You still have to iterate, but instead over 60000
> rows, only over a couple of buckets.|||Muzamil (muzamil@.hotmail.com) writes:
> I think I was not articulate enough to convey the logic properly.
> Anyways, thanks to everyone for your help. By using the ROWLOCK and
> proper indexes, I was ale to reduce the time considerably.

Good indexes is always useful, and of course for iterative processing
it is even more imperative, since the cost a less-than-optimal plan
is multiplied.

I'm just curious, would my bucketing idea be applicable to your problem?
It should give you even more speed, but if you have good-ebough now, there
is of course no reason to spend more time on it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Performance Q : IN Statement

This may be a "how long's a piece of string"-type question but I'm trying to
get a feel for the performance of the IN statement.
Broadly, we've some software which generates SQL for counting but we've hit
a situation where we could generate either re-engineer the SQL or simply
wrap an existing Select generated into a subselect and counting using an IN.
It's a minor change whereas re-engineering would be much more significant
piece of work
As a rule of thumb - Is IN slow?
I know it's probably a bit vague but I'm just after thoughts or perhaps a
link or 2 discussing this.
Thanks
SimonIN can be slower than using one of the alternatives, either a JOIN or an
EXISTS clause:
You can rewrite:
WHERE column IN (SELECT column FROM ...)
as either
INNER JOIN (SELECT column FROM ...) a
ON t.column = a.column
or
WHERE EXISTS (SELECT NULL FROM ... WHERE a.column = t.column)
You can then test which solution performs the best in your situation.
Note that the inner join will return multiple rows if the values in column
are not unique in the derived table.
Jacco Schalkwijk
SQL Server MVP
"Simon Woods" <simonDELETECAPSjwoods@.hotmaiIl.com> wrote in message
news:eq3oGjECFHA.4028@.TK2MSFTNGP15.phx.gbl...
> This may be a "how long's a piece of string"-type question but I'm trying
> to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've
> hit
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an
> IN.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>|||You're right - it is a bit "how long's a piece of string"!
Something that you may find useful is that positive criteria is much faster
than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT IN
(4,5).
IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it should
actually be quicker!
Key to quick querying with criteria is your indexing.
Hope this helps
Paula
"Simon Woods" wrote:

> This may be a "how long's a piece of string"-type question but I'm trying
to
> get a feel for the performance of the IN statement.
> Broadly, we've some software which generates SQL for counting but we've hi
t
> a situation where we could generate either re-engineer the SQL or simply
> wrap an existing Select generated into a subselect and counting using an I
N.
> It's a minor change whereas re-engineering would be much more significant
> piece of work
> As a rule of thumb - Is IN slow?
> I know it's probably a bit vague but I'm just after thoughts or perhaps a
> link or 2 discussing this.
> Thanks
> Simon
>
>|||Paula,
IN (1,2,3) is exactly the same as =1 OR =2 OR =3. IN is just short hand for
multiple OR statements. You can see that if you create a table with a CHECK
constraint that contains an IN clause. When you generate the script from
Query Analyzer, the IN clause will be changed into multiple OR statements.
Jacco Schalkwijk
SQL Server MVP
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:ECC51DC8-3BB3-45B3-A73B-954F9642D157@.microsoft.com...
> You're right - it is a bit "how long's a piece of string"!
> Something that you may find useful is that positive criteria is much
> faster
> than negative, so IN (1,2,3,6,7,8,9,10) should always be quicker than NOT
> IN
> (4,5).
> IN (1,2,3) should not be any slower than =1 OR =2 OR =3, in fact, it
> should
> actually be quicker!
> Key to quick querying with criteria is your indexing.
> Hope this helps
> Paula
> "Simon Woods" wrote:
>

Performance problems with SQL commands in data flow task

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

Jeff-B wrote:

SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:

UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '

Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.

You should redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update. What you've got now is a new, distinct update command for every row on the update path. This is costly.|||

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

|||

Jeff-B wrote:

Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".

If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?

My suggestion of moving the data to a table was assuming you were doing a parameter-based update query.

Your understanding is correct. You were executing 84,000 updates, and generally there is never a good time to do that. If you need to perform an update in the data flow on all of those rows, it would be best to insert the changes into a separate table, to be used later in a set-based update.|||Thank you for your latest response and your help with this problem.|||

Jeff-B wrote:

Thank you for your latest response and your help with this problem.

Jeff,

According to your post you managed to achieve this with an Execute SQL Task. Am I correct?

If using an Execute SQL Task is an option for you then I would go with that over a data-flow every time. SSIS will almost never be able to perform quicker than a RDBMS engine.

-Jamie

|||

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

|||

Jeff-B wrote:

Jamie,

Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.

Caveat that with the fact that its efficient in certain circumstances - unfortunetely doing updates is one of those scenarios. That's due to the vary nature of updates.

-Jamie

sql

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.. .
>

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.
>

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

Performance problem deleting records

Hi,
I delete from a SQL 2000 db 48.000 records with the following SQL statement
(NO SPO!):
Delete * From myTable where dateTimeField < anyDateTimeValue
The dateTimeField has an index. I do not use transactions. Also there is no
trigger on the table.
I receive an timeout after 30 seconds. The complete delete operation
requires about 5 minutes.
What can I do to speed up the operation?
Thanks
ChristianChristian
> Delete * From myTable where dateTimeField < anyDateTimeValue
It is ACCESS's syntax
What kind of index , is it Clustered on Nonclusterd?
Try
Delete From myTable where dateTimeField >'19000101' and dateTimeField <
anyDateTimeValue
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Delete in small batches:
SET ROWCOUNT 5000
WHILE @.@.ROWCOUNT > 0
BEGIN
DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
END
SET ROWCOUNT 0
If you are on SQL2005 use DELETE TOP 5000 instead.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Andrew
> If you are on SQL2005 use DELETE TOP 5000 instead.
Do you think it is "safe" using TOP without ORDER BY clause? I think we do
not have contol ove what gets deleted.
I got used to use ORDER BY with TOP clause
I'd prefer something liker that
WITH OrdersRN AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
FROM dbo.MyOrders
)
DELETE FROM OrdersRN
WHERE RowNum <= 3;
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
> Delete in small batches:
> SET ROWCOUNT 5000
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> END
> SET ROWCOUNT 0
> If you are on SQL2005 use DELETE TOP 5000 instead.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
>> Hi,
>> I delete from a SQL 2000 db 48.000 records with the following SQL
>> statement
>> (NO SPO!):
>> Delete * From myTable where dateTimeField < anyDateTimeValue
>> The dateTimeField has an index. I do not use transactions. Also there is
>> no
>> trigger on the table.
>> I receive an timeout after 30 seconds. The complete delete operation
>> requires about 5 minutes.
>> What can I do to speed up the operation?
>> Thanks
>> Christian
>|||> I delete from a SQL 2000 db 48.000 records with the following SQL
> statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is
> no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
Verify that a poorly written trigger (or triggers) is not contributing to
the problem.|||On Sep 25, 9:02 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Andrew
> > If you are on SQL2005 use DELETE TOP 5000 instead.
> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
> I'd prefer something liker that
> WITH OrdersRN AS
> (
> SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
> FROM dbo.MyOrders
> )
> DELETE FROM OrdersRN
> WHERE RowNum <= 3;
> "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com> wrote in messagenews:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
> > Delete in small batches:
> > SET ROWCOUNT 5000
> > WHILE @.@.ROWCOUNT > 0
> > BEGIN
> > DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> > END
> > SET ROWCOUNT 0
> > If you are on SQL2005 use DELETE TOP 5000 instead.
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> > "Christian Havel" <ChristianHa...@.discussions.microsoft.com> wrote in
> > messagenews:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> >> Hi,
> >> I delete from a SQL 2000 db 48.000 records with the following SQL
> >> statement
> >> (NO SPO!):
> >> Delete * From myTable where dateTimeField < anyDateTimeValue
> >> The dateTimeField has an index. I do not use transactions. Also there is
> >> no
> >> trigger on the table.
> >> I receive an timeout after 30 seconds. The complete delete operation
> >> requires about 5 minutes.
> >> What can I do to speed up the operation?
> >> Thanks
> >> Christian
Hi Uri,
Why do want to control which rows get deleted? At the end of the day
all the rows that match the WHERE clause will gone, as in:
create table #t(i int)
insert #t values(1)
insert #t values(2)
GO
delete top (1) from #t where i > 0
delete top (1) from #t where i > 0
I think Andrew's approach is perfectly safe. Pls correct me if I am
wrong.|||> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
The WHERE clause will determine what gets deleted in this case not the order
by since we want all of them that meet the where clause to go. It may be
beneficial to use an ORDER BY if it helps to find the 5000 rows faster but
it may not. I haven't seen the actual DDL so they should try it both ways
to see. But either way it is safe as it will only delete what the WHERE
clause says to delete.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2301lZ23$HHA.4844@.TK2MSFTNGP02.phx.gbl...
> Andrew
>> If you are on SQL2005 use DELETE TOP 5000 instead.
> Do you think it is "safe" using TOP without ORDER BY clause? I think we do
> not have contol ove what gets deleted.
> I got used to use ORDER BY with TOP clause
> I'd prefer something liker that
> WITH OrdersRN AS
> (
> SELECT *, ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
> FROM dbo.MyOrders
> )
> DELETE FROM OrdersRN
> WHERE RowNum <= 3;
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O%23QZyp3$HHA.4956@.TK2MSFTNGP06.phx.gbl...
>> Delete in small batches:
>> SET ROWCOUNT 5000
>> WHILE @.@.ROWCOUNT > 0
>> BEGIN
>> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
>> END
>> SET ROWCOUNT 0
>> If you are on SQL2005 use DELETE TOP 5000 instead.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
>> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
>> Hi,
>> I delete from a SQL 2000 db 48.000 records with the following SQL
>> statement
>> (NO SPO!):
>> Delete * From myTable where dateTimeField < anyDateTimeValue
>> The dateTimeField has an index. I do not use transactions. Also there is
>> no
>> trigger on the table.
>> I receive an timeout after 30 seconds. The complete delete operation
>> requires about 5 minutes.
>> What can I do to speed up the operation?
>> Thanks
>> Christian
>|||Christian,
Such a query might take a long time if:
- the table is heavily used during the delete (which causes blocking)
- the table has many indexes (the rows need to be deleted from the
indexes too)
- the index on dateTimeField is nonclustered, and myTable has many rows
- there are tables referencing myTable and their foreign key is not
indexed
- there are tables referencing myTable and the foreign key is defined
with ON DELETE CASCADE
If you are deleting a small percentage of the table, then you could
delete in batches (as suggested by others). If you are deleting a large
percentage of the table, you could consider dropping all nonclustered
indexes, execute the delete and then recreate all dropped indexes.
If you have tables with a foreign key constraint to myTable, then make
sure these foreign keys are indexed.
HTH,
Gert-Jan
Christian Havel wrote:
> Hi,
> I delete from a SQL 2000 db 48.000 records with the following SQL statement
> (NO SPO!):
> Delete * From myTable where dateTimeField < anyDateTimeValue
> The dateTimeField has an index. I do not use transactions. Also there is no
> trigger on the table.
> I receive an timeout after 30 seconds. The complete delete operation
> requires about 5 minutes.
> What can I do to speed up the operation?
> Thanks
> Christian|||Why not just increase the timeout threshold? Deleting in smaller batches may
not speed up 'the operation'.
Linchi
"Andrew J. Kelly" wrote:
> Delete in small batches:
> SET ROWCOUNT 5000
> WHILE @.@.ROWCOUNT > 0
> BEGIN
> DELETE FROM MyTable WHERE dateTimeField < anyDateTimeValue
> END
> SET ROWCOUNT 0
> If you are on SQL2005 use DELETE TOP 5000 instead.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Christian Havel" <ChristianHavel@.discussions.microsoft.com> wrote in
> message news:89BD70AD-1FD4-488B-9608-6C351A0557FC@.microsoft.com...
> > Hi,
> >
> > I delete from a SQL 2000 db 48.000 records with the following SQL
> > statement
> > (NO SPO!):
> >
> > Delete * From myTable where dateTimeField < anyDateTimeValue
> >
> > The dateTimeField has an index. I do not use transactions. Also there is
> > no
> > trigger on the table.
> > I receive an timeout after 30 seconds. The complete delete operation
> > requires about 5 minutes.
> > What can I do to speed up the operation?
> >
> > Thanks
> > Christian
>