question on recompiles

On Monday I added a whole bunch of indexes. They were pretty much just
indexes on foreign keys. Should I have recomiled all the sp's? Since I
didn't, should I bother now? According to BOL:
But if a new index is added from which the stored procedure might benefit,
optimization does not automatically happen (until the next time the stored
procedure is run after SQL Server is restarted).
Wouldnt they also get recompiled the first time they were run, whether SQL
was restarted or not?
TIA,
ChrisR
ChrisR wrote:
> On Monday I added a whole bunch of indexes. They were pretty much just
> indexes on foreign keys. Should I have recomiled all the sp's? Since I
> didn't, should I bother now? According to BOL:
> But if a new index is added from which the stored procedure might
> benefit, optimization does not automatically happen (until the next
> time the stored procedure is run after SQL Server is restarted).
> Wouldnt they also get recompiled the first time they were run,
> whether SQL was restarted or not?
Yes, the sp will compile the first time it is run. The issue is whether
parameter sniffing is going to bite you because SQL Server decided on an
execution plan for a query before the indexes were applied. It could
continue to use the old plan even with the new index until it is
recompiled. You can flag the stored procedure for recompile using
sp_recompile. You could also use DBCC FREEPROCCACHE, but that will
affect the entire server.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Sorry David but that is not quite how it works in 2000. As soon as the
index is added any plans that reference the associated table is marked for
recompilation. So the very next time anyone tries to run that sp after the
index is created (or dropped) it will create a new plan. That new plan will
take into account the index. Whether it chooses to use it or not is up to
the optimizer but it is considered immediately after being built. So the
only things that will use the old or existing plans are ones that are in the
process of executing at the time the index is finished being created.
Andrew J. Kelly SQL MVP
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekjWeEGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> ChrisR wrote:
> Yes, the sp will compile the first time it is run. The issue is whether
> parameter sniffing is going to bite you because SQL Server decided on an
> execution plan for a query before the indexes were applied. It could
> continue to use the old plan even with the new index until it is
> recompiled. You can flag the stored procedure for recompile using
> sp_recompile. You could also use DBCC FREEPROCCACHE, but that will affect
> the entire server.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com