Post reply

Does alter index rebuild cause sp recompile?

  • December 14, 2011 at 12:31 pm

    #391129

    Hi All,

    Below link states that "if an underlying table that is used by the stored procedure changes" then there will be a recompile. Does that apply to index rebuilding?

    http://msdn.microsoft.com/en-us/library/ms190439.aspx states

  • December 14, 2011 at 12:43 pm

    #1421547

    Yes. If the plan used the index and the index was rebuild, the plan must recompile on next execution.

    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

  • December 14, 2011 at 12:53 pm

    #1421558

    Just curious, is this since associated statistics is updated?


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • December 14, 2011 at 1:27 pm

    #1421579

    okbangas (12/14/2011)

    Just curious, is this since associated statistics is updated?

    I can't remember if it gets flagged as a schema modification recompile or a statistics-based recompile. It's easy to test though, iirc it's the eventsubclass column in Profiler.

    http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

    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

  • December 14, 2011 at 1:31 pm

    #1421584

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles,

    what matters what happens first?

  • December 14, 2011 at 2:14 pm

    #1421616

    sroumel (12/14/2011)

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles

    Rebuild doesn't trigger update stats, it does the update as part of the index rebuild, plans will be found to be invalid next time they run and hence recompile.

    It matters, because there are query hints that make plans remain valid even if the stats are updated, but a schema change will still invalidate them.

    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

  • December 14, 2011 at 3:17 pm

    #1421657

    GilaMonster (12/14/2011)

    sroumel (12/14/2011)

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles

    Rebuild doesn't trigger update stats, it does the update as part of the index rebuild, plans will be found to be invalid next time they run and hence recompile.

    It matters, because there are query hints that make plans remain valid even if the stats are updated, but a schema change will still invalidate them.

    Does the recompile also apply to a rebuild of indexes which occurs for specific partitions?

  • December 14, 2011 at 3:23 pm

    #1421663

    Lexa (12/14/2011)

    GilaMonster (12/14/2011)

    sroumel (12/14/2011)

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles

    Rebuild doesn't trigger update stats, it does the update as part of the index rebuild, plans will be found to be invalid next time they run and hence recompile.

    It matters, because there are query hints that make plans remain valid even if the stats are updated, but a schema change will still invalidate them.

    Does the recompile also apply to a rebuild of indexes which occurs for specific partitions?

    Errr, probably, but that you'll have to test. See the blog post I referenced above for a way to test out recompiles.

    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

  • December 14, 2011 at 3:41 pm

    #1421669

    GilaMonster (12/14/2011)

    Lexa (12/14/2011)

    GilaMonster (12/14/2011)

    sroumel (12/14/2011)

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles

    Rebuild doesn't trigger update stats, it does the update as part of the index rebuild, plans will be found to be invalid next time they run and hence recompile.

    It matters, because there are query hints that make plans remain valid even if the stats are updated, but a schema change will still invalidate them.

    Does the recompile also apply to a rebuild of indexes which occurs for specific partitions?

    Errr, probably, but that you'll have to test. See the blog post I referenced above for a way to test out recompiles.

    Thanks.

  • December 15, 2011 at 2:51 am

    #1421796

    Thanks for the information.Metaburn[/url]

  • December 15, 2011 at 9:43 am

    #1422058

    GilaMonster (12/14/2011)

    sroumel (12/14/2011)

    Maybe it doesn't matter,

    rebuild index triggers update statistics and then the plan recompiles

    Rebuild doesn't trigger update stats, it does the update as part of the index rebuild, plans will be found to be invalid next time they run and hence recompile.

    It matters, because there are query hints that make plans remain valid even if the stats are updated, but a schema change will still invalidate them.

    Excellent notice,

    i don't use query hints and it never cross my mind that you could force server to keep the plan

    Thank you very much

Viewing 11 posts - 1 through 10 (of 10 total)

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