Posted by Erland Sommarskog on 12/04/07 22:47

ianwr (ianwrigglesworth@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_requests 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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]