SQL Server Performance Forum – Threads Archive

PAGEIOLATCH / Performance problem with UPDATE quer

I’m having a big performance problem with an update query. The query runs fine on a 1Mil rows table but it seems to "hang" when i had 5mil rows in the table. The UPDATE process has a PAGEIOLATCH_SH wait type. I also looked at the memory and CPU usage on the server:
When i start the query the memory usage inceases and the CPU load is quite high (about 50%) when the memory usage reaches the amount of physical memory, the CPU load drops down to 0, and the UPDATE process is in state "sleepin" with PAGEIOLATCH_SH. I assume this a disk I/O related problem, but i have no idea what to do about it… The table has about 5mil rows and represent about 2.5 GB, the server has 4 CPUs at 1800 MHz and 1 GB ram. I assume increasing RAM to, say 4 GB would solve the problem… Is there any other way ? Any hints ? I also though i could break the table into smaller ones… would that help ?

You may also want to make sure your data is defragged since if its all over the place, then you will be doing lots of reads and waits while it reads. If I have to do updates on really large tables, I will break it up into ones that fit managably into my server – for you that sounds like 1 million rows.
If you have a clustered index, use that but do something like this declare @lowid int
declare @highid int
declare @maxid int select @maxid = max(id) from updatetable
set @lowid = 0
set @highid = @lowid + 1000000 while 1=1
begin
select @lowid, @highid, getdate()
waitfor delay ’00:00:05′ update updatetable set
value = something
where id between @lowid and @highid set @lowid = @lowid + 1000000
set @highid = @lowid + 1000000 if @lowid > @maxid
begin
checkpoint
break
end
else
checkpoint
end

Yes i do have a clustered index on that table and i actually tried this "trick" by myself after posting the message and guess what ? it works !!!! The query takes about 20 seconds to complete on 2Mil rows. With this trick it takes no time to complete Thanks a lot ChrisFretwell! That seem to be a solution to my problem, however i have one more question. Isn’t it weird that i have to use such a trick… i mean SQL server could eventually use that kind of optimisation by itself, right now it looks like it’s trying to load the full table into memory instead of loading and updating pieces by pieces. Also, i said in my first post that a memory increase would probably be a solution, do you think i’m right ? Memory seems to be the critical ressource in this situation. Thanks again, and sorry for asking more questions even after you found a solution but i like to understand what i’m doing and what’s happening.

To get optimum resource usage of server take help from PERFMON(SYSMON) for memory, physical disk, processor, process & other SQL counters. Also add Set NOCOUNT before that query which will have little performance gain. Adding more physical memory will gain the performance but before that its better to fine tune the queries which may result fragmentation in long run. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Ok thx all for your help

]]>