Does alter index rebuild cause sp recompile?

Question

Does alter index rebuild cause sp recompile?

Lexa

Hall of Fame

Points: 3314
More actions
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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

okbangas SSChampion Points: 11773 More actions · Answer 2

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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

sroumel SSC Veteran Points: 262 More actions · Answer 4

Maybe it doesn't matter,

rebuild index triggers update statistics and then the plan recompiles,

what matters what happens first?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Lexa Hall of Fame Points: 3314 More actions · Answer 6

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Lexa Hall of Fame Points: 3314 More actions · Answer 8

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.

william.davidson Valued Member Points: 59 More actions · Answer 9

william.davidson

Valued Member

Points: 59

December 15, 2011 at 2:51 am

#1421796

Thanks for the information.Metaburn[/url]

sroumel SSC Veteran Points: 262 More actions · Answer 10

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