April 23, 2007 at 4:17 pm
I'm having a very strange problem with one of our servers that happend intermittently and causes update queries to hang. Sometime an update query will run in 1 minute and sometimes the same query will run for over an hour.
When this happens and I look at the current activity, the process has multiple CXPACKET Waits and a PAGEIOLATCH_SH that appears to be blocked by the CXPACKET waits. It looks something like this
Proc ID WaitType CPUTIME Blocked BY
62 PAGEIOLATCH_SH 1519031 62
62 CXPACKET 172 0
62 CXPACKET 188 0
62 CXPACKET 156 0
62 CXPACKET 188 0
The server has 4 cpus and 4 gigs of ram. When this happens the cpus are minimally active and the physical memory usage is only at 2 gigs. Perfmon shows the avg disk queue length to be 100%. I think it may be a hardware problem. Any advice would be greatly appreciated.
Thanks, Mike
April 23, 2007 at 6:26 pm
Never mind on this. I have found the problem with the query.
Mike
April 30, 2007 at 4:12 pm
I'm trying to diagnose a similar problem - can you provide a short synopsis of the problem in your query that caused this issue?
I was leaning towards I/O problems in my case, since this server has only a single drive for MDFs and one for LDFs - there's not a disk array in sight to go with these quad Xeon processors (seems silly to me). There's also only a single NIC handling all the traffic.
May 1, 2007 at 9:01 am
This seems to happen when updating 2 very large tables (over 2 million) by joining them together. My process waits on a PAGEIOLATCH and goes to sleep and SQL server doesn't seem to be doing anything that is taxing the system. I had thought that adding indexes had helped but the problem was still happening. I found a bit of code on the internet that helped by breaking the update down into smaller chunks by the using the clustered primary key which is an identity column.
I'm still having this problem on large updates and if any has any advice it would greatly appreciated. Adding the loop below to every update statement is a real pain. Below is the basic code which updates the table in chunks of 100000 records at a time.
--Update Matches - This is broken into chunks of 100,000 to keep the update query from hanging
DECLARE
@LOWID INT
DECLARE
@HIGHID INT
DECLARE
@MAXID INT
SELECT
@MAXID = MAX(ID) FROM TABLE
SET
@LOWID = 0
SET
@HIGHID = @LOWID + 100000
WHILE
1=1
BEGIN
SELECT @LOWID, @HIGHID, GETDATE()
WAITFOR DELAY '00:00:02'
UPDATE TABLE SET ColumnX = 'XXX",
TYPE = 'H'
WHERE ID BETWEEN @LOWID and @HIGHID
SET @LOWID = @LOWID + 100000
SET @HIGHID = @LOWID + 100000
IF @LOWID > @MAXID
BEGIN
CHECKPOINT
BREAK
END
ELSE
CHECKPOINT
END
May 2, 2007 at 9:07 am
It appears that your server is extremely busy and is hitting a bottle neck somewhere. In the initial posting by Michael their was a deadlock that either was caused by a throught put issue or created a through put issue.
When I see CXPacket waits and PAGEIO Latch waits I get out Performance Monitor. Initiate the Performance Monitor application from the Administrator Tools folder. Add the following Counter. 1) Memory - Pages / sec 2) Physical Disk - Avg. Disk Queue Length and 3) Processor - % Processor Time.
Watching the values of these counters during peak traffic should point you in the direction of the bottleneck.
November 3, 2008 at 1:53 am
Michael Crider (5/1/2007)
This seems to happen when updating 2 very large tables (over 2 million)by joining them together. My process waits on a PAGEIOLATCH and goes to sleep and SQL server doesn't seem to be doing anythingthat is taxing the system. I had thought that adding indexes had helped but the problem was still happening.
Actually, when updating, indexes will slow things down rather than speed them up because the indexes themelves need to be updated too.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
November 4, 2008 at 9:41 am
Vegard Hagen (11/3/2008)
Michael Crider (5/1/2007)
This seems to happen when updating 2 very large tables (over 2 million)by joining them together. My process waits on a PAGEIOLATCH and goes to sleep and SQL server doesn't seem to be doing anythingthat is taxing the system. I had thought that adding indexes had helped but the problem was still happening.
Actually, when updating, indexes will slow things down rather than speed them up because the indexes themelves need to be updated too.
That is too broad a statement. if you are batching updates/deletes as this user is doing, getting the batch size down to a small enough size that the optimizer uses an index seek to find the rows to update can provide an enormous increase in throughput, despite having an extra index or two to update. If doing most/ALL rows and the table can be taken offline, I would certainly recommend removing all indexes but the one necessary for the index seek then rebuilding the dropped indexes after the update is complete.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 5, 2008 at 1:26 am
Ah, you're right, of course!
I should have said something more along the lines of "when updating, indexes may slow things down", then specified what I meant and how to avoid it. The world is not just black and white, is it?
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply