Our previous DBA created a job that automatically rebuilds all indexes for all databases. This is overkill, I want to move to only rebuilding those that need it. I'm also thinking some might reorganize faster than they would rebuild. I am seeing an inordinate number of plans recompiling execution plans around the same time that this maintenance task runs. I'm thinking the two are related. Here's the question: If you rebuild an index, does that automatically cause the execution plans that used that index to recompile?
Yes. Damn. Need to add more content in order to post this response. For alternative / more intelligent rebuild / reorganise index scripts, check out [Ola Hallengren] or [Michelle Ufford] :
If you would like an awesome script that will rebuild or reorganize your indexes depending on the amount of fragmentation then you should check out the [INDEX DEFRAG SCRIPT](
www.sqlfool.com) by Michelle Ufford. She has spent a great deal of time perfecting this script. Search her site for Index Defrag, listed under Performance & Tuning. As for the recompile of the execution plans, aren't execution plans bases on statistics? Does your maintenance task also update your stats?
Rebuilding an index used by the execution plan of a sproc will cause a recompile - the stats for the index are refreshed (same as doing an UPDATE STATISTIC WITH FULLSCAN), this will mark the current execution plan as invalid and trigger a recompile for the sproc the next time it is run. This is due to the fact that an execution plan using the new stats could produce a totally different plan to the one previously in the plan cache.