PAGEIOLATCH_SH blocking

  • Hi all,

    I have 2 sessions:

    Session1:

    UPDATE a

    SET Column1 = fnc_calculatecost(b.ID)

    FROM Table_A a

    JOIN Table_B b ON a.ID_B_FK = b.ID

    INSERT INTO Table_C

    Session2:

    SELECT

    FROM Table_C

    WHERE ID =

    1/In function fnc_calculatecost, I also have code

    SELECT

    FROM Table_A a

    JOIN Table_B b ON a.ID_B_FK = b.ID

    Table_A ~ 700.000 rows

    Table_B ~ 90.000 rows

    Table_C ~ 2milion rows

    2/Table_A has a foreign key ID_B_FK to Table_B(ID)

    3/Session 1 and 2 are running at the same time.

    So,I'm facing a problem PAGEIOLATCH_SH lock on Table_B table and LCK_M_IS lock on Table_C in my query.

    Any suggestions?

    Thanks,

  • PageIOLatch isn't a lock, it's a latch. Usually used when SQL is moving pages around from memory to disk or disk to memory. Probably you don't have enough cache memory for this and all other queries and the reads from disk are taking time.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/1/2013)


    PageIOLatch isn't a lock, it's a latch. Usually used when SQL is moving pages around from memory to disk or disk to memory. Probably you don't have enough cache memory for this and all other queries and the reads from disk are taking time.

    Thank for your info. As your explanation, we don't have any solution to resolve this issue. Maybe, there were many processes at the same time and there was not enough cache.

  • Read less data (optimise queries), add memory, optimise the IO subsystem

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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