help and advice on waittype 0x0044

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.Regarding PAGEIOLATCH_EX --I/O page latch exclusive. Waiting for the write o
f
an I/O page.
I think its just showing that disk activity is getting performed as updates
are currently executing. There is a possibility of slow disk subsystem(that
you can verify using disk counters in performance monitor like average disk
queue length, %disk time etc.).
Is it possible in your case to perform batch by batch updates? Also, whts
the recovery model of this database.
Manu
"ianwr" wrote:

> 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.
>|||Ian,
I see that on my machines a non-zero current waittype (almost?) always is
paired with the same lastwaittype, so I assume that (with no information to
the contrary) that 0x0044 = PAGEIOLATCH_EX.
It that is correct, then from
http://msdn2.microsoft.com/en-us/library/ms179984.aspx it says: Occurs when
a task is waiting on a latch for a buffer that is in an I/O request. The
latch request is in Exclusive mode. Long waits may indicate problems with
the disk subsystem.
So, although I don't know what is wrong, this seems to indicate some
problems with your SAN. I am not SAN wise, but it suggests that either
there is a communication problem between server and SAN or the SAN is having
disk problems.
FWIW,
RLF
"ianwr" <ianwrigglesworth@.yahoo.co.uk> wrote in message
news:b794459f-13ed-49b4-b444-3609f30ac354@.v4g2000hsf.googlegroups.com...
> 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.|||Hi Manu,
The recovery model is simple mode and i suppose the developer can do
it in batches but is a one off and i told him to leave it running
because it would take just as long to roll back.
Disk subsystem is usally quite quick, i can create an index on 600m+
row table in about 25 mins so for this to take 23 hours is really
strange|||Thanks Russell,
I'm thinking its probably the SAN, I suspect they probably have other
apps running on the same spindles as everythign seems to be setup
completely wrong at this client site.
I'm wondering if there is something else happening on the san that is
causing things to go slow ... how lovely it would be to have some SAN
diagnostic tool that i could run from my desktop rather than go
through the san people. lol.
Anyway thanks for your help
Ian.