Post reply

Does rebuilding indexes and statistics cause recompile of execution plans?

  • May 20, 2010 at 10:24 pm

    #235376

    We run a maintenance job nightly that rebuild indexes and statistics on our production database. The tables within the database are well established so I doubt 20% of table data is changed on anything shorter than a bi-weekly basis.

    Does SQL server see a fresh index or statistics on a table and automatically force a recompile on a stored procedure if executed in order to choose the best execution plan?

    If so, are we causing the engine to work unnecessarily hard each new day?

    Keith Wiggans

  • May 20, 2010 at 11:18 pm

    #1170586

    kwiggans (5/20/2010)

    Does SQL server see a fresh index or statistics on a table and automatically force a recompile on a stored procedure if executed in order to choose the best execution plan?

    Yes, Both index rebuilds (which, by the way update the index's statistics) and stats changes invalidate cached plans based on those objects.

    There are lots of good scripts to rebuild all indexes above certain size/fragmentation level. Start with Michelle's at http://www.sqlfool.com

    As for stats, it's hard to identify the stats that go stale before the 20% update threshold for auto update. If you want to be safe, update them all. If you want to be specific and have time to investigate, remove all the manual stats update and add back updates just for those stats that get stale enough to cause bad query plans before the 20% threshold is reached.

    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

  • May 25, 2010 at 8:51 am

    #1172121

    One addition to Gail's comments: you specifically said index rebuild and then statistics updates. That is both unnecessary and wrong if you are doing it in that order. Rebuilding an index reads 100% of the data and thus you get a FULL SCAN statistics set for that index. So make sure you are only updating statistics for NON-index stats.

    I highly recommend Ola Hallengren's maintenance scripts: ola.hallengren.com

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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