Ann,

> >> When a stored procedure or trigger is compiled, are the plans for

its

> queries chosen at that time?
> >> If so, they should be recompiled after recomputing index

statistics.

> >>In that case, does anyone know how to recompile all stored

procedures

> and triggers
> [snip]
> I thought that the plans were chosen when the procedure was first
> invoked and retained by the server until the database is closed.
> And I don't know of a way to force a recompilation except to alter the
> procedure to itself.

With a view towards the future...

If the statistics for an index were to be recalculated, then it would
seem appropriate to 'mark' the SPs which reference the table as 'dirty'
to have the system recalculate the plan at the next appropriate
invocation.


Sean