在 SQL Server 7.0 中创建存储过程时，过程的文本存储在 syscomments 系统表中。 在用户执行过程时，如果过程还不在高速缓存中，SQL Server 将从 syscomments（在早期版的 SQL Server 中为 sysprocedures）中加载过程，并编译查询计划。 已编译的计划保存在高速缓存中，并在以后调用存储过程时再次使用，直到发生某个操作使计划无效并强制进行重新编译为止。 下列操作可能会引起存储过程计划的重新编译。

• 在 CREATE PROCEDURE 或 EXECUTE 语句中使用 WITH RECOMPILE 子句。
• 对任何过程所引用对象的架构进行更改，包括添加或取消约束、默认值或规则。
• 对过程所引用的表运行sp_recompile。
• 恢复包含过程或过程引用的任意对象的数据库（如果执行的是跨数据库操作）。
• 太多的服务器活动引起计划在高速缓存中超时。

重新编译存储过程的所有这些原因在早期版本中确实存在，并导致在开始执行过程前重新编译过程。 在 SQL Server 7.0 中，引入了一个新功能，该功能使得可以在执行期间重新编译存储过程。 这种新功能可保证优化器对过程内的每个特定语句总是有最可行的计划。 下列事件可能会引起存储过程的运行时重新编译：

• 存储过程引用的表中的大部分数据发生了更改，SQL Server 将重新编译存储过程以确保它有一个基于最新统计数据的计划。
• 过程交错执行数据定义语言 (DDL) 和数据操作语言 (DML) 操作。
• 过程在临时表上执行特定的操作。

在某些情况下，重新编译存储过程的开销远远大于所带来好处，对于大型过程尤其如此。 切记：启动重新编译时，整个批处理或过程均进行重新编译。 这意味着性能的下降直接与过程或批处理的大小成比例。

事件探查器

SQL Server 事件探查器包括 SP:Recompile 事件，可以利用这个事件监控重新编译发生的次数。 只要在执行期间发生存储过程重新编译，就会发生SP:Recompile 事件。

对数据库使用事件探测器

• 菜单路径：开始--程序--Microsoft SQL Server 2008--性能工具--SQL Server Profiler
• 文件--新建跟踪：新建指定数据库的跟踪

Stored Procedures的事件

• SP:Recompile 事件类指示存储过程、触发器或用户定义的函数已被重新编译。 此事件类报告的重新编译在语句级上发生。
• SP:Starting 事件类指示存储过程将要开始执行。
• SP:StmtStarting事件类指示已开始执行存储过程中的 Transact-SQL 语句。
• SP:Completed 事件类指明存储过程已执行完毕。
• SP:StmtCompleted 事件类指示存储过程中的 Transact-SQL 语句已完成。

根据 Event Class 对“事件探查器”跟踪进行分组：

• 在文件菜单上，单击属性。

• 在事件选项选项卡上，只有已经被选择的事件才会被显示。如果想显示所有可供选择的事件，则只需选中“显示所有事件”单选框。要添加一个跟踪事件，在Event列中查找一个事件类下的事件，并单击其左边的检查框；要删除不需要的事件，取消选中的事件选择框。

  
  
  
  

  而在这里我们要添加的Events名为Stored Procedures(存储过程)

• 单击确定。

检查 SP:Recompile 事件个数：
  可以展开 SP:Recompile 组以查看每次发生重新编译时的详细信息。事件的 Text 列指出重新编译的存储过程名称。
  如果多个过程正在引发重新编译事件，则按发生的次数进行存储。如果有大量的 SP:Recompile 事件，而且此时 CPU 占用率很高，则集中解决重新编译次数最多的过程。 如果没有看到任何 SP:Recompile 事件，但是仍然遇到性能问题，则要去解决SQL Server 中的应用程序性能问题。

找出引发重新编译事件的语句：

• 在文件菜单上，单击属性。
• 在 Data Columns 选项卡上，使用向下按钮删除 Groups 标题下的所有其它列。
• 在 Events 选项卡上，删除除 SP:Starting、SP:StmtStarting、SP:Recompile 和 SP:Completed 之外的所有其它事件。 如果不捕获 SP:StmtStarting 事件，可以替换为 SP:StmtCompleted 事件，但不能同时包含这两个事件，因为这样会将需要查询的信息量加倍。
• 如果已经识别出要检查的存储过程重新编译的特定实例，则通过使用 Filters 选项卡，可以将查看的数据限定为事件发生时的特定 SPID(用于该事件的SQL Server进程标识符)和时间范围。
• 单击确定。

SP:Recompile 事件将在发生存储过程语句引起重新编译的 SP:StmtStarting事件之后立即发生。 重新编译事件完成后，将会看到 SP:StmtStarting事件重新出现，表明正在使用新生成的计划执行语句。

请考虑下列示例：

use pubs
go
drop procedure RecompProc 
go
create procedure RecompProc as
create table #t (a int)
select * from #t
go
exec RecompProc

如果在 Query Analyzer 中执行该代码并在“事件探查器”跟踪中查看上述事件，将看到下列序列：

您可以立即指出引起重新编译的语句是：

select * from #t

因为它出现在 SP:Recompile 事件的前后。
  如果您仅捕获 SP:StmtCompleted 事件，而没有捕获 SP:StmtStarting 事件，SP:Recompile 事件将直接出现在引发该事件的语句之前，如下所示：

可以看到 SP:Recompile 事件在 select * from #t 语句的 SP:StmtCompleted 事件之前发生，从而引起了重新编译。 这是有道理的，因为直到为重新编译生成了新查询计划之后，才能完成该语句。

由于行修改引起的重新编译

自从生成原始查询计划以来，如果由存储过程所引用的表中的大多数数据已发生更改，SQL Server 将重新编译存储过程以确保它有一个基于最新统计数据的计划。 例如，请考虑下列存储过程

drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications

对于 RowModifications 过程的第二次执行，在“事件探查器”中将看到下列事件：

使用以下方法消除由于行修改引起的重新编译：

• 使用 sp_executesql 执行语句。
    这是首选的方法。使用 sp_executesql 存储过程执行的语句并没有作为存储过程计划的一部分进行编译。 因此，执行该语句时，SQL Server 将可以随意选择使用语句高速缓存中的现有计划，或者在运行时创建新的计划。 不管哪一种情况下，正在调用的存储过程的计划不会受到影响，因而无须进行重新编译。
    EXECUTE 语句有同样的效果,但是并不推荐使用它。使用EXECUTE 语句的效率没有使用 sp_executesql 的高，因为它不允许进行参数化查询。
    上述所给出的 RowModifications 过程可以使用 sp_executesql 进行编写，如下所示：

drop procedure RowModifications2
go
create procedure RowModifications2 as
set nocount on
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
exec sp_executesql N'select count(*) from #t where a = @a', 
                   N'@a int', @a =  37
go
exec RowModifications2
exec RowModifications2

• 使用子过程执行引起重新编译的语句。
    在这种情况下，该语句可能还会引起重新编译，但不是重新编译大型调用存储过程，而只是重新编译小型子过程。
• 使用 KEEP PLAN 选项。
    临时表对于重新编译有特殊规则，在某些情况下，这些规则比默认的重新编译算法要严格得多。 可以使用 KEEP PLAN 选项将临时表的阈值放宽到默认算法。

由于交错执行数据定义语句 (DDL) 和数据操作语句 (DML) 操作引起重新编译

如果 DDL 操作是在一个过程或批处理内执行的，则过程或批处理在遇到随之发生的影响 DDL 中涉及的表的第一个 DML 操作时将进行重新编译。
  请考虑下列示例存储过程：

drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table #t1 (a int)
-- DML
select * from #t1
-- DDL
create index idx_#t1 on #t1(a)
-- DML
select * from #t1
-- DDL
create table #t2 (a int)
-- DML
select * from #t2
go
exec Interleave

如果在 Query Analyzer 中执行该代码并在“事件探查器”跟踪中查看上述事件，将看到下列序列：

  在这种情况下，存储过程在执行期间重新编译三次。 要了解发生这种情况的原因，请考虑优化器为存储过程开发计划的方式：
1、在过程的初始编译过程中，并不存在临时表 #t1 和 #t2。 因此，没有创建引用这些表的查询的计划。 这些计划必须是在执行期间生成的。
2、在过程第一次执行时，第一步是创建表 #t1。 第二步是从表 #t1 中进行选择，该选择还没有计划。 因此，此时重新编译过程来为 SELECT 语句生成计划。 为表 #t1 中的当前选择生成计划，也为创建索引后表 #t1 中的选择生成计划。 没有为表 #t2 中的选择生成计划，因为表 #t2 还不存在。
3、下一步是对表 #t1 创建索引。 随后，在表 #t1 上执行另一个选择，现在它有来自第一次重新编译的一个计划。 但是，因为表 #t1 的架构自从生成计划后已更改，因此必须再次重新编译过程来为表 #t1 中的选择生成新计划。 由于表 #t2 还不存在，还不可以为表 #t2 中的选择生成计划。
4、下一步，创建表 #t2，并执行表 #t2 中的选择。 由于没有用于该语句的计划，最终一次重新编译过程。

在每次执行存储过程时都发生这些重新编译。 要减少重新编译的次数，请将过程修改为先执行所有 DDL 操作，然后接着执行 DML 操作

由于某些临时表操作引起的重新

在存储过程中使用临时表可能会导致在每次执行过程时重新编译存储过程。为避免这种情况，请更改存储过程以符合下列要求：

• 创建临时表（如 CREATE TABLE 或 SELECT... INTO）的语句不出现在流控制语句如 IF... ELSE 或 WHILE 中。
• 包含任意临时表名称的所有语句出现在引用临时表的任何 DROP TABLE 语句之前。在存储过程中创建的临时表不需要 DROP TABLE 语句，在过程执行完毕时，这些表自动消失。

使用 KEEP PLAN 选项避免重新编译

在存储过程中使用临时表使得查询优化程序变得有些复杂。 表的行计数和统计信息在存储过程执行的整个期间变化非常大。 为确保优化器在所有涉及临时表的情况下使用最优计划，已开发了一种特殊算法，该算法对于重新编译更具有主动性。 该算法的原理是：如果对存储过程创建的临时表已更改六次以上，将在下一个语句引用临时表时重新编译过程。

请考虑下列示例：

drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
-- 使用KEEP PLAN
option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan

最优准则

当作为非所有者执行某个过程时，最好完全限定存储过程名称。这样做会更明确且更易于当前用户重新使用现有执行计划。 例如，如果一个非数据库所有者 (dbo) 用户执行了数据库所有者 (dbo) 所拥有的 pubs 数据库中的存储过程（在本例中为 myProc），则应当使用下面的语句：

exec pubs.dbo.myProc

这种技术从编码和维护的角度，消除了不同所有者使用不同过程版本的混乱状况，同时也允许 SQL Server 更直接地访问特定过程的执行计划。