As a database is changed by such events as adding indexes, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a procedure is run after SQL Server is restarted. It also happens if an underlying table used by the procedure changes. But if a new index is added that the procedure might benefit from, optimization does not automatically happen (until, for example, the next time SQL Server is restarted and the procedure is run). The sp_recompile system procedure forces a recompile of a procedure the next time it's run. The following example marks every stored procedure and trigger that accesses the titles table so that they are recompiled the next time they are executed:
sp_recompile titles