Add a SQL Server index, but how can I recompile only the affected stored procedures?

Question

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

EDIT:

from SQL Server 2005 Books Online, recompiling stored procedures:

Because the database is modified by actions such as adding indexes or changing data in indexed columns, the original query plans used to access its tables must be optimized again by recompiling them. This optimization occurs automatically when you first start the stored procedure after restarting Microsoft SQL Server 2005. This also happens if the underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure can be useful, optimization will not occur until the next time the stored procedure is started after the restart of Microsoft SQL Server. In this situation, it may be useful to force the stored procedure to recompile the next time it executes

Another reason why forced recompilation of a stored procedure is to counteract, as necessary, the behavior of compiling stored procedures "sniffing parameters". When SQL Server executes stored procedures, any parameter values ​​used by the procedure during compilation are included as part of generating the query plan. If these values ​​are typical with which the procedure is subsequently called, then the stored procedure benefits from the query plan each time it is compiled and executed. If not, performance may suffer.

Answer 1

You can remove sp_recompile and specify the name of the table you just indexed. all procs that depend on this table will be deleted from the stored proc cache and will be “compiled” the next time

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

Answer 2

They are usually recompiled automatically. I probably don’t know if this is guaranteed, but this is what I observed - if you change (for example, add an index) the objects referenced by sproc, then it will recompile.

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 will show the table scan as expected.

 create index mytablei on mytable(i) exec sp1 

The plan has changed to index search.

EDIT: ok I came up with a query that works - it gives you all the sproc names that have a link to this table in the plan cache. You can associate the sproc name with the sp_recompile syntax to generate a group of sp_recompile statements that you can 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 3

I believe that stored procedures that could potentially affect the presence of this index will automatically have a new generated query plan, provided that the option to automatically generate statistics is enabled.

See “Recompiling Execution Plans” for details on which events trigger automatic recompilation.

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