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!

  • February 2, 2009 at 7:57 am

    #936443

    GilaMonster (2/2/2009)

    Marios Philippopoulos (2/2/2009)

    Wow, that was something I didn't know, but makes sense the way you put it.

    Not that I don't believe you, but can you post a link that mentions this?

    I don't have one offhand (and I'm sitting at the airport), but it's easy to check. Open the exec plan, find any joi, seek or scan operator and look at the tooltips. If you see things like 'Actual rows', 'Actual IO cost', etc then it's an 'actual' plan with run-time information. If you don't then it's essentially an estimated plan.

    Not on this exactly, but - http://sqlinthewild.co.za/index.php/2007/09/04/execution-plans-estimated-vs-actual/

    I just looked at the Index-seek operator that accounts for 86% of the total workload of the exec plan I posted (2nd post in this thread) and it only lists Estimated values.

    I'm sold! 😉

    I will set up the trace and post my findings.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]