Performance problems, perhaps disk, associated with PAGEIOLATCH_SH

Question

Performance problems, perhaps disk, associated with PAGEIOLATCH_SH

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

You can go back and forth here on the forum for days or even weeks trying to isolate/fix the various and sundry causes of problems. Or you can hire a performance tuning consultant and have him/her work their magic in hours or days - while also improving your knowledge and skills along the way.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Robert Davis One Orange Chip Points: 28027 More actions · Answer 2

Well said, SQLGuru!! I think many of us were thinking it and just not saying it.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

gmby SSCarpal Tunnel Points: 4511 More actions · Answer 3

You might want to check page life expectancy. We had a server with many of the PAGEIOLATCH_SH waits along with page life expectancy that would live at single and double digits. That pointed us toward the possibililty of being memory bound. In our case the queries were tuned as tight as they could, so we realized our tables had grown large enough to force memory to get paged in and out constantly. We were legitimately memory bound. (Tho archiving had been proposed, but avoided by mgmt.) We increased memory from 4gig to 32gig and performance rocks!

Hopefully mgmt learned it's lesson and will not put off archiving efforts...or we'll just hit the wall again...

jg

mrcmiranda_sg SSC Journeyman Points: 77 More actions · Answer 4

Need help please.. We are experiencing PageIOLatch_* and spid being blocked by itself. the query goes like this. Not sure if this is index problem but we already tried to restore a copy of the database to a different server just to isolate the hardware problem, still same. Any suggestion please! thanks!

Select Count(0) From SM_Rework_History_V A,

( Select Strip_ID, Row_Pos, Col_Pos, Max(TimeStamp)

As TimeStamp From SM_Rework_History_V Where Lot_ID = 'ABC12345.1' And

TimeStamp <= '2010/12/29 20:04:09' And (Unit_Status = 'B' Or Unit_Status = 'G')

Group By Strip_ID, Row_Pos, Col_Pos) B,

( Select Distinct Strip_ID From SM_Rework_History_V Where Lot_ID = 'ABC12435.1'

And TimeStamp <= '2010/12/29 20:04:09' And TGrade Is Not Null) C

Where A.Strip_ID = B.Strip_ID

And A.Strip_ID = C.Strip_ID And A.Row_Pos = B.Row_Pos And A.Col_Pos = B.Col_Pos

And A.TimeStamp = B.TimeStamp And A.Unit_Status = 'B'

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) you posted to a 10 month old thread. Please start a new one.

2) the query could be suboptimal. you may also need an index or two. what does the query plan look like?

3) you probably have a suboptimal IO subsystem. I only say that because essentially every client I have ever had has that. 😀

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mrcmiranda_sg SSC Journeyman Points: 77 More actions · Answer 6

Thank you for the response. I would start a new one and will give more details. Thanks!:-)