waittype of PAGEIOLATCH_SH

[Antzz]

Hi Guys,
I have a stored procedure that runs as a job. It is complex and calls multiple SPs from within it. I have been facing the above error from about couple of days back when it executes.

It hangs as it tries to select records from a table before trying to do a row processing. The occurance is random but very frequent.

Any ideas?

 

[ptheriault]

That wait type means that your sp is waiting on a shared lock. Have you run sp_lock or sp_who2 to see if you are being blocked?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Antzz]

Yeah. It shows only that SP as having the IOLATCH. Could not find any other listing.

 

[ptheriault]

PAGEIOLATCH waittypes are used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I/O subsystem issues.

Are there any disk IO errors?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

In addition to being a possible disk IO issue it can also mean that you aren't reading data from the disk in an efficient mannor. Check your indexes, and stats to see if anything is up to date.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com