添加SQL Server索引，但如何只重新编译受影响的存储过程？ [英] add SQL Server index but how to recompile only affected stored procedures?

问题描述

我需要为表添加一个索引，我想只重新编译/所有引用该表的存储过程。有没有快速简便的方法？

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?

编辑：

来自SQL Server 2005联机丛书，重新编译存储过程：

from SQL Server 2005 Books Online, Recompiling Stored Procedures:

由于通过添加索引或更改索引列中的数据等操作更改了数据库，因此应通过重新编译来再次优化用于访问其表的原始查询计划。重新启动Microsoft SQL Server 2005后，第一次运行存储过程时，将自动执行此优化。如果存储过程使用的基础表发生更改，也会发生此问题。 但是，如果添加了一个新索引，存储过程可能会从中受益，则优化不会发生，直到下次重新启动Microsoft SQL Server后运行存储过程。在这种情况下，它可能很有用强制存储过程在下次执行时重新编译

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

强制存储过程重新编译的另一个原因是在必要时抵消参数嗅探行为存储过程编译。当SQL Server执行存储过程时，过程在编译时使用的任何参数值都包含在生成查询计划的过程中。如果这些值表示随后调用过程的典型值，则每次编译和执行时，存储过程都会从查询计划中受益。如果没有，性能可能会受到影响

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

推荐答案

您可以优先考虑sp_recompile并提供刚刚编入索引的表名。所有依赖于该表的proc将从存储的proc缓存中刷新，并在下次执行时被编译

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

从msdn docs中查看：
sp_recompile（Transact-SQL）

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

这篇关于添加SQL Server索引，但如何只重新编译受影响的存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！