When trying to update one column in a table with about 42 million
records the server seems to never finish. I let the query run for 18hrs
before I stopped it. The exact same query on a table with 20-30 million
records finishes anywhere from 40min-2hrs. When I look at the current
activity on our server during the update of the 42mil rows I see under
the wait type for that session is says 'PAGEIOLATCH_SH' with a wait
resource of something like '6:1:2678487'

Every time I refresh the current activity I see the wait resource number
change so I know the server isn't locked up. I also noticed that this
appears under both the update of the smaller table and the update of the
larger table, but as I refresh during the update of the smaller table
I'll also see a wait type of 'not waiting'. I never see that under the
update of the larger table. In other words I think this has a lot to do
with my problem.

I'm running SQL server 2000 sp2 EE on a 2.4Ghz zeon with 4Gb ram.

I ran across some latching/locking articles that said there are some
issues with dual processors and large update queries. Since the Zeon
makes the OS see two processors I configured SQL to use only one. This
didn't seem to help at all.

Our transaction log for the database is set to grow unrestricted and the
drive the logs files reside on has 60gb remaining so I know there is
enough room for the log entries.

The database and tembDB as stripped across 4 drives. I have read
different things saying that the databases and the tempTB should be on
different drives but at this time I don't have the storage to do this.
Is this possibly my problem. Do I have disk IO bottlenecks?

If anyone has any suggestions please post them. I would love to get
this working seamlessly.

The update I'm running is as follows. The cost column is null for every
record to start.

update MainNew set cost = c.icost
From (MainNew join Stores s
on MainNew.StoreId = s.sstore) join ItemCosts c
on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId

Thanks a lot
Philip

Report this post to a moderator

I am not sure about the 'PAGEIOLATCH_SH'. I know that the query is the same
as that of the smaller table. But what about the indexes? The query:
> update MainNew set cost = c.icost
> From (MainNew join Stores s
> on MainNew.StoreId = s.sstore) join ItemCosts c
> on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId

could probably change from one table to another if indexes on the joined
columns were different. Just and idea.

"Philip Morrow" <Cracker@mymorrow.com> wrote in message
news:3DA451F7.6020203@mymorrow.com...
> When trying to update one column in a table with about 42 million
> records the server seems to never finish. I let the query run for 18hrs
> before I stopped it. The exact same query on a table with 20-30 million
> records finishes anywhere from 40min-2hrs. When I look at the current
> activity on our server during the update of the 42mil rows I see under
> the wait type for that session is says 'PAGEIOLATCH_SH' with a wait
> resource of something like '6:1:2678487'
>
> Every time I refresh the current activity I see the wait resource number
> change so I know the server isn't locked up. I also noticed that this
> appears under both the update of the smaller table and the update of the
> larger table, but as I refresh during the update of the smaller table
> I'll also see a wait type of 'not waiting'. I never see that under the
> update of the larger table. In other words I think this has a lot to do
> with my problem.
>
> I'm running SQL server 2000 sp2 EE on a 2.4Ghz zeon with 4Gb ram.
>
> I ran across some latching/locking articles that said there are some
> issues with dual processors and large update queries. Since the Zeon
> makes the OS see two processors I configured SQL to use only one. This
> didn't seem to help at all.
>
> Our transaction log for the database is set to grow unrestricted and the
> drive the logs files reside on has 60gb remaining so I know there is
> enough room for the log entries.
>
> The database and tembDB as stripped across 4 drives. I have read
> different things saying that the databases and the tempTB should be on
> different drives but at this time I don't have the storage to do this.
> Is this possibly my problem. Do I have disk IO bottlenecks?
>
> If anyone has any suggestions please post them. I would love to get
> this working seamlessly.
>
> The update I'm running is as follows. The cost column is null for every
> record to start.
>
> update MainNew set cost = c.icost
> From (MainNew join Stores s
> on MainNew.StoreId = s.sstore) join ItemCosts c
> on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId
>
> Thanks a lot
> Philip
>

Report this post to a moderator

The indexes on the smaller and larger table are identical

ALTER TABLE [MainNew] ADD
CONSTRAINT [PK_MainNew] PRIMARY KEY CLUSTERED
([period],[storeid], [itemid], [price])
ON [PRIMARY]
Go


chris wrote:
> I am not sure about the 'PAGEIOLATCH_SH'. I know that the query is the same
> as that of the smaller table. But what about the indexes? The query:
>
>>update MainNew set cost = c.icost
>> From (MainNew join Stores s
>>on MainNew.StoreId = s.sstore) join ItemCosts c
>>on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId
>
>
> could probably change from one table to another if indexes on the joined
> columns were different. Just and idea.
>
> "Philip Morrow" <Cracker@mymorrow.com> wrote in message
> news:3DA451F7.6020203@mymorrow.com...
>
>>When trying to update one column in a table with about 42 million
>>records the server seems to never finish. I let the query run for 18hrs
>>before I stopped it. The exact same query on a table with 20-30 million
>>records finishes anywhere from 40min-2hrs. When I look at the current
>>activity on our server during the update of the 42mil rows I see under
>>the wait type for that session is says 'PAGEIOLATCH_SH' with a wait
>>resource of something like '6:1:2678487'
>>
>>Every time I refresh the current activity I see the wait resource number
>>change so I know the server isn't locked up. I also noticed that this
>>appears under both the update of the smaller table and the update of the
>>larger table, but as I refresh during the update of the smaller table
>>I'll also see a wait type of 'not waiting'. I never see that under the
>>update of the larger table. In other words I think this has a lot to do
>>with my problem.
>>
>>I'm running SQL server 2000 sp2 EE on a 2.4Ghz zeon with 4Gb ram.
>>
>>I ran across some latching/locking articles that said there are some
>>issues with dual processors and large update queries. Since the Zeon
>>makes the OS see two processors I configured SQL to use only one. This
>>didn't seem to help at all.
>>
>>Our transaction log for the database is set to grow unrestricted and the
>>drive the logs files reside on has 60gb remaining so I know there is
>>enough room for the log entries.
>>
>>The database and tembDB as stripped across 4 drives. I have read
>>different things saying that the databases and the tempTB should be on
>>different drives but at this time I don't have the storage to do this.
>>Is this possibly my problem. Do I have disk IO bottlenecks?
>>
>>If anyone has any suggestions please post them. I would love to get
>>this working seamlessly.
>>
>>The update I'm running is as follows. The cost column is null for every
>>record to start.
>>
>>update MainNew set cost = c.icost
>> From (MainNew join Stores s
>>on MainNew.StoreId = s.sstore) join ItemCosts c
>>on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId
>>
>>Thanks a lot
>>Philip
>>
>
>
>

Report this post to a moderator

Phillip,

If your trying to update every row in the table you will eventually need to
take out a table level lock or will have thousands or hundreds of thousands
of lower level locks. NO ONE can be accessing this table with even a shared
lock in order for this to complete. I would attempt to do the updates in
many smaller updates vs 1 large one. It would be much more user friendly
and will most likely finish faster as well. There are several techniques
and which one is best depends some on the tables layout etc. If nothing else
you can use SET ROWCOUNT to limit the number of updates with each pass and
add " AND Cost IS NULL. As I said there may be more optimized way but you
will need to determine which is best given your scenario.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm


"Philip Morrow" <Cracker@mymorrow.com> wrote in message
news:3DA46EC1.8060900@mymorrow.com...
> The indexes on the smaller and larger table are identical
>
> ALTER TABLE [MainNew] ADD
> CONSTRAINT [PK_MainNew] PRIMARY KEY CLUSTERED
> ([period],[storeid], [itemid], [price])
> ON [PRIMARY]
> Go
>
>
> chris wrote:
> > I am not sure about the 'PAGEIOLATCH_SH'. I know that the query is the
same
> > as that of the smaller table. But what about the indexes? The query:
> >
> >>update MainNew set cost = c.icost
> >> From (MainNew join Stores s
> >>on MainNew.StoreId = s.sstore) join ItemCosts c
> >>on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId
> >
> >
> > could probably change from one table to another if indexes on the joined
> > columns were different. Just and idea.
> >
> > "Philip Morrow" <Cracker@mymorrow.com> wrote in message
> > news:3DA451F7.6020203@mymorrow.com...
> >
> >>When trying to update one column in a table with about 42 million
> >>records the server seems to never finish. I let the query run for 18hrs
> >>before I stopped it. The exact same query on a table with 20-30 million
> >>records finishes anywhere from 40min-2hrs. When I look at the current
> >>activity on our server during the update of the 42mil rows I see under
> >>the wait type for that session is says 'PAGEIOLATCH_SH' with a wait
> >>resource of something like '6:1:2678487'
> >>
> >>Every time I refresh the current activity I see the wait resource number
> >>change so I know the server isn't locked up. I also noticed that this
> >>appears under both the update of the smaller table and the update of the
> >>larger table, but as I refresh during the update of the smaller table
> >>I'll also see a wait type of 'not waiting'. I never see that under the
> >>update of the larger table. In other words I think this has a lot to do
> >>with my problem.
> >>
> >>I'm running SQL server 2000 sp2 EE on a 2.4Ghz zeon with 4Gb ram.
> >>
> >>I ran across some latching/locking articles that said there are some
> >>issues with dual processors and large update queries. Since the Zeon
> >>makes the OS see two processors I configured SQL to use only one. This
> >>didn't seem to help at all.
> >>
> >>Our transaction log for the database is set to grow unrestricted and the
> >>drive the logs files reside on has 60gb remaining so I know there is
> >>enough room for the log entries.
> >>
> >>The database and tembDB as stripped across 4 drives. I have read
> >>different things saying that the databases and the tempTB should be on
> >>different drives but at this time I don't have the storage to do this.
> >>Is this possibly my problem. Do I have disk IO bottlenecks?
> >>
> >>If anyone has any suggestions please post them. I would love to get
> >>this working seamlessly.
> >>
> >>The update I'm running is as follows. The cost column is null for every
> >>record to start.
> >>
> >>update MainNew set cost = c.icost
> >> From (MainNew join Stores s
> >>on MainNew.StoreId = s.sstore) join ItemCosts c
> >>on c.DMA = s.SMarketNo and c.MItem = MainNew.ItemId
> >>
> >>Thanks a lot
> >>Philip
> >>
> >
> >
> >
>

Report this post to a moderator

Philip Morrow wrote:
> When trying to update one column in a table with about 42 million
> records the server seems to never finish. I let the query run for
> 18hrs before I stopped it. The exact same query on a table with 20-
> 30 million records finishes anywhere from 40min-2hrs. When I look at
> the current activity on our server during the update of the 42mil
> rows I see under the wait type for that session is says
> 'PAGEIOLATCH_SH' with a wait resource of something like '6:1:2678487'

<snip>

If I wore a DB analyst's hat I'd be asking why you have got a design that
requires 42 million records to be updated (especially if this is a periodic
task rather than a one off).

Not meaning to be unhelpful, but having just watched someone add 3G memory
and three processors when the fix was to correct some indexes, it is always
worth challenging these things.

Ian

Report this post to a moderator

It is a one time deal that only happens at the time of dataload, and I'm
posting this issue here for good suggestions on a better/different way
to accomplish this task. I could partition it but I'd rather wait till
after the data has been loaded and gone through our dataload script
which is where this update is.

Plus I hear a lot about how SQL server is making leaps and bounds in the
area of performance and can handle the massive amounts of data that
would normally be reserved for something like Oracle. Oracle could do
this update without even choking. I've done it.

Ian Spencer wrote:
> Philip Morrow wrote:
>
>>When trying to update one column in a table with about 42 million
>>records the server seems to never finish. I let the query run for
>>18hrs before I stopped it. The exact same query on a table with 20-
>>30 million records finishes anywhere from 40min-2hrs. When I look at
>>the current activity on our server during the update of the 42mil
>>rows I see under the wait type for that session is says
>>'PAGEIOLATCH_SH' with a wait resource of something like '6:1:2678487'
>
>
> <snip>
>
> If I wore a DB analyst's hat I'd be asking why you have got a design that
> requires 42 million records to be updated (especially if this is a periodic
> task rather than a one off).
>
> Not meaning to be unhelpful, but having just watched someone add 3G memory
> and three processors when the fix was to correct some indexes, it is always
> worth challenging these things.
>
> Ian
>
>

Report this post to a moderator