Post reply

PAGEIOLATCH_SH

  • May 10, 2005 at 7:11 am

    #86531

    Hi,

    I'm running a DTS job that is archiving audit information and this amounts to 27 million rows...

    I am getting PAGEIOLATCH_SH in the process info screen.

    I have checked blocking and I have checked the performance monitor.

    I am getting a PAG lock which won't go.

    Should I stop the job or is this likely to clear but take some time.

    Thanks...Graeme

  • May 10, 2005 at 8:09 am

    #557791

    Long PAGEIOLATCH waittype mostly indicates I/O subsystem bottleneck, Check whether you have high disk queue length from perfmon.

  • May 10, 2005 at 8:20 am

    #557794

    Yes thanks Allen, my investigations have pointed towards this

    The AVg Qu Length was about normal and the processor is not working too hard...the available MBytes was quite low. Would this suggest changing memory configuration on the sql server.

    I tried selecting records for a table with 9 million rows and I got a NETWORKIO wait type...and again it was taking ages....I'm a little confused now.

    Any thoughts ?

    Regards..Graeme

  • May 10, 2005 at 8:38 am

    #557800

    In addition to add more memory, you may also consider

    1. How do you configure the disk system, what type RAID do you have?

    2. How do you allocate the database and tempdb?

    3. Change the archive process to small batch on daily basis?

    In order to reduce NETWORKIO wait type you are experiencing, you have to process the data in serevr side and just return the result you need.

  • May 10, 2005 at 8:42 am

    #557804

    Many Thanks Allen.

    Graeme

  • April 17, 2007 at 8:03 am

    #700919

    Allen,

    Is it possible that we see large physical disk io queues if sql is using more number of threads to perform the lookups from the tables than the sub system can handle at once?

    If so, how would we rectify the configuration? what is a sane limit to set for "max worker threads"?

    S

  • April 18, 2007 at 12:12 pm

    #701214

    It is recommended to leave the "max worker threads" setting alone. However...

    http://msdn2.microsoft.com/en-us/library/ms187024.aspx

Viewing 7 posts - 1 through 6 (of 6 total)

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