Re: What's the deal with PAGEIOLATCH_SH?

From: James Bradley (jbradley_at_isa-og.com)
Date: 04/24/04 

Date: Sat, 24 Apr 2004 09:32:00 -0600

>From what I understand, a latch is a lightweight lock, which seems
fine to me. I checked the spids from Enterprise Manager and there is
no blocking/blocked by listed. When I looked, there were only two
connections to the databases - one by Query Analyzer when it was
running my command, and the second by Enterprise Manager when I was
looking at what was happening.

Could the PAGIOLATCH_SH be waiting for data to come in from the disk?
I was monitoring total disk I/O and it was only 1MB/sec each way, not
like the 20-60MB/sec that I was expecting.

I really need to figure out what is causing these delays, because I
can't wait hours for a statement that should take 20 minutes :-(

Thanks,

Jami

On Sat, 24 Apr 2004 09:30:17 +1000, "Greg Linwood"
<g_linwoodQhotmail.com> wrote:

>Hi James
>
>A PAGEIOLATCH_SH simply indicates that the connection your statement is
>running on is waiting on acquiring a shared lock on a storage page. There
>are a variety of reasons why this could be occurring, but the most common is
>blocking. Have you checked sp_lock to see if the spid is blocked by another
>spid? You can also easily see if the spid is actually doing work by checking
>the CPU activity at the same time.
>
>Regards,
>Greg Linwood
>SQL Server MVP
>
>"James Bradley" <jbradley@isa-og.com> wrote in message
>news:ti3j801v1otant21ph1f5l8pq3t4apm4bv@4ax.com...
>> I'm seeing some strange performance problems with my SQL server (8.0
>> SP3). I have some large data conversion work to do and sometimes the
>> server runs pretty quickly, and sometimes is simply appears to go idle
>> in the middle of a long statement!
>>
>> Whenever I check the current activity on the process, I'm seeing that
>> it is waiting (usually 50-400 mSec) for a PAGEIOLATCH_SH. The system
>> has no load (1-5% CPU, 1MB/sec disk I/O). I am using two databases,
>> and I am the only user on those two. There are 3-5 other users on a
>> different database, usually idle, and we are running reporting
>> services.
>>
>> What is the server waiting for? Is there some conflict with tempdb?
>> Is there some command to figure out what the resource is that it is
>> wairing for (e.g., 9:1:1072396)?
>>
>> Thanks!
>>
>> Jami
>

Relevant Pages

  • RE: Alert for Blocked Process?
    ... "Blocking" as such is not a bad thing. ... Server is given a 'spid'. ... status, waittime and waittype. ... Microsoft, SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Blocking
    ... Enterprise Manager is not really a good tool to use to ... monitor these types of things - unless you want to refresh ... monitor for blocking and log the results of blocking ... How to monitor blocking in SQL Server 2005 and in SQL Server ...
    (microsoft.public.sqlserver.odbc)
  • SPID value of -4
    ... I am currently seeing a spid value of -4 in my production SQL Server 2005 ... There is alot of blocking and I came across this number. ...
    (microsoft.public.sqlserver.connect)
  • Re: Need help in finding SPID
    ... There's some confusion in the SQL Server word regarding terminology. ... We do mean SPID, sessionID or whatever you want to call it. ... How would I track back to the process causing the blocking? ...
    (microsoft.public.sqlserver.server)
  • Deadlocked on thread | communication buffer resources with another process
    ... resources with another process and has been chosen as the deadlock victim. ... SPID: 101, ECID: 0, Not Blocking ...
    (microsoft.public.sqlserver.server)
Loading