Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Costly update trigger -70,000,000 logical reads for 30,000 rows updated! RE: Costly update trigger -70,000,000 logical reads for 30,000 rows updated!

  • January 31, 2009 at 8:26 pm

    #936087

    Marios Philippopoulos (1/31/2009)

    I have a trigger that appears to execute a very large number of logical reads; on one occasion it has hit 73,421,728 logical reads!

    There are two puzzling aspects about this:

    (1) Only about 30,000 rows are being updated in the base table (as can be seen in the exec plan)

    (2) The dominant operator (86% of total workload) is an index seek on an index that contains a seemingly unrelated column (a column that does not appear anywhere in the trigger body).

    I have captured the number of logical reads and the execution plan by polling the sys.dm_db_exec_requests DMV once a minute.

    How can such a huge number of logical reads of the trigger be reconciled to the relatively modest number of rows updated in the underlying table?

    This sounds like an accidental cross join or triangular join between two tables... you need to double check and see how many rows are in each condtion that will satisfy the join. You may be missing a join column or simply have not anticipated a possible many to many relationship.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)