Update Takes Long Time to Complete!?

[Karaoke Prince]

Hi There,

I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no configuration changed. Until
yesterday, I re-ran it again and it took me more than 18hrs and still
not yet finished!!!

What's wrong with it? I can ran it successfully before. I have tried
two times but the result was still the same.

My SQL statement is:

update [all_sales] a
set a.accounting_month = b.accounting_month
from date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

An index on [all_sales].sales_date is built successfully.
A composite index on ([date_map].start_date, [date_map].end_date) is
built successfully.

My server config is:

SQL Server 2000 with Service Pack 3
Windows 2000 with Service Pack 4
DELL PowerEdge 6650 Server
DUAL XEON 1900MHz Processors
2G RAM
2G Page File on Drive C
2G Page File on Drive D
DELL Diagnostics on all SCSI harddisks were all PASSED.

Any experts could simly give me a help????

Thanks x 1,000,000,000

[Ray Higdon]

Where is your clustered index? It might help to have it on sales date
(in this scenario, don't know if it fits the business use)

First of all make sure your statistics are up to date, you also want to
make sure the tables are not fragmented by reindexing if needed. (in
case you don't know, rebuilding the clustered index alone will
automatically rebuild all non-clustered indexes on that table)

I would try changing that composite index to two different non-clustered
indexes as the composite index could not be used in the second part of
your where condition. When you have an index on columns (a,b) you can
use the index when you say "where a = N" but the index will not be used
when you say "where b = N"

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Karaoke Prince (ka************@hotmail.com) writes:

I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no configuration changed. Until
yesterday, I re-ran it again and it took me more than 18hrs and still
not yet finished!!!
The first thing to check for is blocking. It might simply be that
some other process is holding a lock on the [all sales] table, preventing
you from continue. Use sp_who to check this. If you see a non-zero value
in the Blk column, this spid is blocking the spid on this line.

The second thing to check for is triggers. A trigger on a 15-million-
row table could be fatal.

Not that these are the most likely reasons, but they are easy to check.
What's wrong with it? I can ran it successfully before. I have tried
two times but the result was still the same.

My SQL statement is:

update [all_sales] a
set a.accounting_month = b.accounting_month
from date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

If that is your SQL, you are not running SQL Server, but some other
product. When I run the above, I get a syntax error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

In lieu of other information, I have to assume that the real query is

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a, date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

I would recommend that you add this condition to this query:

and a.accounting_month <> b.accounting_month

This may or may not improve the query plan, but at least it reduces
the number of rows. (Since you ran this statement a couple of
months ago, I guess that most rows already have the correct
accounting_month.)

Note that the query is such that SQL Server will have to read all rows
in the table. (I assume that all values of [all sales].sales_date
are covered by date_map.)

If there were clustered indexes on a.sales_date and b.start_date,
SQL Server could use a merge join. But this would only give a minor
improvement over the case where there are no indexes at all, in
which case I would expect SQL Server to hash date_map (I guess
this table has < 1000 rows).

Really, what is going on I don't know, but if you have a powerful machine,
SQL Server may be doing some sort of a loop join, invoking parallellism.

You could type the query into Query Analyzer, and then press Ctrl-L to
see the estimated execution plan. If the plan involves loop join, I
would try this:

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a
inner hash join date_map b
on a.sales_date >= b.start_date and a.sales_date < b.end_date
where a.accounting_month <> b.accounting_month

SQL Server will issue a warning when you use join hint, but as long as
the query executes well, that's OK.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[Bjarke Wedemeijer]

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Karaoke Prince (ka************@hotmail.com) writes:

I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no configuration changed. Until
yesterday, I re-ran it again and it took me more than 18hrs and still
not yet finished!!!

The first thing to check for is blocking. It might simply be that
some other process is holding a lock on the [all sales] table, preventing
you from continue. Use sp_who to check this. If you see a non-zero value
in the Blk column, this spid is blocking the spid on this line.

The second thing to check for is triggers. A trigger on a 15-million-
row table could be fatal.

Not that these are the most likely reasons, but they are easy to check.

What's wrong with it? I can ran it successfully before. I have tried
two times but the result was still the same.

My SQL statement is:

update [all_sales] a
set a.accounting_month = b.accounting_month
from date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

If that is your SQL, you are not running SQL Server, but some other
product. When I run the above, I get a syntax error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

In lieu of other information, I have to assume that the real query is

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a, date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

I would recommend that you add this condition to this query:

and a.accounting_month <> b.accounting_month

This may or may not improve the query plan, but at least it reduces
the number of rows. (Since you ran this statement a couple of
months ago, I guess that most rows already have the correct
accounting_month.)

Note that the query is such that SQL Server will have to read all rows
in the table. (I assume that all values of [all sales].sales_date
are covered by date_map.)

If there were clustered indexes on a.sales_date and b.start_date,
SQL Server could use a merge join. But this would only give a minor
improvement over the case where there are no indexes at all, in
which case I would expect SQL Server to hash date_map (I guess
this table has < 1000 rows).

Really, what is going on I don't know, but if you have a powerful machine,
SQL Server may be doing some sort of a loop join, invoking parallellism.

You could type the query into Query Analyzer, and then press Ctrl-L to
see the estimated execution plan. If the plan involves loop join, I
would try this:

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a
inner hash join date_map b
on a.sales_date >= b.start_date and a.sales_date < b.end_date
where a.accounting_month <> b.accounting_month

SQL Server will issue a warning when you use join hint, but as long as
the query executes well, that's OK.

Dear Erland -

these are just guesses..... If queries run for such a long time I
normally
use the stored procedure track_waitstats
see :(www.microsoft.com/australia/resources/ distribution_slides.ppt )

during the long run i sample the waits with the dbcc
sqlperf(waitstats)
and then I know exactly what SQL Server is waiting for, with that
knowledge one quickly can determine what is wrong.
In fact - in the oracle community the wait events have been along a
long
time - for several years this is also possible on SQL Server with the
dbcc sqlperf(waitstats), though on ehas to take into consideration
that
the waitstats on SQL Server are server-wide - one can not
(unfortuanality not yet) trace the wait events on a single user or
process.

For an exact description of waits and DBMS systems I also refer to
http//www.hotsos.com - one can always learn someting (good and bsad)
from oracle:))

[Mario]

Bjarke/Erland,

I did a presentation on this subject (in Lalandia, DK), early October. The
presenation and tools are on www.sqlinternals.com. I also added some tools:
an experimental dll that you can use to collect waitstats per user (and
query them, from an exe or from an xp). Only works on SP3(!)
I also added a little C-program (with source) which looks at the waitstats
of a particular spid. It accesses sqlserver memory directly through
'readprocessmemory', so very low overhead and possible high sample counts
(which you can specify in ms).
The big problem is still documentation on those waitstats: things get more
interesting if you realy know what you are looking at.

--
regards,
Mario

http://www.sqlinternals.com

"Bjarke Wedemeijer" <we********@mail.dk> wrote in message
news:68**************************@posting.google.c om...

Erland Sommarskog <so****@algonet.se> wrote in message

news:<Xn**********************@127.0.0.1>...

[posted and mailed, please reply in news]

Karaoke Prince (ka************@hotmail.com) writes:

I have an update statement to update a field of a table (~15,000,000
records). It took me around 3 hours to finish 2 weeks ago. After that
no one touched the server and no configuration changed. Until
yesterday, I re-ran it again and it took me more than 18hrs and still
not yet finished!!!

The first thing to check for is blocking. It might simply be that
some other process is holding a lock on the [all sales] table, preventing you from continue. Use sp_who to check this. If you see a non-zero value
in the Blk column, this spid is blocking the spid on this line.

The second thing to check for is triggers. A trigger on a 15-million-
row table could be fatal.

Not that these are the most likely reasons, but they are easy to check.

What's wrong with it? I can ran it successfully before. I have tried
two times but the result was still the same.

My SQL statement is:

update [all_sales] a
set a.accounting_month = b.accounting_month
from date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

If that is your SQL, you are not running SQL Server, but some other
product. When I run the above, I get a syntax error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

In lieu of other information, I have to assume that the real query is

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a, date_map b
where a.sales_date >= b.start_date and a.sales_date < b.end_date;

I would recommend that you add this condition to this query:

and a.accounting_month <> b.accounting_month

This may or may not improve the query plan, but at least it reduces
the number of rows. (Since you ran this statement a couple of
months ago, I guess that most rows already have the correct
accounting_month.)

Note that the query is such that SQL Server will have to read all rows
in the table. (I assume that all values of [all sales].sales_date
are covered by date_map.)

If there were clustered indexes on a.sales_date and b.start_date,
SQL Server could use a merge join. But this would only give a minor
improvement over the case where there are no indexes at all, in
which case I would expect SQL Server to hash date_map (I guess
this table has < 1000 rows).

Really, what is going on I don't know, but if you have a powerful machine, SQL Server may be doing some sort of a loop join, invoking parallellism.

You could type the query into Query Analyzer, and then press Ctrl-L to
see the estimated execution plan. If the plan involves loop join, I
would try this:

update [all_sales]
set accounting_month = b.accounting_month
from [all sales] a
inner hash join date_map b
on a.sales_date >= b.start_date and a.sales_date < b.end_date
where a.accounting_month <> b.accounting_month

SQL Server will issue a warning when you use join hint, but as long as
the query executes well, that's OK.

Dear Erland -

these are just guesses..... If queries run for such a long time I
normally
use the stored procedure track_waitstats
see :(www.microsoft.com/australia/resources/ distribution_slides.ppt )

during the long run i sample the waits with the dbcc
sqlperf(waitstats)
and then I know exactly what SQL Server is waiting for, with that
knowledge one quickly can determine what is wrong.
In fact - in the oracle community the wait events have been along a
long
time - for several years this is also possible on SQL Server with the
dbcc sqlperf(waitstats), though on ehas to take into consideration
that
the waitstats on SQL Server are server-wide - one can not
(unfortuanality not yet) trace the wait events on a single user or
process.

For an exact description of waits and DBMS systems I also refer to
http//www.hotsos.com - one can always learn someting (good and bsad)
from oracle:))