We help IT Professionals succeed at work.

what is wait_type of pageiolatch_sh?

Ginola
Ginola asked
on
Also how do I get the session thread out of this state?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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?
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
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
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
You don't have to forget that even if only one process deals with 1 table, other processes work on the same database, and thus on the same file!
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.