none
PAGEIOLATCH_SH locks

    Pergunta

  • Hi

    I am submitting PAGEIOLATCH_SH to perform an update to a table, creating locks of other sessions (many). the question is:

    How do I manage this situation? or How can I reduce these locks?

    S.O. Windows 2k3

    B.D  SQL Server 2005



    thanks

    regards


    Mauricio Mejia


    • Editado Mauricio Mejia quinta-feira, 7 de junho de 2012 13:12 Plataform Information
    quinta-feira, 7 de junho de 2012 13:11

Respostas

  • Hi can you tell us more about the process that you are trying to perform that is responsible for this wait type?

    As others have mentioned, the wait type PAGEIOLATCH_SH occurs when SQL Server is waiting on a latch for a buffer that is in an IO request. It's possible that the process responsible could be inefficient and as such is performing intensive IO operations such as table/Index scans.

    I would recommend looking into the performance of the responsible process/operations, by looking at the executions plans involved, before looking to the disk subsystem for further investigation.

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Thank's u Jhon,

    Very great appreciation of the problem, I will verify from that point of view the situation, unfortunately I do not control the application because it is administered by a third party, I will send relevant reports to those responsible for looking more deeply explored.

    Thanks again for your time and gentle cooperation

      (sorry for my English)


    Mauricio Mejia

    • Marcado como Resposta Mauricio Mejia sexta-feira, 15 de junho de 2012 16:46
    sexta-feira, 15 de junho de 2012 13:38

Todas as Respostas

  • >>>>or How can I reduce these locks?

    How many rows does it update? Do you have properly defined indexes?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    quinta-feira, 7 de junho de 2012 13:13
  • well

    I have only the administration of the database, the application is administered by a third party, the information has been provided to the owner of the application, however I feel that one of my disks may be causing the problem.

    regards

    Mauricio Mejia

    sexta-feira, 8 de junho de 2012 13:17
  • PAGEIOLATCH_SH Occurs when a task is waiting for a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits of this kind indicate a problem with the disk subsystem that is corelated to PAGEIOLATCH_X, Latches are short term light weight synchronization objects. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, and so on. Therefore, the duration of latches is typically sensitive to available memory. If this is significant in percentage, it typically indicates cache contention.

    Please check the for number of rows updating in one batch. If no if updates are high then you can split it into small batches. Along with that we need to check memory setting of sql server & performance of disk system installed

    sexta-feira, 15 de junho de 2012 01:49
  • Hi can you tell us more about the process that you are trying to perform that is responsible for this wait type?

    As others have mentioned, the wait type PAGEIOLATCH_SH occurs when SQL Server is waiting on a latch for a buffer that is in an IO request. It's possible that the process responsible could be inefficient and as such is performing intensive IO operations such as table/Index scans.

    I would recommend looking into the performance of the responsible process/operations, by looking at the executions plans involved, before looking to the disk subsystem for further investigation.

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    sexta-feira, 15 de junho de 2012 06:45
  • Hi can you tell us more about the process that you are trying to perform that is responsible for this wait type?

    As others have mentioned, the wait type PAGEIOLATCH_SH occurs when SQL Server is waiting on a latch for a buffer that is in an IO request. It's possible that the process responsible could be inefficient and as such is performing intensive IO operations such as table/Index scans.

    I would recommend looking into the performance of the responsible process/operations, by looking at the executions plans involved, before looking to the disk subsystem for further investigation.

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Thank's u Jhon,

    Very great appreciation of the problem, I will verify from that point of view the situation, unfortunately I do not control the application because it is administered by a third party, I will send relevant reports to those responsible for looking more deeply explored.

    Thanks again for your time and gentle cooperation

      (sorry for my English)


    Mauricio Mejia

    • Marcado como Resposta Mauricio Mejia sexta-feira, 15 de junho de 2012 16:46
    sexta-feira, 15 de junho de 2012 13:38