Posted by ianwr on 12/04/07 14:59

Hi,

I wonder if anyone can shed any light on the following as i just can't
explain it.

A user is running an update on a 500m+ row table setting a column
value, computing its value from another column in the table. It's now
been running for 23hours.

The server is Itanium 64, enterprise 2005, SAN based storage and it
usually handles anything with this volume quite quickly, probably
about 30 mins or so.

There is nothing else running currently although overnight batches,
backups etc have been running within the last 23 hours.

In sysprocess it showing the following :-

spid kpid blocked waittype waittime
lastwaittype waitresource
52 5236 0 0x0044 30
PAGEIOLATCH_EX 6:13:1754732

the process seems to stay in this waittype for a few secnds and then
goes to a 0x0000 and then back into this one again. I can see from the
IO counter that IO is increasing and also looking at the current IO i
see the following so presume the query is still working :-

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

gives results :-

6 13 151115052 10 0x0000000008624080

I just can't explain why it is so slow when nothing else is ruuning.

Anyone have any ideas on what i can check on?

Thanks

Ian.

[Back to original message]