add SQL Server index but how to recompile only affected stored procedures?

Question

I need to add an index to a table, and I want to recompile only/all the stored procedures that make reference to this table. Is there any quick and easy way?

EDIT:

from SQL Server 2005 Books Online, Recompiling Stored Procedures:

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server 2005 is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer

Answer 1

They are generally recompiled automatically. I guess I don't know if this is guaranteed, but it has been what I have observed - if you change (e.g. add an index) the objects referenced by the sproc then it recompiles.

create table mytable (i int identity)
insert mytable default values
go 100
create proc sp1 as select * from mytable where i = 17
go
exec sp1

If you look at the plan for this execution, it shows a table scan as expected.

create index mytablei on mytable(i)
exec sp1

The plan has changed to an index seek.

EDIT: ok I came up with a query that appears to work - this gives you all sproc names that have a reference to a given table in the plan cache. You can concatenate the sproc name with the sp_recompile syntax to generate a bunch of sp_recompile statements you can then execute.

;WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,TableRefs (SProcName, ReferencedTableName) as
(
    select
        object_name(qp.objectid) as SProcName,
        objNodes.objNode.value('@Database', 'sysname') + '.' + objNodes.objNode.value('@Schema', 'sysname') + '.' + objNodes.objNode.value('@Table', 'sysname') as ReferencedTableName 
    from sys.dm_exec_cached_plans cp
    outer apply sys.dm_exec_sql_text(cp.plan_handle) st
    outer apply sys.dm_exec_query_plan(cp.plan_handle) as qp
    outer apply qp.query_plan.nodes('//Object[@Table]') as objNodes(objNode)
    where cp.cacheobjtype = 'Compiled Plan'
    and cp.objtype = 'Proc'
)
select 
    * 
from TableRefs
where SProcName is not null
    and isnull(ReferencedTableName,'') = '[db].[schema].[table]'

Answer 2

Hi,

I believe that the stored procedures that would potentially benefit from the presence of the index in question will automatically have a new query plan generated, provided the auto generate statistics option has been enabled.

See the section entitled Recompiling Execution Plans for details of what eventualities cause an automatic recompilation.

http://technet.microsoft.com/en-us/library/ms181055(SQL.90).aspx

Answer 3

You can exceute sp_recompile and supply the table name you've just indexed. all procs that depend on that table will be flushed from the stored proc cache, and be "compiled" the next time they are executed

See this from the msdn docs: sp_recompile (Transact-SQL)