Re: What's the deal with PAGEIOLATCH_SH?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Greg Linwood (g_linwoodQhotmail.com)
Date: 04/23/04 

Date: Sat, 24 Apr 2004 09:30:17 +1000

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: Slow database update question
    ... ASYNC_NETWORK_IO means that SQL Server is waiting for the connection to fetch data. ... Select sequencenumber, openprice,highprice,lowprice,closeprice,volume ... The first spid is waiting for an Intent Exclusive Lock on page level, ...
    (microsoft.public.sqlserver.programming)
  • Re: Slow database update question
    ... The first spid is waiting for an Intent Exclusive Lock on page level, ... This wait state indicates delays out side SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can you use osql in DTS?
    ... Dim pkg, task, spid ... Columnist, SQL Server Professional ... the variable I need to pass into a stored procedure and have it function ... >> osql. ...
    (microsoft.public.sqlserver.dts)
  • Re: Can you use osql in DTS?
    ... Dim pkg, task, spid ... Columnist, SQL Server Professional ... the variable I need to pass into a stored procedure and have it function ... >> osql. ...
    (microsoft.public.sqlserver.programming)
  • Re: Can SELECT block UPDATE?
    ... a select can block an update depending on the isolation level. ... Here is a link to the SQL Server 2000 BOL topic: ... Selecting in the READ UNCOMMITTED isolatino level, or using the NOLOCK hint, is the least restrictive locking, but as the notes explain, it has its downsides as well. ... I tried to find spid that was causing the block, ...
    (microsoft.public.sqlserver.server)