PAGEIOLATCH_SH

Question

PAGEIOLATCH_SH

chrism 16498

SSC Enthusiast

Points: 124
More actions
February 17, 2010 at 9:42 pm

#141296

Hi guys,
I'm just wondering if any has any pointers for me.
I am currently pulling my hair out due to a PAGEIOLATCH_SH lock on our SQL server 2000 database. I have previously investigate this an found post's that suggested turning off parrallelism which I have done, however my client is enduring a major performance hit right now due to a process blocking itself with PAGEIOLATCH_SH.
Is anyone able to assist to by telling me what causes a PAGEIOLATCH_SH lock and how to go about diagnosing the root cause of the problem.

Viewing 5 posts - 1 through 4 (of 4 total)

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

chrism 16498 (2/17/2010)
Hi guys,
I'm just wondering if any has any pointers for me.
I am currently pulling my hair out due to a PAGEIOLATCH_SH lock on our SQL server 2000 database. I have previously investigate this an found post's that suggested turning off parrallelism which I have done, however my client is enduring a major performance hit right now due to a process blocking itself with PAGEIOLATCH_SH.
Is anyone able to assist to by telling me what causes a PAGEIOLATCH_SH lock and how to go about diagnosing the root cause of the problem.

PAGEIOLATCH_SH indicates the IO bottleneck .

Check all possible disk side counter (Via Google).

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

chrism 16498 SSC Enthusiast Points: 124 More actions · Answer 2

Yesterday when the pageiolatch_sh locks were ocurring I opened perfmon and added the following counters Physical disk: reads/sec and writes/sec and SQL Server buffer manager: Page Life Expectancy.

I noticed that the writes/sec were constantly on 100% but reads/sec were very low and the SQL server page life expectancy was around 60% then climbed to 100%.

So from that I gather that something is writing to the hard disk however I can't identify if it is SQL server or an external application or user. My clients IT department are not giving me any help but so far I have not been able to find another process running ie Anti-virus or backup

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Well, you can spread out your backup schedule so it is not running during the normal duty hours and won't be a factor. Also, when it happens again, try turning off your anti-virus. If it is not configured correctly it may be causing contention issues with your databases. If nothing else is installed on the server, then you should be able to look at the processes running and determine if it is SQL that is causing your problem.

Joie Andrew
"Since 1982"

yizhe2000 Valued Member Points: 65 More actions · Answer 4

I had met the same problem few months ago.

I found that was coused by the BIG sql . just like the update in a huge table ,you may monitor the sql at the sql server console monitor,you can see the activity running on the server .then you will find what make this problem.

I try to ruduece the data in then update sql. then the problem has disappeared.:-)