what is wait_type of pageiolatch_sh?

That wait_type indicates that a process is waiting that SQL server reads/writes data to disk. This wait type is generally only seen to some of the system processes, and not to the user processes...

As i guess that the session you are speaking of is not a normal user process, you shouldn't try to get it out of that state explicitely. If this state is persistent, then you might have a slow disk, a slow disk controller, to few disks/controllers aso...

Cheers

I tend to agree with you to an extent however I think it may be more specific. i.e.

If I break the status down I get:-

pageio latch _sh

and I also can get

pageio latch _ex

the _ex & _sh sound to me like exclusive and shared i.e. lock type and LATCH is the type of Index Lock. So is it that the pageio corresponds more to a locking issue?

IO is always a locking issue, as only 1 process can write to the same file at the same time. And as all the large DB systems work with multiprocesses, the access to the file(s) has to be handeled properly, and there comes the locking into the play.
CHeers

I'd agree but only one process was dealing with this table, there was no locking contention at all, unless the delete somehow locked itself - possible in SQL Server I don't know? i.e. running out of locks and unable to escalate or something.

cheers

Ginola:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.