re-Index advice

We periodically rebuild our indexes when we notice defragmentation of
indexes however after rebuilding the indexes we do not recompile the
stored procedure. My experience has been that after rebuiling the
indexes we ususlly notice a performance gain right away however I was
wondering if it is also necessary to recompile all our stored
procedures after the indexes are rebuilt. What is the best practise?
ThanksWhen you reindex an index it will by default update the stats which
invalidate the plans for anything that references them. That in turn will
force the recompile of the plan the next time it is used. So in short you do
not need to recompile after an index rebuild as it is already done for you.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154619914.120880.28460@.b28g2000cwb.googlegroups.com...
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>|||shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks
>
Defragging the indexes shouldn't require recompiling anything, unless
the indexes were so badly fragmented that the optimize was ignoring them
and using a sub-optimal execution plan. I personally have never had to
do this.
You don't say how you "notice" fragmentation, but you might consider an
automated approach that runs nightly or weekly. See my site for one
possible method:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Andrew that makes sense. What would be the best way to check to
make sure that the stored proc's are indeed getting recompiled?
Thanks
shub wrote:
> We periodically rebuild our indexes when we notice defragmentation of
> indexes however after rebuilding the indexes we do not recompile the
> stored procedure. My experience has been that after rebuiling the
> indexes we ususlly notice a performance gain right away however I was
> wondering if it is also necessary to recompile all our stored
> procedures after the indexes are rebuilt. What is the best practise?
> Thanks|||Well it is not something one generally has to check on. If you know the
stats are getting updated (you can see with DBCC SHOWSTTISTICS) then you can
count on any plans that reference those stats to get recompiled the next
time they are run. This is true in all cases unless you have used the KEEP
PLAN or KEEP FIXED PLAN hints in the query.
Andrew J. Kelly SQL MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1154694889.877119.181210@.p79g2000cwp.googlegroups.com...
> Thanks Andrew that makes sense. What would be the best way to check to
> make sure that the stored proc's are indeed getting recompiled?
> Thanks
>
> shub wrote:
>