SQL Server Performance

SP with PAGEIOLATCH_SH while running

Discussion in 'Performance Tuning for DBAs' started by khchan, Jan 5, 2004.

  1. khchan New Member

    Hi all,

    I'm facing a problem of PAGEIOLATCH_SH while running a SP. The SP runs daily to update records in a table.

    Let says, I have two tables: APPLICATION, CUSTOMER. The SP reads from the two tables to find out some 'Outdated' applications, as well we the customer ID. The it stored the data into a temp table TEMP_APP. Then it reads from TEMP_APP, a cursor is used to store the data and CUSTOMER table is updated while looping the cursor.

    We must update the CUSTOMER record one-by-one since each record is updated with different values. The SP reads and updates around 10000 records.

    From PERMON, I could see % Processor Time hits nearly 100% as the Processor Queue Length increase. However, I can't see any CPU bottleneck as it is already a PIII 733 CPU and the number of records affected is so small. Other stat such as Disk Qeueue Length/ Read Writes and Buffer Cache Hit Ratio are normal.

    Can anyone help me?

    Thanks!
  2. satya Moderator

    How about memory settings on SQL?

    Contention can be caused by issues other than I/O or memory performance; for example, heavy concurrent inserts into the same index range can cause this type of contention.

    Any read accesses to the same range as the inserts would also conflict on the latches. The solution in these cases is to distribute the inserts using a more appropriate.

    Consequently, latch duration is normally sensitive to available memory.

    Also review PERFMON information for memory & process counters.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. khchan New Member

    Hi Satya,

    As I said, the SP will update the CUSTOMER table one-by-one so it issue around 10000 UPDATE statements. After all, the updates are committed, or rollbacked if error. Do you think it is caused by the concurrent updates? What is I narrow down the number of records to <5000?

    Besides, what figure shall I see for memory & process counter? The available bytes drops once the SP start, but stays around 5000000. For process counter, I dont' have the data.
  4. satya Moderator

    As mentioned it is caused by concurrent updates and it depends on the memory available while processing the transaction.

    Just capture the counters for assessment and is there any chance of upgrading physical memory onthe box?

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Luis Martin Moderator

    How about Physical Disk Queue length?.
    BTW: What version of SQL and Sp?


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. satya Moderator

    True it also relates that SQL Server is waiting for reading a I/O page. Please check if there is any disk I/O bottleneck exists.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. ChrisFretwell New Member

    When you say you must update one-by-one because each is updated differently, you may still be able to do it without a cursor. How are they different, how do you determine the differences etc?

    Genearally, I only use cursors if I need to traverse the data in a specific order, where what I do with one record depends on what came before or comes after. Different updates based on assorted criteria can usually (not always) be done with case statements or a couple of different update statements with different where clauses.

    So, even if you need to update them somewhat differently, do you need to do it in specific order? Or some other set of criteria?

    Chris
  8. khchan New Member

    Physical Disk Read/Write are both 0. Physical Query Lenght is also 0. Its SQL Server 7 SP3 on Win NT Server SP6.


    quote:Originally posted by LuisMartin

    How about Physical Disk Queue length?.
    BTW: What version of SQL and Sp?


    quote:Originally posted by satya

    True it also relates that SQL Server is waiting for reading a I/O page. Please check if there is any disk I/O bottleneck exists.

    My case is that:

    APPLICATION:
    APPID / DATE
    1 / 2003-12-01
    2 / 2003-12-02
    3 / 2004-01-01

    CUSTOMER:

    before update:
    APPID / CUSTID / NAME / NO
    1 / 1 / STEVEN / A123
    1 / 2 / ROBERT / A456
    1 / 4 / PETER / A789
    2 / 3 / SANDY / B123
    2 / 5 / DON / B234.....

    after update:
    APPID / CUSTID / NAME / NO
    1 / 1 / STEVEN / C1
    1 / 2 / ROBERT / C2
    1 / 4 / PETER / C3
    2 / 3 / SANDY / C1
    2 / 5 / DON / C2...

    UPDATE CUSTOMER SET NO = C1 WHERE NAME = STEVEN

    The update should be by APPLICATIONS, ie for APPLICATION 1, the CUSTOMERS are updated with C1, C2, C3.... for APPLICATION 2, C1, C2.....

    The update criteria is APPLICATION DATE < 2004-01-01. And the two tables are joined by APPID, which is the PK and FK.

    How can I update all records in one statement?
  9. satya Moderator

    Any triggers & indexes involved and depending on current situation you may defer to update all records at once. Better to differentiate or perform in transaction mode to avoid any blocking.

    Test and apply SP4 to the server as few fixes may solve the issue.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Twan New Member

    Hi ya,

    >>>
    Physical Disk Read/Write are both 0. Physical Query Lenght is also 0. Its SQL Server 7 SP3 on Win NT Server SP6.
    >>>

    This is probably because the disk counters are turned off by default in NT. You need to switch them on using the command line tool diskperf, and rebooting the machine

    The following would do it in one statement, assuming the the incrementing of the NO column is done based on the order of custid. you'd need to join the application table into the select to allow the criteria application_date < 'date' to be included, I've left it off for simplicity. Also the select could be inserted into another temp variable/table, but this is likely to be slightly slower



    declare @test table(
    APPID int,
    CUSTID int,
    NAME varchar(30),
    NO varchar(30)
    )

    insert into @test values (1, 1 , 'STEVEN' , 'A' )
    insert into @test values (1, 2, 'ROBERT', 'B' )
    insert into @test values (1, 4, 'PETER', 'C' )
    insert into @test values (2, 3, 'SANDY', 'D' )
    insert into @test values (2, 5, 'DON', 'E' )

    update t
    set NO = tu.NO
    from@test t,
    (selectt.appid,
    t.custid,
    'C' + convert( varchar(10), count(*) ) as NO
    from@test t,
    @test t1
    wheret.appid = t1.appid
    andt.custid >= t1.custid
    group by t.appid, t.custid
    ) tu
    wheret.appid = tu.appid
    andt.custid = tu.custid

    select * from @test

    Cheers
    Twan

Share This Page