SQL Server Performance

PAGEIOLATCH_SH Mystery

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by Righteousman, May 3, 2010.

  1. Righteousman New Member

    Hi Guys,
    We had a major problem over the weekend where a specific process seems to have gotten stuck for about 12 hours before I killed it and restarted it (and it works ok now).
    The process was doing a delete from one table, based on critieria in another table. The two tables were joined together and the table being joined in was HUGE (132 million rows). Normally the delete runs in about 15 minutes, even though the join results in a table scan -- however this time it appeared to never want to finish.
    Upon further investigation I saw that it was waiting on PAGEIOLATCH_SH. Never waiting more than maybe 100ms, but always with that wait type and always showing a different Page ID.
    Also no other processes were accessing anything in that database -- it was just the one process accessing the database entirely to itself for the entire duration.
    Does anyone have any ideas as to what might have happened? I'm completely stumped on this.
    One thing I did do was create an index on the 132 million row table so that the join it does is now a SEEK instead of a SCAN and that sped it up by quite a bit. But with that said, I still need to determine why the lockup occurred all of a sudden when doing that SCAN.
    Any thoughts or ideas would be greatly appreciated!
    Thanks
    Edit: I should also mention that only about 2000 rows were being deleted so it wasn't a hugely logged operation or anything like that.
  2. satya Moderator

    What is the service pack on SQL Server?
    The indication of PAGEIOLATCH_SH is waiting for the completion of an IO that it itself issued.
    You can also identify I/O bottlenecks by examining the latch waits. These latch waits account for the physical I/O waits when a page is accessed for reading or writing and the page is not available in the buffer pool. When the page is not found in the buffer pool, an asynchronous I/O is posted and then the status of the I/O is checked. If I/O has already completed, the worker proceeds normally. Otherwise, it waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request.

    See this TSQL to get I/O latch wait stats:
    Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type
    FInally,
    A slow I/O subsystem, or overworked I/O subsystem will sometimes experience high PageIOlatch

    waits that are actually I/O issues. These issues can be compounded by cache flushes or missing indexes.
  3. dzuberts New Member

    I found this to be one of simplest and most enlightening explanations of the PAGEIOLATCH wait types. Thanks very much. I have a follow-on question: Is there a means of determining if your buffer pools are underallocated -- ie., in order to determine if this is the cause of the PAGEIOLATCH waits vs. an overworked I/O subsystem?
  4. Luis Martin Moderator

    PAGEIOLATCH_SH

    Shared mode page IO latch request. The PAGEIO latches are a subset of BUFlatches used when the buffer and associated data page or the index page is inthe middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers.

    Resolved By

    DBAs and Developers

    Solutions

    1. Tune the SQL statement waiting on this waittype. Inefficient SQL statements read more data than necessary and will try toacquire more latches than necessary as a result.
    2. Significant wait time for this waittype may suggest disk I/O subsystem issues.
  5. satya Moderator

    Dzuberts
    Welcome to the forums.
    I believe the buffer pools allocation depends on the system configuration such as memory and 64 or 32 bit. BOL refers that:
    When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free physical memory. Under Microsoft Windows 2000, SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. Maintaining this free memory prevents Windows 2000 from paging.
    If there is less memory free, SQL Server releases memory to Windows 2000. If there is more memory free, SQL Server allocates memory to the buffer pool. SQL Server adds memory to the buffer pool only when its workload requires more memory; a server at rest does not increase the size of its buffer pool.
    This leads to Lock Pages in Memory, see http://blogs.technet.com/b/askperf/...ck-pages-in-memory-do-you-really-need-it.aspx blog post also.
    By any means you have to closely follow the Disk IO and Memory in order to take advantage of performance when heavy usage of databases data & log files usage. Also placing the data, log and tempdb files are important.
    I would love to hear back about your problem on these waits, any issue post the scenario back here.
  6. dzuberts New Member

    Hello Satya:
    I've another item that might help shed some light. We run 64-bit Enterprise (9.00.4053.00) on NT 5.2 (3790) on a NUMA box with 4 dual core AMD64 processors. The 'memory' property for SQL is set as MIN=0, MAX-59440 .
    When I query sys.dm_os_sys_info it indicates that the server has 64GB (which is true), but only 7MB (that's right 'Mega') is indicated for the Buffer Pool committed, commit_target, and visible. In other words, almost nothing of that 64GB is used by the buffer pool -- and the values do not appear to change as the load on the server changes. By the way, this is 'mostly' a warehouse server although occassional index defrags are run thoughout the day on special occassions.
    I don't know if that bit of info provides any addtional clue as to what is causing the waits -- but if you've got further thoughts, please share them.
  7. satya Moderator

    What was the outcome of DBCC MEMORYSTATUS - http://support.microsoft.com/kb/907877 fyi.
    Do you see those values at all the times?
    Are you monitoring the SQL instance regularly in specific to Memory, CPU & DIsk usage?
    I don't say that your system do not have memory issues, but we cannot rule out the situations values supported from DMVs aren't reflecting the actual situation. The default action of a dynamic memory setting would be the internal physical memory pressure due to changed memory settings or the distribution of components such as reserved and stolen pages from tbe buffer pool. This might happen due a poor performing query or a parameterized adhoc query execution.
    See this http://blogs.msdn.com/b/psssql/arch...s-sql-server-2008-numa-and-foreign-pages.aspx about NUMA & foreign pages that may cause memory contention.
    Just a reference on troubleshooting memory http://download.microsoft.com/downl...0A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx issues.
    Edit:
    This http://blogs.msdn.com/b/psssql/arch...s-bob-dorr-s-sql-server-i-o-presentation.aspx link too for information.

Share This Page