Can anyone help with waittype 0x0044?

[ianwr]

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_t icks,
scheduler_addre ss
from sys.dm_io_virtu al_file_stats(N ULL, NULL)t1,
sys.dm_io_pendi ng_io_requests as t2
where t1.file_handle = t2.io_handle

gives results :-

6 13 151115052 10 0x0000000008624 080

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.

[Erland Sommarskog]

ianwr (ia************ **@yahoo.co.uk) writes:

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.

Would the update cause the rows to grow? For instance, if this is
a new column that was added as nullable, and is now being populated?
In that case the table will need to grow, and could take some time.
Not the least if the data file has to grow as well.

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

In sys.dm_exec_req uests there is a wait_type which is likely to be
more informative than 0x0044.

Anyone have any ideas on what i can check on?

Obviously a

SELECT COUNT(*) FROM tbl (NOLOCK) WHERE col <expected value

will you some progress information.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ianwr]

Hi Erland,

Thanks for the view to use, unfortunately when i arrived this morning
the task had stopped and took about 29 hours to run.

Going to keep an eye on things and check out the san as well today,
Thanks for the info anyway, if it happens again i'll repost

Thanks

Ian,