simple update statment and waits (Sql 2k)

when i run a simple update statement,  i see that there is a PAGEIOLATCH_SH wait immediately.. and it lingers on for a long time.. (2 hours).. the updates is for 12M records.

http://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28

says that this indicates DISK issues..

is there a way to confirm this? (this is SQL 2000)
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
please clarify the background to this...

whilst as the article suggests high values for these indicators could be an indication that it is time to begin hardware replacement... they have to be read in the overall context of system usage/load...

are you saying you are updating a significant proportion of a 12million row table...?

what is the unit of work...?
what other users/processes are active on the server/disk?

what is the purpose of the update?
is this a regular requirement/one off?

have you had any other indications of possiblle hardware problems?
when was the last hardware/software change...

...

OS and software levels...
skaraiCommented:
In Perfmon you can add the physical disk counters for the respective disks (if logical volumes are co-located on a physical disk you would want to add the logical counters as well. Average disk i/o read and write times will suffice to illustrate the point. Ideally the value wouldmt be greater than 2 - on inuffcient disk i/o bandwidth systems the counter(s) can easily go up into the hundreds.

Another inportant place to look in case you are on SQL 2000 SP 4 (2039) or better (min build recommendation is 2157), are the SQL server errorlogs where you would want to look for messages regarding disk i/o latency. E.g a disk i/o completion lastet longer than 15s (when you see this message your disk i/o is factor 1000 too slow. Expected i/o completion is 15 ms or better.
25112Author Commented:
the update statement is
UPDATE a
SET column1 = b.Column1
FROM a
INNER JOIN b
ON a.column1 = b.Column2
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

25112Author Commented:
are you saying you are updating a significant proportion of a 12million row table...?
what is the unit of work...?
what other users/processes are active on the server/disk?
what is the purpose of the update?
is this a regular requirement/one off?
have you had any other indications of possiblle hardware problems?
when was the last hardware/software change...
OS and software levels...
------------------------------
yes, 12 m records are expected to be updated
the server is normal in every other way.. there is no other load.. (this is not a live server)
the purpose is to update the correct id from another table. this will be a one time process.
i checked perfmon and hardware counters are OK, and never had any hardware issues so far (crash etc).. this is an old server.. server os 2003..it has sql 2000 sp3 8.00.760
25112Author Commented:

skarai, thanks for the idea of error log.. but nothing in there. (this is only SP3 - is that an issue)
LowfatspreadCommented:
the purpose is to update the correct id from another table. this will be a one time process.

in that case if your only problem is the excessive time it takes ... investigate other means...

the standard approach would be to perform the updates in smaller chunks with commits after every 10000 rows...
(e.g. update Top (n) ... or ;with cte as (select top (n) .... Update cte set ...) in a loop)
that will reduce the overall system overhead...

alternatively you could use BCP to select the data out if the table with the new values applied...
then drop the table ...
then recreate the table with just the clustering index
then bcp the data back into the table
then re-applied any other indexes you have....




Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
Make sure that you have indexes on the columns in each of their tables:

a.column1 = b.Column2
ZberteocCommented:
I am referring to the columns in the ON clause used to join the 2 tables, of course.
25112Author Commented:
thanks for the standards..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.