Post reply

PageIOLatch_SH

  • August 17, 2009 at 2:54 pm

    #385202

    Hi,

    All of a sudden we are getting alot of PageIoLatch_SH waits for all the queries on one of our servers. What we figured out is DISK IO usage was very high when we get these waits, but we dont understand what is causing these high disk IOs. One thing is sure that disk IOs are not high becuase of these queries infact its the other way around. Because these queries were running fine and return data under one second most of the times till we start getting these high disk IOs today.

    Any help will be highly appreciated.

    Thanks,

    Usman

  • August 17, 2009 at 3:08 pm

    #1040526

    You may want to check what other jobs are running on the server at the same time. We had cases of Antivirus SW, Backup jobs etc contributed to high IO

    Regards,Yelena Varsha

  • August 17, 2009 at 3:18 pm

    #1040535

    Thanks for replying Yelena, i have already checked that and its not caused by any other job or antivirus atleast.

  • August 17, 2009 at 3:37 pm

    #1040549

    High IOs can also be caused by low physical memory, check if something memory-intensive is running, in this case paging can be a reason for the IO activity.

    Regards,Yelena Varsha

  • August 18, 2009 at 9:00 am

    #1040940

    didn't find anything memory intensive, everything looks normal on that side.

    Now on the web page side we are getting "Data provider or other service returned an E_FAIL status" error due to disk congestion.

  • August 18, 2009 at 2:29 pm

    #1041172

    EDIT: Nevermind, after re-reading I'm not sure what the problem is. Maybe someone else does or there could be a problem with your disk, or something?

  • August 19, 2009 at 4:04 am

    #1041367

    Hi Usman,

    another cause of this could be out of date statistics or badly fragmented indexes. Do you regularly perform maintenance on these databases?

    There are various built-in functions in SQL Server that you can use to determine whether a specific database has high I/O's. Which version of SQL are you running (2000, 2005, 2008)?

  • August 19, 2009 at 5:06 am

    #1041399

    Just to make sure. Did you check the SQL logs. Also check that you have sufficiant space left on all the databases in the server. Especialy check the log files and temp DB. To check IO generated by SQL use:

    select spid,physical_io fio

    into #f

    from sysprocesses where spid > 50

    waitfor delay '00:00:02'

    select s.spid,fio,physical_io,physical_io-fio used

    from sysprocesses s join #f on s.spid = #f.spid

    where s.spid > 50

    order by physical_io - fio desc

    drop table #f

    This will indicate the io used by SQL user processes.

    If you still have a problem please let me know

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
    Do not reinvent the wheel.
    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

  • August 19, 2009 at 7:39 am

    #1041508

    Thanks for your response guys, but i have resolved the issue. There was a missing INCLUDED column in an index (how stupid i am).

Viewing 9 posts - 1 through 8 (of 8 total)

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