question

shannonlowder avatar image
shannonlowder asked

Execution Plan Recompile

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?
indexingrebuildreorganize
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
ThomasRushton avatar image
ThomasRushton answered
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][1] or [Michelle Ufford][2] [1]: http://ola.hallengren.com [2]: http://www.sqlfool.com
6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Not used 2000 for over a year... THANK jesus!
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
+1 Nuts. I was going to post Michelle's script. That one is just brilliant.
1 Like 1 ·
sp_lock avatar image sp_lock commented ·
Top supporter of ola's scripts... Especially the latest index rebuild!
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
I've just had to write a similar but very cut-down version of the Reindexing script to run on SQL2k. *sigh* These things should not be required in Y2000!
0 Likes 0 ·
Tim avatar image Tim commented ·
You beat me by a few minutes. I like SQL Fool's (Michelle Ufford) script. Very modular and simple to use. I have heard of Ola's script and understand it is quite powerful.
0 Likes 0 ·
Show more comments
Tim avatar image
Tim answered
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?
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
Stats are updated on a REBUILD, but not on a REORG.
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
@TRAD, when you rebuild the index the stats of that index are rebuilt too. This should cause a recompile on procs that use that index/stats
1 Like 1 ·
Tim avatar image Tim commented ·
Thanks, I learn a bit more each and every day.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
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.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
ISTR hearing that it won't recompile immediately, but will mark the execution plan as invalid causing a rebuild of the plan at the time of next execution. I could be mis-remembering, though.
1 Like 1 ·
WilliamD avatar image WilliamD commented ·
Sorry, didn't make that clear. You are right that the plan would be marked as invalid, then recompiled at the next call. (made that clear in my answer now)
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.