SQL Server 2005 中的批编译、重新编译和计划缓存问题(1)

发布时间：2014-08-01 00:00 来源：未知

一、本白皮书的目的

此白皮书的目的有几个。阐述了批处理在 SQL Server 2005 中的缓存和重用方式，并就最大限度重用已缓存的计划提供了相应的最佳实务。另外，文中还说明了一些重新编译批处理的方案，并针对减少或消除不必要的重新编译，给出了最佳实务。本白皮书阐述了 SQL Server 2005 的“语句级重新编译”功能。另外，还介绍了许多工具及实用程序，它们可在查询编译、查询重新编译、计划缓存和计划重用过程中充当很有用的观测工具。我们在整篇文章中对比了 SQL Server 2000 和 SQL Server 2005 的不同表现，以便读者能够更好地了解文中的内容。本文档所列举的语句均适用于 SQL Server 2000 和 SQL Server 2005。同时，明确指出了这两个 SQL Server 版本在行为上的差异。

本文面向三类读者：

用户：使用、维护并为 SQL Server 开发应用程序的人员。初识 SQL Server 2005 的用户及正从 SQL Server 2000 进行迁移的人员将在这里找到有用的信息。

开发人员：SQL Server 开发人员将在这里找到有用的背景信息。

测试人员和项目经理：本文档将对“SQL Server 2005 中的编译和重新编译”功能提供说明。

在查询、批处理、存储过程、触发器、被准备语句或动态 SQL 语句（以下称为“批处理”）在 SQL Server 上开始执行以前，批处理将被编译成计划。然后，将执行该计划以便发挥其效力或生成结果。

一个批处理可包含一个或多个 SELECT、INSERT、UPDATE 和 DELETE 语句、存储过程调用（T-SQL“粘连”或控制结构（比如：SET、IF、WHILE、DECLARE）可能使其产生交错）、DDL 语句（比如：CREATE、DROP）以及与权限相关的语句（比如：GRANT、DENY 和 REVOKE）。批处理还可包含 CLR 构造的定义和用法（比如：用户定义的类型、函数、过程和聚合）。

已编译的计划被保存到 SQL Server 的一部分内存中，这部分内存称为计划缓存。将搜索计划缓存以获得重用计划的机会。假如对某个批处理重用计划，就可避免编译工作。请注意，在有关 SQL Server 的文献中，过去所用的“过程缓存”一词在本文中被称为“计划缓存”。“计划缓存”用词更准确，因为计划缓存不仅仅保存存储过程的查询计划。

在涉及 SQL Server 的用语中，上段所提到的编译过程有时会被误认为是“重新编译”，但该过程仅涉及“编译”。

重新编译的定义：假设某个批处理被编译成一个或多个查询计划的集合。在 SQL Server 开始执行任何单独的查询计划之前，服务器将检查该查询计划的有效性（正确性）和最优性。假如某个检查失败了，将重新编译相应查询计划所对应的语句或整个批处理，并可能生成一个不同的查询计划。这种编译称为“重新编译”。

请特别注意，不必预先缓存该批处理的查询计划。实际上，某些批处理类型从不被缓存，但仍能引发重新编译。举个例，有个批处理包含一个大于 8 KB 的文本。假设该批处理创建了一个临时表，并在表中插入了 20 行。所插入的第七行将导致重新编译，但由于其包含的文本较大，将不缓存该批处理。

在 SQL Server 中执行的多数重新编译都是有根据的。有些是为了确保语句的正确性；另一些是为了在 SQL Server 数据库中的数据发生变化时，获得最佳的查询执行计划。然而，重新编译有时会大大延缓批处理执行的速度。这时，就有必要减少进行重新编译的次数。

在 SQL Server 2000 中重新编译批处理时，将重新编译批处理中的所有语句，而不仅仅是触发重新编译的语句。SQL Server 2005 在该行为上做了改进，只编译导致重新编译的语句，而不是整个批处理。与 SQL Server 2000 相比，这一“语句级重新编译”功能将改善 SQL Server 2005 的重新编译行为。尤其，在批处理重新编译过程中，SQL Server 2005 所用的 CPU 时间和内存更少，而得到的编译块也更少。

语句级重新编译有一个优点显而易见：不必再只是为了减少较长的存储过程的重新编译罚点，而将其分成多个较短的存储过程。

在处理重新编译问题之前，本文将用较大的篇幅来讨论查询计划缓存的相关及重要的主题。缓存计划以便重用。假如未缓存查询计划，重用机会将为零。这种计划将在每次执行时被编译，从而导致性能低下。只有在极少数情况下，才不需要进行缓存。本文将在后面指出这些情况。

SQL Server 可缓存许多批处理类型的查询计划。以下列举了这些类型。对于每种类型，我们都说明了重用计划的必要条件。请注意，这些条件不一定充分。稍后，读者将在本文中看到完整的相关内容。

SELECT ProductID 
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductID 
SELECT ProductID 
  
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductId

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 1000 
ORDER BY ProductID 
  
SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 2000 
ORDER BY ProductID

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > @p1 
ORDER BY ProductID

EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber  
FROM Production.Product p  
INNER JOIN Production.ProductDescription pd  
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 170 
  
EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber 
FROM Production.Product p INNER JOIN Production.ProductDescription pd 
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 1201

SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal 
FROM Sales.SalesOrderDetail sod 
WHERE SalesOrderID <   
GROUP BY SalesOrderID 
ORDER BY SalesOrderID", SQL_NTS)
SQLExecute(hstmt)

EXEC ( 'SELECT *' + ' FROM Production.Product pr  
INNER JOIN Production.ProductPhoto ph' + '  
ON pr.ProductID = ph.ProductPhotoID' +  
' WHERE pr.MakeFlag = ' + @mkflag )

认识到多“级”缓存匹配将独立进行，这一点很重要。举个例子。假设批处理 1（非存储过程）包含下列语句（及其他）：

EXEC dbo.procA

批处理 2（也不是存储过程）与批处理 1 在文本上不相匹配，但包含引用相同存储过程的 “EXEC dbo.procA”。这里，批处理 1 和批处理 2 的查询计划不相匹配。然而，只要在这两个批处理的一个中执行 “EXEC dbo.procA”，同时在执行当前批处理之前执行了另一个批处理，而 procA 的查询计划仍存在于计划缓存中，就有可能实现 procA 的查询计划重用。但是，每次单独执行 procA 都会得到执行上下文。该执行上下文要么刚刚生成（假如正在使用所有现有的执行上下文），要么被重用（假如未使用的执行上下文可用）。即使使用 EXEC 执行了动态 SQL，或者在批处理 1 和批处理 2 内部执行了自动参数化语句，也有可能产生某种类型的重用。总之，下列三类批处理会启动他们自己的“级别”（无论任何包含级别中是否存在缓存匹配，都会在这些级别中产生缓存匹配）：

对于上述规则，存储过程是一个例外。例如，假如两个不同的存储过程都包含“EXEC procA”语句，那么就不会产生 procA 的查询计划和执行上下文重用。

查询计划和执行上下文

当一个可缓存的批处理被提交给 SQL Server 2005 进行执行时，该批处理会被编译，而它的一个查询计划会被放到计划缓存中。查询计划是一种只读的可重入结构（由多个用户共享）。任何时候，查询计划在计划缓存中最多只能有两个实例：一个用于所有的串行执行，另一个用于所有的并行执行。并行执行的副本适用于所有的并行级别。（严格说来，假如相同的用户使用带有相同会话选项的两个不同会话设置的两个相同的查询同时达到 SQL Server 2005，在执行时将存在两个查询计划。但是，当执行结束时，仅有一个查询计划会保留在计划缓存中。）

执行上下文是从查询计划中派生的。执行上下文是为生成查询结果而“执行”的。执行上下文也被缓存和重用。当前执行批处理的每位用户将拥有一个执行上下文，其中保存了特定于其执行的数据（比如：参数值）。虽然被重用，但是执行上下文并不是可重入的（例如，它们是单线程的）。也就是说，在任何时候，一个执行上下文只能执行一个由会话提交的批处理，而在执行时，相应的上下文不会提供给任何其他会话或用户。

查询计划与从中派生的执行上下文之间的关系如下图所示。其中，有一个查询计划，从中派生了三个执行上下文。这些执行上下文包含参数值和特定于用户的信息。对于参数值和特定于用户的信息而言，查询计划都不是明确的。

在计划缓存中，一个查询计划和多个相关联的执行上下文可以共存。然而，单个执行上下文（假如没有相关联的查询计划）无法存在于计划缓存中。只要从计划缓存中删除了查询计划，所有相关联的执行上下文也将随之被删除。

当搜索计划缓存以寻找计划重用的机会时，将比较各个查询计划，而不是各个执行上下文。一旦找到了可重用的查询计划，就能找到（导致执行上下文重用）或新生成可用的执行上下文。所以，查询计划重用不一定会导致执行上下文重用。

执行上下文是在“匆忙中 (on the fly)”派生的，其间一个主干执行上下文会在批处理执行开始之前生成。随着执行的进行，将生成必要的执行上下文片断并放入该主干中。这意味着，即便从中删除了特定于用户的信息和查询参数，两个执行上下文也不必完全相同。由于派生自相同查询计划的执行上下文的结构可以彼此不同，因此用于特定执行的执行上下文对性能有轻微的影响。随着计划缓存变“热”并达到稳定状态，这种性能差异的影响会越来越小。

例如：假设批处理 B 包含一个“if”语句。当 B 开始执行时，就会为其生成一个执行上下文。假设在首次执行时，提取了“if”的“true”分支。此外，假设在首次执行时，B 再次由另一个连接提交。因为当时唯一存在的执行上下文正被使用，所以将生成第二个执行上下文，并提供给第二个连接。假设第二个执行上下文提取了“if”的“false”分支。当这两个执行都完成之后，将有第三个连接提交 B。假设 B 的第三个执行选择了“true”分支，假如 SQL Server 为该连接选择了 B 的第一个执行上下文而非第二个执行上下文，那么完成该执行的速度将稍快一些。

可重用批处理 S 的执行上下文，即使 S 的调用顺序有所不同。例如，调用顺序可以是“存储过程 1 --> 存储过程 2 --> S”，而第二个调用顺序可以是“存储过程 3 --> S”。可对 S 的第二次执行重用其第一次执行的执行上下文。

假如批处理执行生成了严重级别高达 11 或更高的错误，那么其执行上下文会被破坏。假如批处理执行生成了一个警告（严重级别为 10），那么执行上下文就不会被破坏。因此，即便没有内存方面的压力——会导致计划缓存缩小，计划缓存中所缓存的（给定查询计划的）执行上下文的数量也会起伏不定。

不缓存并行计划的执行上下文。SQL Server 编译并行查询计划的一个必备条件是：满足了处理器关联掩码和“最高程度的并行”服务器级选项的值（可能是用“sp_configure”存储过程设置）后所剩下的处理器的最低数量大于 1。即使编译了并行查询计划，SQL Server 的“查询执行”组件也可能会从中生成一个串行执行上下文。不缓存派生自并行计划的任何执行上下文——串行或并行。但是，会缓存并行查询计划。

查询计划缓存及各种 SET 选项（与 showplan 相关及其他）

各种 SET 选项——多数与 showplan 相关——以多种复杂的方式影响着查询计划和执行上下文的编译、缓存和重用。下表汇总了相关的详细信息。

应按如下顺序阅读该表中的内容。批处理通过表中第一列所指定的特定模式提交给 SQL Server。已提交的批处理的计划缓存中可能存在、也可能不存在已缓存的查询计划。第 2 列和第 3 列描述了存在已缓存的查询计划时的情况；第 4 列和第 5 列说明了不存在已缓存的查询计划时的情况。在每个类别中，查询计划和执行上下文的各种情况都是独立的。表中说明了结构（查询计划或执行上下文）所发生的情况：是否被缓存、重用和使用。

查询计划和执行上下文的相关成本

对于每个查询计划和执行上下文，都会保留一个成本。该成本会在一定程度上控制计划或上下文在计划缓存中的存在时间。在 SQL Server 2000 和 SQL Server 2005 中，成本的计算和操作方式有所不同。详细情况如下。

SQL Server 2000：对于查询计划，成本用于度量查询优化器用于优化批处理的服务器资源（CPU 时间和 I/O）。对于特殊查询，其成本为零。对于执行上下文，成本用于度量用于初始化执行上下文以便各个语句做好执行准备的服务器资源（CPU 时间和 I/O）。请注意，执行上下文成本并不包含批处理执行期间所带来的成本（CPU 和 I/O）。通常，执行上下文成本低于查询计划成本。

以下说明了在 SQL Server 2000 中，批处理的查询计划如何带来成本开销。影响成本的因素有四个：生成计划所用的 CPU 时间 (cputime)；从磁盘读取的页数 (ioread)；写入磁盘的页数 (iowrite)；以及批处理的查询计划所占的内存页数 (pagecount)。这样，查询计划成本可表示为（f 是一个数学函数）：

Query plan cost c = f(cputime, ioread, iowrite) / pagecount

以下说明了在 SQL Server 2000 中，批处理的执行上下文如何带来成本开销。上方等式中单独的成本 c 将针对批处理中的每个语句进行计算，并加以累计。但是请注意，单独成本现在是语句初始化成本，而非语句编译或执行成本。

有时，惰性写入器进程会清除计划缓存并减少成本。将成本除于四，并根据需要四舍五入。（例如，25 --> 6 --> 1 --> 0。）当内存方面有压力时，成本为零的查询计划和执行上下文将从计划缓存中删除。重用查询计划或执行上下文时，其成本会被重置回编译（或执行上下文生成）成本。特殊查询的查询计划成本总是以 1 为单位逐渐递增。因此，频繁执行的批处理的查询计划在计划缓存中的存在时间要长于不频繁执行的批处理的计划。

SQL Server 2005：特殊查询的成本为零。另外，查询计划的成本用于度量生成其所需的资源量。尤其，该成本按“记号数”计算，最大值为 31，共由三部分组成：

成本 = I/O 成本 + 上下文开关成本（用于度量 CPU 成本） + 内存成本

该成本各部分的计算方法如下。

在 SQL Server 2005 中，计划缓存不同于数据缓存。此外，还有一些特定于功能的缓存。在 SQL Server 2005 中，惰性写入器进程不会增加成本。相反，只要计划缓存的大小达到缓冲池大小的 50%，下一个计划缓存访问就会以 1 为单位减少所有计划的记号数。请注意，由于这种减少情况是随为查找计划而访问计划缓存的线程而产生的，因此可认为这一减少是以惰性方式进行的。在 SQL Server 2005 中，假如所有缓存大小的总和达到或超过了缓冲池大小的 75%，那么将激活一个专用的资源监视器线程，它将减少所有缓存中的所有对象的记号数。（所以该线程的行为方式与 SQL Server 2000 中的惰性写入器线程大致相同。）查询计划重用导致查询计划成本被重置为初识值。

五、文本其他部分的内容说明

到此，读者应该清楚：为了获得良好的 SQL Server 批处理执行性能，需要执行下列这两项操作：

下一节描述了有关查询计划重用的详细信息。同时，在适当位置给出了可实现更好的计划重用的最佳实务。再下一节中，我们介绍了可能导致重新编译次数增多的一些常见方案，并给出了避免发生这种情况的最佳实务。

六、查询计划重用

计划缓存包含查询计划和执行上下文。概念上，查询计划同与之相关联的执行上下文相链接。批处理 S 的查询计划重用独立于 S 本身（比如：查询文本或存储过程名称）以及该批处理的一些外部因素（比如：生成 S 的用户名，生成 S 的 SET 选项，与 S 相关联的连接的 SET 选项等等）。有一些外部因素会影响计划重用：只要两个相同的查询在这些因素之一上有所不同，就将无法使用常见计划。而其他外部因素则不会影响计划重用。

大多数影响计划重用的因素都罗列在 sys.syscacheobjects 虚拟表中。下方列表描述了“典型用法”方案中的因素。在某些情况下，条目只会指出何时计划未被缓存（并因此永不被重用）。

通常，假如导致查询计划被缓存的连接的服务器、数据库和连接设置与当前连接的相应设置相同，就会重用查询计划。其次，批处理所引用的对象不要求名称解析。例如，Sales.SalesOrderDetail 不要求名称解析，而 SalesOrderDetail 则相反，因为名为 SalesOrderDetail 的表会存在于多个数据库中。大体上，完全合格的对象名称会为计划重用提供更多的机会。

影响计划重用的因素

请注意，查询计划假如还未被缓存，就不能被重用。 所以，我们将仅明确指出无可缓存性，即表示无重用。

最佳实务：当通过非典型参数值执行存储过程时，“EXEC ...WITH RECOMPILE”可用于确保新的查询计划不会替代使用典型参数值编译的现有的已缓存计划。

“EXEC ...WITH RECOMPILE”还可与用户定义的函数一起使用，但只有在存在 EXEC 关键字时才可以。

回想一下，当 SQL Server 在批处理 B 中执行语句后，重新编译了某些（或所有）语句，这时 B 被重新编译。产生重新编译的原因可分为两大类：

下面两节将详细介绍这两个类别。

导致批处理重新编译的与正确性相关的原因

后面列举了一些导致与正确性相关的重新编译的具体操作。因为必须进行这类重新编译，所以用户可以选择不进行这些操作，或者在 SQL Server 运行的非高峰期执行这些操作。

对象的架构

下列操作将刷新引用特定数据库的计划缓存条目，并随之导致全新编译。

use master 
go 
<-- A query Q that references a database called db1 --> 
go

当 SQL Server 2005 的事务级快照隔离级别开启时，通常会发生计划重用。只要快照隔离级别下的批处理中的语句引用了一个对象（其架构自开启快照隔离模式后即被更改），同时缓存并重用了该语句的查询计划，就会发生语句级重新编译。全新编译的查询计划将被缓存，而该语句本身则会失败（根据相应隔离级别的语义）。假如未缓存查询计划，就会发生编译，随后被编译的查询计划会被缓存，而语句本身则会失败。

SET 选项

正如第 6 节已经提到的，在批处理开始执行后更改下列 SET 选项中的一项或多项，将导致重新编译：ANSI_NULL_DFLT_OFF、ANSI_NULL_DFLT_ON、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、DATEFIRST、DATEFORMAT、FORCEPLAN、LANGUAGE、NO_BROWSETABLE、NUMERIC_ROUNDABORT、QUOTED_IDENTIFIER。

导致批处理重新编译的与计划最优性相关的原因

SQL Server 设计用于当数据库中的数据更改时，生成最佳的查询执行计划。使用 SQL Server 的查询处理器中的统计（直方图）来跟踪数据更改。所以，与计划最优性相关的原因同统计密切相关。

开始详细介绍与计划最优性相关的原因之前，让我们列出不会发生与计划最优性相关的重新编译的情况。

深入介绍查询编译

下方流程图简单明了地描述了 SQL Server 中的批处理编译和重新编译过程。主要的处理步骤如下所示（本文档后面将对各个步骤进行详细介绍）：

与计划最优性相关的重新编译：整体情况

每个 SELECT、INSERT、UPDATE 和 DETELE 语句都访问一个或多个表。表内容因 INSERT、UPDATE 和 DELETE 等操作而发生变化。SQL Server 的查询处理器设计用于通过潜在地生成不同的查询计划（每个查询计划在生成时都是最佳的），来适应这种变化。使用表基数直接跟踪表内容，并使用表列上的统计（直方图）进行间接跟踪。

每个表都有一个与之相关联的重新编译阈值 (RT)。RT 是表中列数的一个函数。在查询编译期间，查询处理器将不载入或载入若干个在查询中引用的表上的统计。这些统计被称为引人关注的统计。对于查询中引用的每个表，已编译的查询计划包含：

阈值交叉测试——执行其用于决定是否要重新编译查询计划——由下列公式定义：

| modctr(snapshot) – modctr(current) | >= RT

modctr(current) 表示修改计数器的当前值，而 modctr(snapshot) 表示查询计划上次编译时修改计数器的值。假如阈值交叉在任何令人关注的统计上取得了成功，那么将重新编译查询计划。在 SQL Server 2000 中，包含该查询的整个批处理都被重新编译；而在 SQL Server 2005 中，仅重新编译上述查询。

假如表或索引视图 T 上没有统计，或者在查询编译期间 T 上现有的统计都不被认为是“令人关注的”，那么仍会仅根据 T 的基数，执行下列阈值交叉测试。

| card(snapshot) – card(current) | >= RT

card(current) 表示当前 T 中的行数，而 card(snapshot) 表示查询计划上次编译时的行数。

下面几节将介绍“整体情况”中引入的几个重要概念。

“令人关注”的统计的概念

对于每个查询计划 P，优化器保存被载入以生成 P 的统计的 ID。请注意，“载入的”集同时包含：

换而言之，查询优化器出于某个原因或其他原因，将载入的统计认作是“令人关注的”。

回想一下，统计可以手动或自动创建或更新。还会因执行下列命令而导致统计更新：

重新编译阈值 (RT)

表的重新编译阈值可部分决定引用该表的查询的重新编译频率。RT 取决于表类型（永久或临时）以及编译查询计划时表中的行数（基数）。在批处理中引用的所有表的重新编译阈值都将随该批处理的查询计划一起保存。

RT 的计算方法如下所示。(n 表示编译查询计划时表的基数。）

表修改计数器（rowmodctr 和 colmodctr）

如上所述，RT 与表所执行的修改次数进行了对比。使用称为 rowmodctr（在 SQL Server 2000 中）和 colmodctr（在 SQL Server 2005 中）的计数器跟踪表所进行的修改次数。　这两种计数器都不是针对具体事务的。例如，假如启动了某个事务，并在表中插入了 100 行，然后再回滚操作，那么对修改计数器所作的更改将不会被回滚。

Rowmodctr (SQL Server 2000)

每个表都有一个 rowmodctr 与之相关联。其值可从 sysindexes 系统表获得。在表或索引视图 T 的一个或多个列上创建的每个统计都有一个 rowmodctr 的快照值与之相关联。无论该统计何时被更新——手动或自动（通过 SQL Server 的自动统计功能），rowmodctr 的快照值也会被刷新。有关 rowmodctr 的详细信息在下方白皮书中有所描述：http://msdn.microsoft.com/library/default.asp url=/nhp/Default.asp contentid=28000409

“阈值交叉”测试中所提到的 Rowmodctr(current) 是 sysindexes 系统表（针对堆栈或聚集索引）在查询编译期间进行测试时所保留的值。

rowmodctr 可在 SQL Server 2005 服务器上使用，但其值总为 0。

补充说明一下，在 SQL Server 2000 中，当 rowmodctr 为 0 时，将无法导致重新编译。

Colmodctr (SQL Server 2005)

与 rowmodctr 不同，每个表列都会保存一个 colmodctr 值（非永久性计算列除外）。同普通列一样，永久性计算列拥有 colmodctr。使用 colmodctr 值，可以更细化地跟踪表的更改。Colmodctr 值对用户不可用；仅供查询处理器使用。

当在表或索引视图 T 的一个或多个列上（通过自动统计功能手动或自动）创建或更新统计时，最左边一列的 colmodctr 的快照值将保存在统计二进制大对象 (stats-blob) 中。

“阈值交叉”测试中所提到的 Colmodctr(current) 在查询编译期间进行测试时保留在 SQL Server 2005 的元数据中的值。

与 rowmodctr 不同，colmodctr 的值是一个不断递增的序列：colmodctr 值从不被重置为 0。

不存在非永久性计算列的 Colmodctr 值。其派生自参与计算的列。

使用 rowmodctr 和 colmodctr 跟踪表和索引视图的更改

由于 rowmodctr 和 colmodctr 值用于做出重新编译决定，因此它们的值被当作表更改来进行修改。在下列描述中，我们仅提到了表。但是，相同的情况也适用于索引视图。可通过下列语句更改表：INSERT、DELETE、UPDATE、大容量插入和表截断。下列表定义了修改 rowmodctr 和 colmodctr 值的方式。

两种特殊情况

与计划最优性相关的重新编译在下列两种特殊情况中的处理方式有所不同。

特殊情况 1：在空表或索引视图上创建的统计

SQL Server 2005 处理下列情况的方式不同于 SQL Server 2000。用户创建了一个空表 T。然后又在 T 一个或多个列上创建了一个统计 S。由于 T 为空，因此统计二进制大对象（直方图）为 NULL，但已经在 T 上创建了统计。假设在查询编译期间已发现 S 是“令人关注的”。根据重新编译阈值的“500 行”规则，只有至少包含 500 行，T 才会在 SQL Server 2000 上导致重新编译。所以，假如 T 包含的行不足 500，用户可能使用欠优化的计划。

SQL Server 2005 可检测到这种特殊情况，并以不同的方式进行处理。在 SQL Server 2005 中，这种表或索引视图的重新编译阈值为 1。换句话说，即使仅在 T 中插入一行，也可能导致重新编译。发生这种重新编译时，S 将被更新，同时 S 的直方图不再为 NULL。然而，这一重新编译附带了重新编译阈值 (500 + 0.20 * n)的一般规则。

在 SQL Server 2005 中，即使发生下列情况，重新编译阈值始终为 1：(1) T 没有统计；或者 (2) T 没有在查询编译期间被认作是“令人关注的”统计。

特殊情况 2：触发器重新编译

导致重新编译的与计划最优性相关的所有原因都适用于触发器。另外，由于已插入或已删除的表中的行数在不同的触发器执行间发生巨大变化，也会对触发器产生与计划最优性相关的重新编译。

回想一下，影响一行或多行的触发器会被单独缓存。已插入和已删除的表中的行数通过触发器的查询计划进行保存。这些数字反映了导致计划缓存的触发器执行的行数。假如后续的触发器执行产生了拥有“截然不同的”行数的已插入或已删除的表，那么将对该触发器进行重新编译（并缓存带有新行数的全新的查询计划）。

在 SQL Server 2005 中，“截然不同”的定义如下：

| log10(n) – log10(m) | > 1         if m > n 
| log10(n) – log10(m) | > 2.1      otherwise

其中 n 是已缓存查询计划中的已插入或已删除表的行数，而 m 是当前的触发器执行的对应表的行数。假如同时存在“已插入”和“已删除”的表，将对两者分别执行上面提到的测试。

举一个计算示例，从 10 到 100 的行数更改不会导致重新编译，而从 10 到 101 的更改则完全相反。

在 SQL Server 2000 中，“截然不同”的定义如下：

| log10(n+5) – log10(m+5) | >= 1

其中 n 和 m 的定义同上。请注意，根据这个公式，在 SQL Server 2000 中将已插入或已删除的表的基数从 5 改为 95，将导致重新编译，而从 5 到 94 的更改则不然。

识别与统计相关的重新编译

可通过包含字符串“Statistics changed”的事件探查器跟踪（将在本文后面介绍）的“EventSubClass”列来识别与统计相关的重新编译。

结束语

与本文档的主题没有直接相关的一个问题是：给定的多个统计以相同的顺序存在于一组相同的列中，那么在查询优化期间，查询优化器如何决定所要载入的统计呢？答案并不那么简单，但查询优化器采用如下原则：为最近的统计提供比较旧的统计更高的优先权；为使用 FULLSCAN 选项计算得出的统计提供比用样例计算得出的统计更高的优先权；等等。

与计划最优性相关的编译、重新编译和统计创建/更新间的“因果”关系可能会造成混淆。回想一下，统计可通过手动或自动方式创建或更新。只有编译和重新编译才会导致统计的自动创建或更新。另一方面，当（手动或自动）创建或更新一个统计时，重新编译查询计划（可能会发现该统计“令人关注”）的概率将增大。

最佳实务

下面给出了四个用于减少与计划最优性相关的批处理重新编译的最佳实务：

最佳实务：因为表变量的基数发生变化不会导致重新编译，所以可考虑使用表变量来替代临时表。然而，由于查询优化器不跟踪表变量的基数，同时不在表变量上创建或维护统计，因此不可能产生最佳的查询计划。用户必须确认情况是否如此，并适当地加以权衡。

最佳实务：KEEP PLAN 查询提示可改变临时表的重新编译阈值，使之与永久表的重新编译阈值相同。所以，假如对临时表的更改会导致大量的重新编译，就可使用此查询提示。可使用下列语法指定该提示：

SELECT B.col4, sum(A.col1) 
FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 
WHERE B.col3 < 100 
GROUP BY B.col4 
OPTION (KEEP PLAN)

最佳实务：为了完全避免因与计划最优性相关的（与统计更新相关的）原因而导致的重新编译，可使用下列语法指定 KEEPFIXED PLAN 查询提示：

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID 
FROM Sales.Store s INNER JOIN Sales.Customer c 
ON s.CustomerID = c.CustomerID 
WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 
GROUP BY c.TerritoryID, c.SalesPersonID 
ORDER BY Number DESC 
OPTION (KEEPFIXED PLAN)

运用这一选项，只有与正确性相关的原因（例如，语句更改所引用的表的架构，或用 sp_recompile 过程标记的表）才会导致重新编译。

在 SQL Server 2005 中，下方所述的行为方式略有不同。假设带有 OPTION(KEEPFIXED PLAN) 提示的查询首次被编译，而这一编译会导致统计的自动创建。假如 SQL Server 2005 可获得一个特殊的“统计锁”，那么就会发生重新编译并自动创建统计。假如无法获得“统计锁”，就会不产生重新编译，并在没有该统计的情况下编译查询。在 SQL Server 2000 中，出于与统计相关的原因，带有 OPTION(KEEPFIXED PLAN) 的查询从不会被重新编译，所以在这种情况下，不会尝试获取“统计锁”或自动创建统计。

最佳实务：对表或索引视图上定义的索引和统计关闭统计自动更新，将确保因这些对象所导致的与计划最优性相关的重新编译将停止。但是请注意，用这种方法关闭“自动统计”功能通常并不是一个好办法，因为查询优化器不再响应这些对象中的数据变更，并可能导致次最佳查询计划。不到万不得已不要采用这种方法。

八、编译、重新编译和并发

在 SQL Server 2000 中，存储过程、触发器和动态 SQL 的编译和重新编译均被串行化。例如，假定使用“EXEC dbo.SP1”提交了一个存储过程用以执行。并假设当 SQL Server 编译 SP1 时，收到了另一个引用相关存储过程的请求“EXEC dbo.SP1”。第二个请求将等到第一个请求完成 SP1 的编译，然后尝试重用结果查询计划。在 SQL Server 2005 中，编译被串行化，而重新编译则不会。换句话说，相同存储过程的两个并发重新编译可能会继续。最后结束的重新编译请求将替代由另一个请求生成的查询计划。

九、编译、重新编译和参数嗅探

“参数嗅探”是一个过程，通过这一过程，SQL Server 的执行环境可在编译或重新编译时“嗅探”当前参数值，并将之传递给查询优化器，以用于生成更快的查询执行计划。“当前”一词指导致编译或重新编译的语句调用中所存在的参数值。在 SQL Server 2000 和 SQL Server 2005 中，将在编译或重新编译下列批处理类型时嗅探参数值：

在 SQL Server 2005 中，这一操作被扩展到使用 OPTION(RECOMPILE) 查询提示提交的查询上。对于这种查询（可以是 SELECT、INSERT、UPDATE 或 DELETE），将同时嗅探本地变量的参数值和当前值。在批处理中，所嗅探到的（参数和本地变量的）值后面紧跟着带有 OPTION(RECOMPILE) 提示的语句。尤其对于参数来说，不会嗅探批处理调用所附带的值。

SQL Server 的事件探查器使得识别导致重新编译的批处理变得很简单。启动一个新的事件探查器跟踪，并在存储过程事件类别下，选择下列事件。（为了减少所生成的数据量，建议用户取消选定其他事件。）

此外，为了检测与统计更新相关的重新编译，可选择“对象”类别下的“自动统计”事件。

现在，启动 SQL Server 2005 Management Studio，并执行下列 T-SQL 代码：

use AdventureWorks          -- On SQL Server 2000, say "use pubs" 
go 
drop procedure DemoProc1 
go 
create procedure DemoProc1 as 
create table #t1 (a int, b int) 
select * from #t1 
go 
exec DemoProc1 
go 
exec DemoProc1 
go

暂停事件探查器跟踪，并将看到下列事件。

该事件序列指示“select * from #t1”为导致重新编译的语句。EventSubClass 列指出了进行重新编译的原因。在这种情况下，当 DemoProc1 在开始执行之前被编译，就可对“create table”语句进行变异。后续的“select”语句可能不会被编译，因为其引用了一个在初始编译时不存在的临时表 #t1。因此，DemoProc1 的已编译计划是不完整的。当 DemoProc1 开始执行时，随即创建了 #t1，然后就可以对“select”语句进行编译。由于 DemoProc1 已经执行，因此根据我们对重新编译的定义，这一编译可视为重新编译。此重新编译的真正原因是“延迟编译”。

请注意，有趣的一点是：当再次执行 DemoProc1 时，查询计划将不再是不完整的。重新编译已经将 DemoProc1 的一个完整的查询计划插入计划缓存中。所以，第二次执行过程中未发生任何重新编译。

SQL Server 2000 在这方面的情况也相同。

通过选择下列这组跟踪事件，也可以识别导致重新编译的批处理。

假如在选择了这组新的跟踪事件后执行了刚才所述的例子，那么跟踪输出将如下所示。

在此请注意，导致重新编译的语句将在 SP:Recompile 事件后被输出。这种方法不如第一种方法直接了当。因此，之后应跟踪第一组事件探查器跟踪事件。

为了看到所有针对 SP:Recompile 事件报告的可能导致重新编译的原因，请在 SQL Server 2005 上发出下列查询：

select v.subclass_name, v.subclass_value 
from sys.trace_events e inner join sys.trace_subclass_values v  
on e.trace_event_id = v.trace_event_id 
where e.name = 'SP:Recompile'

上述查询的输出如下。（仅输出不带有阴影的列；带有阴影的列用于提供其他详细信息。）

在 SQL Server 2000 中，EventSubClass 列包含从 1 到 6 的整数值，意义与上表所列的内容相同。SQL Server 2005 新增了最后两个类别。

对本节所述的两个例子，SQL Server 2000 上的跟踪输出与 SQL Server 2005 相同，除了在 SQL Server 2000 上，EventSubClass 列包含“3”而非字符串“Deferred compile”。从内部来说，语句级重新编译发生在 SQL Server 2005 上，因此，仅有“select * from #t1”在 SQL Server 2005 上进行重新编译，而在 SQL Server 2000 上，整个 DemoProc1 都将被重新编译。

因混用 DDL 和 DML 而导致重新编译

在批处理或存储过程中混用数据定义语言 (DDL) 和数据操作语言 (DML) 语句并不是一个好办法，因为这会引起不必要的重新编译。下面这个例子运用存储过程阐述了这一点。（批处理也会发生同样的情况。但是，由于 SQL Server 2005 事件探查器没有提供必要的跟踪事件，因此无法对其进行实时观测。）创建下列存储过程。

drop procedure MixDDLDML 
go 
create procedure MixDDLDML as 
create table tab1 (a int)            -- DDL 
select * from tab1                   -- DML 
create index nc_tab1idx1 on tab1(a)  -- DDL 
select * from tab1                   -- DML 
create table tab2 (a int)            -- DDL 
select * from tab2                   -- DML 
go 
exec MixDDLDML 
go

在事件探查器跟踪输出中，可观测到下列事件。

这里说明了 MixDDLDML 是如何编译的。

总之，在这个例子中，SQL Server 2000 和 SQL Server 2005 中都发生了三次重新编译。但是，SQL Server 2005 的重新编译成本要低于 SQL Server 2000，因为前者属于语句级而非存储过程级重新编译。

假如根据下方所示来编写存储过程，那么将观察到有趣的现象。

create procedure DDLBeforeDML as 
create table tab1 (a int)            -- DDL 
create index nc_tab1idx1 on tab1(a)  -- DDL 
create table tab2 (a int)            -- DDL 
select * from tab1                   -- DML 
select * from tab1                   -- DML 
select * from tab2                   -- DML 
go 
exec DDLBeforeDML 
go

在事件探查器跟踪输出中，可观察到下列事件。

在 SQL Server 2005 中，出于语句级重新编译，仍会发生这三次重新编译。与 MixDDLDML 存储过程相比，重新编译的次数并没有减少。假如在 SQL Server 2000 上尝试相同的例子，重新编译的次数将从 3 次减少到 1 次。在 SQL Server 2000 中，重新编译在存储过程级上进行，因而可以一次性编译三个“select”语句。总之，与 SQL Server 2000 相比，SQL Server 2005 的重新编译工作量没有增加，但是重新编译的次数却增多了。

下面，考虑以下 T-SQL 代码段：

-- dbo.someTable will be used to populate a temp table 
-- subsequently. 
create table dbo.someTable (a int not null, b int not null) 
go 
declare @i int 
set @i = 1 
while (@i <= 2000) 
begin 
    insert into dbo.someTable values (@i, @i+5) 
    set @i = @i + 1 
end 
go 
  
-- This is the stored procedure of main interest. 
create procedure dbo.AlwaysRecompile 
as  
set nocount on 
  
-- create a temp table 
create table #temp1(c int not null, d int not null) 
  
select count(*) from #temp1 
  
-- now populate #temp1 with 2000 rows 
insert into #temp1 
select * from dbo.someTable 
  
-- create a clustered index on #temp1 
create clustered index cl_idx_temp1 on #temp1(c) 
  
select count(*) from #temp1 
go

在 SQL Server 2000 中，当首次执行这个存储过程时，将对第一个“select”语句生成第一个 SP:Recompile 事件。这是一次延迟编译，不是真正的重新编译。第二个 SP:Recompile 事件针对第二个“select”。当发生第一次重新编译时，第二个“select”也会被编译，因为在 SQL Server 2000 中，编译是在批处理级别上进行的。然后，在执行时，#temp1 的架构因新建的聚集索引而发生了变化。所以，产生第二个 SP:Recompile 的原因是架构更改。

因行修改次数而导致的重新编译

考虑下方存储过程及其执行。

use AdventureWorks   -- or say "use pubs" on SQL Server 2000 
go 
create procedure RowCountDemo 
as 
begin 
    create table #t1 (a int, b int) 
 
    declare @i int 
    set @i = 0    while (@i < 20) 
    begin 
       insert into #t1 values (@i, 2*@i - 50) 
 
       select a 
       from #t1  
       where a < 10 or ((b > 20 or a >=100) and (a < 10000)) 
       group by a 
  
       set @i = @i + 1 
    end 
end 
go 
exec RowCountDemo 
go

回想一下，当表在计算阈值时为空，临时表的重新编译阈值为 6。当执行 RowCountDemo 时，在 #t1 包含整 6 行后，可观察到与“statistics changed”（统计被更改）相关的重新编译。通过更改“while”循环的上限，可观察到更多的重新编译。

因 SET 选项更改而导致的重新编译

考虑下列存储过程。

use AdventureWorks 
go 
create procedure SetOptionsDemo as 
begin 
    set ansi_nulls off 
    select p.Size, sum(p.ListPrice) 
    from Production.Product p  
         inner join Production.ProductCategory pc 
          on p.ProductSubcategoryID = pc.ProductCategoryID 
    where p.Color = 'Black' 
    group by p.Size 
end 
go 
exec SetOptionsDemo    -- causes a recompilation 
go 
exec SetOptionsDemo    -- does not cause a recompilation 
go

当执行 SetOptionsDemo 时，在“ansi_nulls”为 ON 的情况下编译“select”查询。当 SetOptionsDemo 开始执行时，该 SET 选项的值将由于“set ansi_nulls off”而发生变化，因而已编译的查询计划将不再“有效”。所以，将在“ansi_nulls”为 OFF 的情况下进行重新编译。第二次执行不会导致重新编译，因为已缓存的计划将在“ansi_nulls”为 OFF 的情况下进行编译。

表明 SQL Server 2005 所需的重新编译较 SQL Server 2000 多的另一个示例

考虑下列存储过程。

use AdventureWorks     -- say "use pubs" on SQL Server 2000 
go 
create procedure CreateThenReference as 
begin 
   -- create two temp tables 
   create table #t1(a int, b int) 
   create table #t2(c int, d int) 
 
   -- populate them with some data 
   insert into #t1 values (1, 1) 
   insert into #t1 values (2, 2) 
   insert into #t2 values (3, 2) 
   insert into #t2 values (4, 3) 
 
         -- issue two queries on them 
   select x.a, x.b, sum(y.c) 
   from #t1 x inner join #t2 y on x.b = y.d 
   group by x.b, x.a 
   order by x.b 
  
   select *  
   from #t1 z cross join #t2 w 
   where w.c != 5 or w.c != 2 
end 
go 
exec CreateThenReference 
go

在 SQL Server 2005 中，CreateThenReference 的第一次执行导致了六项语句级重新编译：其中有四项针对“insert”语句，有两项针对“select”查询。当该存储过程开始执行时，最初的查询计划不包含针对“insert”或“select”语句的计划，因为其所引用（临时表 #t1 和 #t2）的对象还不存在。创建了 #t1 和 #t2 之后，将编译“insert”和“select”的查询计划，而这些编译被视为重新编译。在 SQL Server 2000 中，由于整个存储过程被立即重新编译，因此仅发生一次（存储过程级）重新编译——第一个“insert”开始执行时所引发的重新编译。这时，整个存储过程都被重新编译，而因为 #t1 and #t2 已经存在，可一次性对后续的“insert”和“select”进行编译。显而易见，通过添加更多引用诸如 #t1 和 #t2 等对象的语句，SQL Server 2005 中的语句级重新编译次数可无限增加。

十一、工具与命令

本节介绍了用于观测和调试重新编译的各种工具和命令。

Sys.syscacheobjects 虚拟表

虽然可以从任何数据库进行查询，但该虚拟表理论上仅存在于 master 数据库中。该虚拟表的 cacheobjtype 列特别有趣。当 cacheobjtype = "Compiled Plan"，相应的行将引用一个查询计划。当 cacheobjtype = "Executable Plan"，相应的行将引用一个执行上下文。正如我们前面所说明的，每个执行上下文必须有自己的关联查询计划，反之则不然。所涉及的另一列是 objtype 列：指示其计划被缓存的对象的类型（比如：“Adhoc”、“Prepared”和“Proc”）。setopts 列编码了一个位图，指示在编译计划时生效的 SET 选项。有时，相同的已编译计划（仅 setopts 列有所不同）的多个副本被缓存在一个计划缓存中。这表示不同的连接正在使用几组不同的 SET 选项——通常属于不该发生的情况。usecounts 列保存了自对象被缓存以来已缓存对象被重用的次数。

请参考 BOL 了解有关此虚拟表的更多信息。

DBCC FREEPROCCACHE

此命令可删除计划缓存中的所有已缓存的查询计划和执行上下文。不应在生产服务器上运行该命令，因为它反过来会影响正在运行的应用程序的性能。在对重新编译问题进行故障诊断时，该命令对于控制计划缓存的内容很有用。

DBCC FLUSHPROCINDB( db_id )

此命令可删除特定数据库的计划缓存中的所有已缓存计划。不应在生产服务器上运行该命令，因为它反过来会影响正在运行的应用程序的性能。

事件探查器跟踪事件

下列事件探查器跟踪事件涉及观测和调试计划缓存、编译和重新编译行为。

性能计数器

在调试可能因过度编译和重新编译所导致的性能问题时，涉及下列性能计数器的值。

SQL Server 2005 可缓存提交给其以执行的各种语句类型的查询计划。查询计划缓存可导致查询计划重用，避免编译罚点，并更好地运用计划缓存。一些编码方法会阻碍查询计划缓存和重用，因此应加以避免。SQL Server 可发现查询计划重用的机会。特别是，查询计划会因下面这两个原因而无法重用：(a) 出现在查询计划中的对象架构会发生变化，从而导致计划无效；(b) 查询计划所引用的表中的数据所发生的变化足以使计划变成非最佳的。SQL Server 可在查询执行时发现这两类情况，并根据需要对整个或部分批处理进行重新编译。不良的 T-SQL 编码方法会增加重新编译的频率，从而反过来影响 SQL Server 的性能。在许多情况下，都可以对这类情况进行调试和纠正。

附录 A：SQL Server 2005 何时不自动参数化查询？

自动参数化是一个过程，SQL Server 通过这个过程将出现在 SQL 语句中的文本常量替换为诸如 @p1 和 @p2 等参数。然后，SQL 语句的已编译计划以参数化的形式被缓存在计划缓存中，以便后续的语句（只是在文本常量的值上有所不同）可重用已缓存的计划。正如第四部分所提到的，只有参数值不影响查询计划选择的 SQL 语句才会被自动参数化。

SQL Server 的 LPE（语言处理和执行）组件可参数化 SQL 语句。当发现文本常量的值不影响查询计划选择时，QP（查询处理器）组件将声明 LPE 的自动参数化尝试是“安全的”，并继续执行自动参数化；否则，将声明自动参数化是“不安全的”，并将其中止。在第 11.5 节提到的一些性能计数器的值（‘SQLServer:SQL 统计’类别）报告了有关自动参数化的统计信息。

下方列表列举了 SQL Server 2005 不对其进行自动参数化的语句类型。

UPDATE Sales.Customer 
SET CustomerType = N'S' 
WHERE CustomerType = @a

WHERE 20 > 5

一、本白皮书的目的

此白皮书的目的有几个。阐述了批处理在 SQL Server 2005 中的缓存和重用方式，并就最大限度重用已缓存的计划提供了相应的最佳实务。另外，文中还说明了一些重新编译批处理的方案，并针对减少或消除不必要的重新编译，给出了最佳实务。本白皮书阐述了 SQL Server 2005 的“语句级重新编译”功能。另外，还介绍了许多工具及实用程序，它们可在查询编译、查询重新编译、计划缓存和计划重用过程中充当很有用的观测工具。我们在整篇文章中对比了 SQL Server 2000 和 SQL Server 2005 的不同表现，以便读者能够更好地了解文中的内容。本文档所列举的语句均适用于 SQL Server 2000 和 SQL Server 2005。同时，明确指出了这两个 SQL Server 版本在行为上的差异。

本文面向三类读者：

用户：使用、维护并为 SQL Server 开发应用程序的人员。初识 SQL Server 2005 的用户及正从 SQL Server 2000 进行迁移的人员将在这里找到有用的信息。

开发人员：SQL Server 开发人员将在这里找到有用的背景信息。

测试人员和项目经理：本文档将对“SQL Server 2005 中的编译和重新编译”功能提供说明。

在查询、批处理、存储过程、触发器、被准备语句或动态 SQL 语句（以下称为“批处理”）在 SQL Server 上开始执行以前，批处理将被编译成计划。然后，将执行该计划以便发挥其效力或生成结果。

一个批处理可包含一个或多个 SELECT、INSERT、UPDATE 和 DELETE 语句、存储过程调用（T-SQL“粘连”或控制结构（比如：SET、IF、WHILE、DECLARE）可能使其产生交错）、DDL 语句（比如：CREATE、DROP）以及与权限相关的语句（比如：GRANT、DENY 和 REVOKE）。批处理还可包含 CLR 构造的定义和用法（比如：用户定义的类型、函数、过程和聚合）。

已编译的计划被保存到 SQL Server 的一部分内存中，这部分内存称为计划缓存。将搜索计划缓存以获得重用计划的机会。假如对某个批处理重用计划，就可避免编译工作。请注意，在有关 SQL Server 的文献中，过去所用的“过程缓存”一词在本文中被称为“计划缓存”。“计划缓存”用词更准确，因为计划缓存不仅仅保存存储过程的查询计划。

在涉及 SQL Server 的用语中，上段所提到的编译过程有时会被误认为是“重新编译”，但该过程仅涉及“编译”。

重新编译的定义：假设某个批处理被编译成一个或多个查询计划的集合。在 SQL Server 开始执行任何单独的查询计划之前，服务器将检查该查询计划的有效性（正确性）和最优性。假如某个检查失败了，将重新编译相应查询计划所对应的语句或整个批处理，并可能生成一个不同的查询计划。这种编译称为“重新编译”。

请特别注意，不必预先缓存该批处理的查询计划。实际上，某些批处理类型从不被缓存，但仍能引发重新编译。举个例，有个批处理包含一个大于 8 KB 的文本。假设该批处理创建了一个临时表，并在表中插入了 20 行。所插入的第七行将导致重新编译，但由于其包含的文本较大，将不缓存该批处理。

在 SQL Server 中执行的多数重新编译都是有根据的。有些是为了确保语句的正确性；另一些是为了在 SQL Server 数据库中的数据发生变化时，获得最佳的查询执行计划。然而，重新编译有时会大大延缓批处理执行的速度。这时，就有必要减少进行重新编译的次数。

在 SQL Server 2000 中重新编译批处理时，将重新编译批处理中的所有语句，而不仅仅是触发重新编译的语句。SQL Server 2005 在该行为上做了改进，只编译导致重新编译的语句，而不是整个批处理。与 SQL Server 2000 相比，这一“语句级重新编译”功能将改善 SQL Server 2005 的重新编译行为。尤其，在批处理重新编译过程中，SQL Server 2005 所用的 CPU 时间和内存更少，而得到的编译块也更少。

语句级重新编译有一个优点显而易见：不必再只是为了减少较长的存储过程的重新编译罚点，而将其分成多个较短的存储过程。

在处理重新编译问题之前，本文将用较大的篇幅来讨论查询计划缓存的相关及重要的主题。缓存计划以便重用。假如未缓存查询计划，重用机会将为零。这种计划将在每次执行时被编译，从而导致性能低下。只有在极少数情况下，才不需要进行缓存。本文将在后面指出这些情况。

SQL Server 可缓存许多批处理类型的查询计划。以下列举了这些类型。对于每种类型，我们都说明了重用计划的必要条件。请注意，这些条件不一定充分。稍后，读者将在本文中看到完整的相关内容。

SELECT ProductID 
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductID 
SELECT ProductID 
  
FROM Sales.SalesOrderDetail 
GROUP BY ProductID 
HAVING AVG(OrderQty) > 5 
ORDER BY ProductId

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 1000 
ORDER BY ProductID 
  
SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > 2000 
ORDER BY ProductID

SELECT ProductID, SalesOrderID, LineNumber 
 FROM Sales.SalesOrderDetail  
WHERE ProductID > @p1 
ORDER BY ProductID

EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber  
FROM Production.Product p  
INNER JOIN Production.ProductDescription pd  
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 170 
  
EXEC sp_executesql N'SELECT p.ProductID, p.Name, p.ProductNumber 
FROM Production.Product p INNER JOIN Production.ProductDescription pd 
ON p.ProductID = pd.ProductDescriptionID  
WHERE p.ProductID = @a', N'@a int', 1201

SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal 
FROM Sales.SalesOrderDetail sod 
WHERE SalesOrderID <   
GROUP BY SalesOrderID 
ORDER BY SalesOrderID", SQL_NTS)
SQLExecute(hstmt)

EXEC ( 'SELECT *' + ' FROM Production.Product pr  
INNER JOIN Production.ProductPhoto ph' + '  
ON pr.ProductID = ph.ProductPhotoID' +  
' WHERE pr.MakeFlag = ' + @mkflag )

认识到多“级”缓存匹配将独立进行，这一点很重要。举个例子。假设批处理 1（非存储过程）包含下列语句（及其他）：

EXEC dbo.procA

批处理 2（也不是存储过程）与批处理 1 在文本上不相匹配，但包含引用相同存储过程的 “EXEC dbo.procA”。这里，批处理 1 和批处理 2 的查询计划不相匹配。然而，只要在这两个批处理的一个中执行 “EXEC dbo.procA”，同时在执行当前批处理之前执行了另一个批处理，而 procA 的查询计划仍存在于计划缓存中，就有可能实现 procA 的查询计划重用。但是，每次单独执行 procA 都会得到执行上下文。该执行上下文要么刚刚生成（假如正在使用所有现有的执行上下文），要么被重用（假如未使用的执行上下文可用）。即使使用 EXEC 执行了动态 SQL，或者在批处理 1 和批处理 2 内部执行了自动参数化语句，也有可能产生某种类型的重用。总之，下列三类批处理会启动他们自己的“级别”（无论任何包含级别中是否存在缓存匹配，都会在这些级别中产生缓存匹配）：

对于上述规则，存储过程是一个例外。例如，假如两个不同的存储过程都包含“EXEC procA”语句，那么就不会产生 procA 的查询计划和执行上下文重用。

查询计划和执行上下文

当一个可缓存的批处理被提交给 SQL Server 2005 进行执行时，该批处理会被编译，而它的一个查询计划会被放到计划缓存中。查询计划是一种只读的可重入结构（由多个用户共享）。任何时候，查询计划在计划缓存中最多只能有两个实例：一个用于所有的串行执行，另一个用于所有的并行执行。并行执行的副本适用于所有的并行级别。（严格说来，假如相同的用户使用带有相同会话选项的两个不同会话设置的两个相同的查询同时达到 SQL Server 2005，在执行时将存在两个查询计划。但是，当执行结束时，仅有一个查询计划会保留在计划缓存中。）

执行上下文是从查询计划中派生的。执行上下文是为生成查询结果而“执行”的。执行上下文也被缓存和重用。当前执行批处理的每位用户将拥有一个执行上下文，其中保存了特定于其执行的数据（比如：参数值）。虽然被重用，但是执行上下文并不是可重入的（例如，它们是单线程的）。也就是说，在任何时候，一个执行上下文只能执行一个由会话提交的批处理，而在执行时，相应的上下文不会提供给任何其他会话或用户。

查询计划与从中派生的执行上下文之间的关系如下图所示。其中，有一个查询计划，从中派生了三个执行上下文。这些执行上下文包含参数值和特定于用户的信息。对于参数值和特定于用户的信息而言，查询计划都不是明确的。

在计划缓存中，一个查询计划和多个相关联的执行上下文可以共存。然而，单个执行上下文（假如没有相关联的查询计划）无法存在于计划缓存中。只要从计划缓存中删除了查询计划，所有相关联的执行上下文也将随之被删除。

当搜索计划缓存以寻找计划重用的机会时，将比较各个查询计划，而不是各个执行上下文。一旦找到了可重用的查询计划，就能找到（导致执行上下文重用）或新生成可用的执行上下文。所以，查询计划重用不一定会导致执行上下文重用。

执行上下文是在“匆忙中 (on the fly)”派生的，其间一个主干执行上下文会在批处理执行开始之前生成。随着执行的进行，将生成必要的执行上下文片断并放入该主干中。这意味着，即便从中删除了特定于用户的信息和查询参数，两个执行上下文也不必完全相同。由于派生自相同查询计划的执行上下文的结构可以彼此不同，因此用于特定执行的执行上下文对性能有轻微的影响。随着计划缓存变“热”并达到稳定状态，这种性能差异的影响会越来越小。

例如：假设批处理 B 包含一个“if”语句。当 B 开始执行时，就会为其生成一个执行上下文。假设在首次执行时，提取了“if”的“true”分支。此外，假设在首次执行时，B 再次由另一个连接提交。因为当时唯一存在的执行上下文正被使用，所以将生成第二个执行上下文，并提供给第二个连接。假设第二个执行上下文提取了“if”的“false”分支。当这两个执行都完成之后，将有第三个连接提交 B。假设 B 的第三个执行选择了“true”分支，假如 SQL Server 为该连接选择了 B 的第一个执行上下文而非第二个执行上下文，那么完成该执行的速度将稍快一些。

可重用批处理 S 的执行上下文，即使 S 的调用顺序有所不同。例如，调用顺序可以是“存储过程 1 --> 存储过程 2 --> S”，而第二个调用顺序可以是“存储过程 3 --> S”。可对 S 的第二次执行重用其第一次执行的执行上下文。

假如批处理执行生成了严重级别高达 11 或更高的错误，那么其执行上下文会被破坏。假如批处理执行生成了一个警告（严重级别为 10），那么执行上下文就不会被破坏。因此，即便没有内存方面的压力——会导致计划缓存缩小，计划缓存中所缓存的（给定查询计划的）执行上下文的数量也会起伏不定。

不缓存并行计划的执行上下文。SQL Server 编译并行查询计划的一个必备条件是：满足了处理器关联掩码和“最高程度的并行”服务器级选项的值（可能是用“sp_configure”存储过程设置）后所剩下的处理器的最低数量大于 1。即使编译了并行查询计划，SQL Server 的“查询执行”组件也可能会从中生成一个串行执行上下文。不缓存派生自并行计划的任何执行上下文——串行或并行。但是，会缓存并行查询计划。

查询计划缓存及各种 SET 选项（与 showplan 相关及其他）

各种 SET 选项——多数与 showplan 相关——以多种复杂的方式影响着查询计划和执行上下文的编译、缓存和重用。下表汇总了相关的详细信息。

应按如下顺序阅读该表中的内容。批处理通过表中第一列所指定的特定模式提交给 SQL Server。已提交的批处理的计划缓存中可能存在、也可能不存在已缓存的查询计划。第 2 列和第 3 列描述了存在已缓存的查询计划时的情况；第 4 列和第 5 列说明了不存在已缓存的查询计划时的情况。在每个类别中，查询计划和执行上下文的各种情况都是独立的。表中说明了结构（查询计划或执行上下文）所发生的情况：是否被缓存、重用和使用。

查询计划和执行上下文的相关成本

对于每个查询计划和执行上下文，都会保留一个成本。该成本会在一定程度上控制计划或上下文在计划缓存中的存在时间。在 SQL Server 2000 和 SQL Server 2005 中，成本的计算和操作方式有所不同。详细情况如下。

SQL Server 2000：对于查询计划，成本用于度量查询优化器用于优化批处理的服务器资源（CPU 时间和 I/O）。对于特殊查询，其成本为零。对于执行上下文，成本用于度量用于初始化执行上下文以便各个语句做好执行准备的服务器资源（CPU 时间和 I/O）。请注意，执行上下文成本并不包含批处理执行期间所带来的成本（CPU 和 I/O）。通常，执行上下文成本低于查询计划成本。

以下说明了在 SQL Server 2000 中，批处理的查询计划如何带来成本开销。影响成本的因素有四个：生成计划所用的 CPU 时间 (cputime)；从磁盘读取的页数 (ioread)；写入磁盘的页数 (iowrite)；以及批处理的查询计划所占的内存页数 (pagecount)。这样，查询计划成本可表示为（f 是一个数学函数）：

Query plan cost c = f(cputime, ioread, iowrite) / pagecount

以下说明了在 SQL Server 2000 中，批处理的执行上下文如何带来成本开销。上方等式中单独的成本 c 将针对批处理中的每个语句进行计算，并加以累计。但是请注意，单独成本现在是语句初始化成本，而非语句编译或执行成本。

有时，惰性写入器进程会清除计划缓存并减少成本。将成本除于四，并根据需要四舍五入。（例如，25 --> 6 --> 1 --> 0。）当内存方面有压力时，成本为零的查询计划和执行上下文将从计划缓存中删除。重用查询计划或执行上下文时，其成本会被重置回编译（或执行上下文生成）成本。特殊查询的查询计划成本总是以 1 为单位逐渐递增。因此，频繁执行的批处理的查询计划在计划缓存中的存在时间要长于不频繁执行的批处理的计划。

SQL Server 2005：特殊查询的成本为零。另外，查询计划的成本用于度量生成其所需的资源量。尤其，该成本按“记号数”计算，最大值为 31，共由三部分组成：

成本 = I/O 成本 + 上下文开关成本（用于度量 CPU 成本） + 内存成本

该成本各部分的计算方法如下。

在 SQL Server 2005 中，计划缓存不同于数据缓存。此外，还有一些特定于功能的缓存。在 SQL Server 2005 中，惰性写入器进程不会增加成本。相反，只要计划缓存的大小达到缓冲池大小的 50%，下一个计划缓存访问就会以 1 为单位减少所有计划的记号数。请注意，由于这种减少情况是随为查找计划而访问计划缓存的线程而产生的，因此可认为这一减少是以惰性方式进行的。在 SQL Server 2005 中，假如所有缓存大小的总和达到或超过了缓冲池大小的 75%，那么将激活一个专用的资源监视器线程，它将减少所有缓存中的所有对象的记号数。（所以该线程的行为方式与 SQL Server 2000 中的惰性写入器线程大致相同。）查询计划重用导致查询计划成本被重置为初识值。

五、文本其他部分的内容说明

到此，读者应该清楚：为了获得良好的 SQL Server 批处理执行性能，需要执行下列这两项操作：

下一节描述了有关查询计划重用的详细信息。同时，在适当位置给出了可实现更好的计划重用的最佳实务。再下一节中，我们介绍了可能导致重新编译次数增多的一些常见方案，并给出了避免发生这种情况的最佳实务。

六、查询计划重用

计划缓存包含查询计划和执行上下文。概念上，查询计划同与之相关联的执行上下文相链接。批处理 S 的查询计划重用独立于 S 本身（比如：查询文本或存储过程名称）以及该批处理的一些外部因素（比如：生成 S 的用户名，生成 S 的 SET 选项，与 S 相关联的连接的 SET 选项等等）。有一些外部因素会影响计划重用：只要两个相同的查询在这些因素之一上有所不同，就将无法使用常见计划。而其他外部因素则不会影响计划重用。

大多数影响计划重用的因素都罗列在 sys.syscacheobjects 虚拟表中。下方列表描述了“典型用法”方案中的因素。在某些情况下，条目只会指出何时计划未被缓存（并因此永不被重用）。

通常，假如导致查询计划被缓存的连接的服务器、数据库和连接设置与当前连接的相应设置相同，就会重用查询计划。其次，批处理所引用的对象不要求名称解析。例如，Sales.SalesOrderDetail 不要求名称解析，而 SalesOrderDetail 则相反，因为名为 SalesOrderDetail 的表会存在于多个数据库中。大体上，完全合格的对象名称会为计划重用提供更多的机会。

影响计划重用的因素

请注意，查询计划假如还未被缓存，就不能被重用。 所以，我们将仅明确指出无可缓存性，即表示无重用。

最佳实务：当通过非典型参数值执行存储过程时，“EXEC ...WITH RECOMPILE”可用于确保新的查询计划不会替代使用典型参数值编译的现有的已缓存计划。

“EXEC ...WITH RECOMPILE”还可与用户定义的函数一起使用，但只有在存在 EXEC 关键字时才可以。

回想一下，当 SQL Server 在批处理 B 中执行语句后，重新编译了某些（或所有）语句，这时 B 被重新编译。产生重新编译的原因可分为两大类：

下面两节将详细介绍这两个类别。

导致批处理重新编译的与正确性相关的原因

后面列举了一些导致与正确性相关的重新编译的具体操作。因为必须进行这类重新编译，所以用户可以选择不进行这些操作，或者在 SQL Server 运行的非高峰期执行这些操作。

对象的架构

下列操作将刷新引用特定数据库的计划缓存条目，并随之导致全新编译。

use master 
go 
<-- A query Q that references a database called db1 --> 
go

当 SQL Server 2005 的事务级快照隔离级别开启时，通常会发生计划重用。只要快照隔离级别下的批处理中的语句引用了一个对象（其架构自开启快照隔离模式后即被更改），同时缓存并重用了该语句的查询计划，就会发生语句级重新编译。全新编译的查询计划将被缓存，而该语句本身则会失败（根据相应隔离级别的语义）。假如未缓存查询计划，就会发生编译，随后被编译的查询计划会被缓存，而语句本身则会失败。

SET 选项

正如第 6 节已经提到的，在批处理开始执行后更改下列 SET 选项中的一项或多项，将导致重新编译：ANSI_NULL_DFLT_OFF、ANSI_NULL_DFLT_ON、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、DATEFIRST、DATEFORMAT、FORCEPLAN、LANGUAGE、NO_BROWSETABLE、NUMERIC_ROUNDABORT、QUOTED_IDENTIFIER。

导致批处理重新编译的与计划最优性相关的原因

SQL Server 设计用于当数据库中的数据更改时，生成最佳的查询执行计划。使用 SQL Server 的查询处理器中的统计（直方图）来跟踪数据更改。所以，与计划最优性相关的原因同统计密切相关。

开始详细介绍与计划最优性相关的原因之前，让我们列出不会发生与计划最优性相关的重新编译的情况。

深入介绍查询编译

下方流程图简单明了地描述了 SQL Server 中的批处理编译和重新编译过程。主要的处理步骤如下所示（本文档后面将对各个步骤进行详细介绍）：

与计划最优性相关的重新编译：整体情况

每个 SELECT、INSERT、UPDATE 和 DETELE 语句都访问一个或多个表。表内容因 INSERT、UPDATE 和 DELETE 等操作而发生变化。SQL Server 的查询处理器设计用于通过潜在地生成不同的查询计划（每个查询计划在生成时都是最佳的），来适应这种变化。使用表基数直接跟踪表内容，并使用表列上的统计（直方图）进行间接跟踪。

每个表都有一个与之相关联的重新编译阈值 (RT)。RT 是表中列数的一个函数。在查询编译期间，查询处理器将不载入或载入若干个在查询中引用的表上的统计。这些统计被称为引人关注的统计。对于查询中引用的每个表，已编译的查询计划包含：

阈值交叉测试——执行其用于决定是否要重新编译查询计划——由下列公式定义：

| modctr(snapshot) – modctr(current) | >= RT

modctr(current) 表示修改计数器的当前值，而 modctr(snapshot) 表示查询计划上次编译时修改计数器的值。假如阈值交叉在任何令人关注的统计上取得了成功，那么将重新编译查询计划。在 SQL Server 2000 中，包含该查询的整个批处理都被重新编译；而在 SQL Server 2005 中，仅重新编译上述查询。

假如表或索引视图 T 上没有统计，或者在查询编译期间 T 上现有的统计都不被认为是“令人关注的”，那么仍会仅根据 T 的基数，执行下列阈值交叉测试。

| card(snapshot) – card(current) | >= RT

card(current) 表示当前 T 中的行数，而 card(snapshot) 表示查询计划上次编译时的行数。

下面几节将介绍“整体情况”中引入的几个重要概念。

“令人关注”的统计的概念

对于每个查询计划 P，优化器保存被载入以生成 P 的统计的 ID。请注意，“载入的”集同时包含：

换而言之，查询优化器出于某个原因或其他原因，将载入的统计认作是“令人关注的”。

回想一下，统计可以手动或自动创建或更新。还会因执行下列命令而导致统计更新：

重新编译阈值 (RT)

表的重新编译阈值可部分决定引用该表的查询的重新编译频率。RT 取决于表类型（永久或临时）以及编译查询计划时表中的行数（基数）。在批处理中引用的所有表的重新编译阈值都将随该批处理的查询计划一起保存。

RT 的计算方法如下所示。(n 表示编译查询计划时表的基数。）

表修改计数器（rowmodctr 和 colmodctr）

如上所述，RT 与表所执行的修改次数进行了对比。使用称为 rowmodctr（在 SQL Server 2000 中）和 colmodctr（在 SQL Server 2005 中）的计数器跟踪表所进行的修改次数。　这两种计数器都不是针对具体事务的。例如，假如启动了某个事务，并在表中插入了 100 行，然后再回滚操作，那么对修改计数器所作的更改将不会被回滚。

Rowmodctr (SQL Server 2000)

每个表都有一个 rowmodctr 与之相关联。其值可从 sysindexes 系统表获得。在表或索引视图 T 的一个或多个列上创建的每个统计都有一个 rowmodctr 的快照值与之相关联。无论该统计何时被更新——手动或自动（通过 SQL Server 的自动统计功能），rowmodctr 的快照值也会被刷新。有关 rowmodctr 的详细信息在下方白皮书中有所描述：http://msdn.microsoft.com/library/default.asp url=/nhp/Default.asp contentid=28000409

“阈值交叉”测试中所提到的 Rowmodctr(current) 是 sysindexes 系统表（针对堆栈或聚集索引）在查询编译期间进行测试时所保留的值。

rowmodctr 可在 SQL Server 2005 服务器上使用，但其值总为 0。

补充说明一下，在 SQL Server 2000 中，当 rowmodctr 为 0 时，将无法导致重新编译。

Colmodctr (SQL Server 2005)

与 rowmodctr 不同，每个表列都会保存一个 colmodctr 值（非永久性计算列除外）。同普通列一样，永久性计算列拥有 colmodctr。使用 colmodctr 值，可以更细化地跟踪表的更改。Colmodctr 值对用户不可用；仅供查询处理器使用。

当在表或索引视图 T 的一个或多个列上（通过自动统计功能手动或自动）创建或更新统计时，最左边一列的 colmodctr 的快照值将保存在统计二进制大对象 (stats-blob) 中。

“阈值交叉”测试中所提到的 Colmodctr(current) 在查询编译期间进行测试时保留在 SQL Server 2005 的元数据中的值。

与 rowmodctr 不同，colmodctr 的值是一个不断递增的序列：colmodctr 值从不被重置为 0。

不存在非永久性计算列的 Colmodctr 值。其派生自参与计算的列。

使用 rowmodctr 和 colmodctr 跟踪表和索引视图的更改

由于 rowmodctr 和 colmodctr 值用于做出重新编译决定，因此它们的值被当作表更改来进行修改。在下列描述中，我们仅提到了表。但是，相同的情况也适用于索引视图。可通过下列语句更改表：INSERT、DELETE、UPDATE、大容量插入和表截断。下列表定义了修改 rowmodctr 和 colmodctr 值的方式。

两种特殊情况

与计划最优性相关的重新编译在下列两种特殊情况中的处理方式有所不同。

特殊情况 1：在空表或索引视图上创建的统计

SQL Server 2005 处理下列情况的方式不同于 SQL Server 2000。用户创建了一个空表 T。然后又在 T 一个或多个列上创建了一个统计 S。由于 T 为空，因此统计二进制大对象（直方图）为 NULL，但已经在 T 上创建了统计。假设在查询编译期间已发现 S 是“令人关注的”。根据重新编译阈值的“500 行”规则，只有至少包含 500 行，T 才会在 SQL Server 2000 上导致重新编译。所以，假如 T 包含的行不足 500，用户可能使用欠优化的计划。

SQL Server 2005 可检测到这种特殊情况，并以不同的方式进行处理。在 SQL Server 2005 中，这种表或索引视图的重新编译阈值为 1。换句话说，即使仅在 T 中插入一行，也可能导致重新编译。发生这种重新编译时，S 将被更新，同时 S 的直方图不再为 NULL。然而，这一重新编译附带了重新编译阈值 (500 + 0.20 * n)的一般规则。

在 SQL Server 2005 中，即使发生下列情况，重新编译阈值始终为 1：(1) T 没有统计；或者 (2) T 没有在查询编译期间被认作是“令人关注的”统计。

特殊情况 2：触发器重新编译

导致重新编译的与计划最优性相关的所有原因都适用于触发器。另外，由于已插入或已删除的表中的行数在不同的触发器执行间发生巨大变化，也会对触发器产生与计划最优性相关的重新编译。

回想一下，影响一行或多行的触发器会被单独缓存。已插入和已删除的表中的行数通过触发器的查询计划进行保存。这些数字反映了导致计划缓存的触发器执行的行数。假如后续的触发器执行产生了拥有“截然不同的”行数的已插入或已删除的表，那么将对该触发器进行重新编译（并缓存带有新行数的全新的查询计划）。

在 SQL Server 2005 中，“截然不同”的定义如下：

| log10(n) – log10(m) | > 1         if m > n 
| log10(n) – log10(m) | > 2.1      otherwise

其中 n 是已缓存查询计划中的已插入或已删除表的行数，而 m 是当前的触发器执行的对应表的行数。假如同时存在“已插入”和“已删除”的表，将对两者分别执行上面提到的测试。

举一个计算示例，从 10 到 100 的行数更改不会导致重新编译，而从 10 到 101 的更改则完全相反。

在 SQL Server 2000 中，“截然不同”的定义如下：

| log10(n+5) – log10(m+5) | >= 1

其中 n 和 m 的定义同上。请注意，根据这个公式，在 SQL Server 2000 中将已插入或已删除的表的基数从 5 改为 95，将导致重新编译，而从 5 到 94 的更改则不然。

识别与统计相关的重新编译

可通过包含字符串“Statistics changed”的事件探查器跟踪（将在本文后面介绍）的“EventSubClass”列来识别与统计相关的重新编译。

结束语

与本文档的主题没有直接相关的一个问题是：给定的多个统计以相同的顺序存在于一组相同的列中，那么在查询优化期间，查询优化器如何决定所要载入的统计呢？答案并不那么简单，但查询优化器采用如下原则：为最近的统计提供比较旧的统计更高的优先权；为使用 FULLSCAN 选项计算得出的统计提供比用样例计算得出的统计更高的优先权；等等。

与计划最优性相关的编译、重新编译和统计创建/更新间的“因果”关系可能会造成混淆。回想一下，统计可通过手动或自动方式创建或更新。只有编译和重新编译才会导致统计的自动创建或更新。另一方面，当（手动或自动）创建或更新一个统计时，重新编译查询计划（可能会发现该统计“令人关注”）的概率将增大。

最佳实务

下面给出了四个用于减少与计划最优性相关的批处理重新编译的最佳实务：

最佳实务：因为表变量的基数发生变化不会导致重新编译，所以可考虑使用表变量来替代临时表。然而，由于查询优化器不跟踪表变量的基数，同时不在表变量上创建或维护统计，因此不可能产生最佳的查询计划。用户必须确认情况是否如此，并适当地加以权衡。

最佳实务：KEEP PLAN 查询提示可改变临时表的重新编译阈值，使之与永久表的重新编译阈值相同。所以，假如对临时表的更改会导致大量的重新编译，就可使用此查询提示。可使用下列语法指定该提示：

SELECT B.col4, sum(A.col1) 
FROM dbo.PermTable A INNER JOIN #TempTable B ON A.col1 = B.col2 
WHERE B.col3 < 100 
GROUP BY B.col4 
OPTION (KEEP PLAN)

最佳实务：为了完全避免因与计划最优性相关的（与统计更新相关的）原因而导致的重新编译，可使用下列语法指定 KEEPFIXED PLAN 查询提示：

SELECT c.TerritoryID, count(*) as Number, c.SalesPersonID 
FROM Sales.Store s INNER JOIN Sales.Customer c 
ON s.CustomerID = c.CustomerID 
WHERE s.Name LIKE '%Bike%' AND c.SalesPersonID > 285 
GROUP BY c.TerritoryID, c.SalesPersonID 
ORDER BY Number DESC 
OPTION (KEEPFIXED PLAN)

运用这一选项，只有与正确性相关的原因（例如，语句更改所引用的表的架构，或用 sp_recompile 过程标记的表）才会导致重新编译。

在 SQL Server 2005 中，下方所述的行为方式略有不同。假设带有 OPTION(KEEPFIXED PLAN) 提示的查询首次被编译，而这一编译会导致统计的自动创建。假如 SQL Server 2005 可获得一个特殊的“统计锁”，那么就会发生重新编译并自动创建统计。假如无法获得“统计锁”，就会不产生重新编译，并在没有该统计的情况下编译查询。在 SQL Server 2000 中，出于与统计相关的原因，带有 OPTION(KEEPFIXED PLAN) 的查询从不会被重新编译，所以在这种情况下，不会尝试获取“统计锁”或自动创建统计。

最佳实务：对表或索引视图上定义的索引和统计关闭统计自动更新，将确保因这些对象所导致的与计划最优性相关的重新编译将停止。但是请注意，用这种方法关闭“自动统计”功能通常并不是一个好办法，因为查询优化器不再响应这些对象中的数据变更，并可能导致次最佳查询计划。不到万不得已不要采用这种方法。

八、编译、重新编译和并发

在 SQL Server 2000 中，存储过程、触发器和动态 SQL 的编译和重新编译均被串行化。例如，假定使用“EXEC dbo.SP1”提交了一个存储过程用以执行。并假设当 SQL Server 编译 SP1 时，收到了另一个引用相关存储过程的请求“EXEC dbo.SP1”。第二个请求将等到第一个请求完成 SP1 的编译，然后尝试重用结果查询计划。在 SQL Server 2005 中，编译被串行化，而重新编译则不会。换句话说，相同存储过程的两个并发重新编译可能会继续。最后结束的重新编译请求将替代由另一个请求生成的查询计划。

九、编译、重新编译和参数嗅探

“参数嗅探”是一个过程，通过这一过程，SQL Server 的执行环境可在编译或重新编译时“嗅探”当前参数值，并将之传递给查询优化器，以用于生成更快的查询执行计划。“当前”一词指导致编译或重新编译的语句调用中所存在的参数值。在 SQL Server 2000 和 SQL Server 2005 中，将在编译或重新编译下列批处理类型时嗅探参数值：

在 SQL Server 2005 中，这一操作被扩展到使用 OPTION(RECOMPILE) 查询提示提交的查询上。对于这种查询（可以是 SELECT、INSERT、UPDATE 或 DELETE），将同时嗅探本地变量的参数值和当前值。在批处理中，所嗅探到的（参数和本地变量的）值后面紧跟着带有 OPTION(RECOMPILE) 提示的语句。尤其对于参数来说，不会嗅探批处理调用所附带的值。

SQL Server 的事件探查器使得识别导致重新编译的批处理变得很简单。启动一个新的事件探查器跟踪，并在存储过程事件类别下，选择下列事件。（为了减少所生成的数据量，建议用户取消选定其他事件。）

此外，为了检测与统计更新相关的重新编译，可选择“对象”类别下的“自动统计”事件。

现在，启动 SQL Server 2005 Management Studio，并执行下列 T-SQL 代码：

use AdventureWorks          -- On SQL Server 2000, say "use pubs" 
go 
drop procedure DemoProc1 
go 
create procedure DemoProc1 as 
create table #t1 (a int, b int) 
select * from #t1 
go 
exec DemoProc1 
go 
exec DemoProc1 
go

暂停事件探查器跟踪，并将看到下列事件。

该事件序列指示“select * from #t1”为导致重新编译的语句。EventSubClass 列指出了进行重新编译的原因。在这种情况下，当 DemoProc1 在开始执行之前被编译，就可对“create table”语句进行变异。后续的“select”语句可能不会被编译，因为其引用了一个在初始编译时不存在的临时表 #t1。因此，DemoProc1 的已编译计划是不完整的。当 DemoProc1 开始执行时，随即创建了 #t1，然后就可以对“select”语句进行编译。由于 DemoProc1 已经执行，因此根据我们对重新编译的定义，这一编译可视为重新编译。此重新编译的真正原因是“延迟编译”。

请注意，有趣的一点是：当再次执行 DemoProc1 时，查询计划将不再是不完整的。重新编译已经将 DemoProc1 的一个完整的查询计划插入计划缓存中。所以，第二次执行过程中未发生任何重新编译。

SQL Server 2000 在这方面的情况也相同。

通过选择下列这组跟踪事件，也可以识别导致重新编译的批处理。

假如在选择了这组新的跟踪事件后执行了刚才所述的例子，那么跟踪输出将如下所示。

在此请注意，导致重新编译的语句将在 SP:Recompile 事件后被输出。这种方法不如第一种方法直接了当。因此，之后应跟踪第一组事件探查器跟踪事件。

为了看到所有针对 SP:Recompile 事件报告的可能导致重新编译的原因，请在 SQL Server 2005 上发出下列查询：

select v.subclass_name, v.subclass_value 
from sys.trace_events e inner join sys.trace_subclass_values v  
on e.trace_event_id = v.trace_event_id 
where e.name = 'SP:Recompile'

上述查询的输出如下。（仅输出不带有阴影的列；带有阴影的列用于提供其他详细信息。）

在 SQL Server 2000 中，EventSubClass 列包含从 1 到 6 的整数值，意义与上表所列的内容相同。SQL Server 2005 新增了最后两个类别。

对本节所述的两个例子，SQL Server 2000 上的跟踪输出与 SQL Server 2005 相同，除了在 SQL Server 2000 上，EventSubClass 列包含“3”而非字符串“Deferred compile”。从内部来说，语句级重新编译发生在 SQL Server 2005 上，因此，仅有“select * from #t1”在 SQL Server 2005 上进行重新编译，而在 SQL Server 2000 上，整个 DemoProc1 都将被重新编译。

因混用 DDL 和 DML 而导致重新编译

在批处理或存储过程中混用数据定义语言 (DDL) 和数据操作语言 (DML) 语句并不是一个好办法，因为这会引起不必要的重新编译。下面这个例子运用存储过程阐述了这一点。（批处理也会发生同样的情况。但是，由于 SQL Server 2005 事件探查器没有提供必要的跟踪事件，因此无法对其进行实时观测。）创建下列存储过程。

drop procedure MixDDLDML 
go 
create procedure MixDDLDML as 
create table tab1 (a int)            -- DDL 
select * from tab1                   -- DML 
create index nc_tab1idx1 on tab1(a)  -- DDL 
select * from tab1                   -- DML 
create table tab2 (a int)            -- DDL 
select * from tab2                   -- DML 
go 
exec MixDDLDML 
go

在事件探查器跟踪输出中，可观测到下列事件。

这里说明了 MixDDLDML 是如何编译的。

总之，在这个例子中，SQL Server 2000 和 SQL Server 2005 中都发生了三次重新编译。但是，SQL Server 2005 的重新编译成本要低于 SQL Server 2000，因为前者属于语句级而非存储过程级重新编译。

假如根据下方所示来编写存储过程，那么将观察到有趣的现象。

create procedure DDLBeforeDML as 
create table tab1 (a int)            -- DDL 
create index nc_tab1idx1 on tab1(a)  -- DDL 
create table tab2 (a int)            -- DDL 
select * from tab1                   -- DML 
select * from tab1                   -- DML 
select * from tab2                   -- DML 
go 
exec DDLBeforeDML 
go

在事件探查器跟踪输出中，可观察到下列事件。

在 SQL Server 2005 中，出于语句级重新编译，仍会发生这三次重新编译。与 MixDDLDML 存储过程相比，重新编译的次数并没有减少。假如在 SQL Server 2000 上尝试相同的例子，重新编译的次数将从 3 次减少到 1 次。在 SQL Server 2000 中，重新编译在存储过程级上进行，因而可以一次性编译三个“select”语句。总之，与 SQL Server 2000 相比，SQL Server 2005 的重新编译工作量没有增加，但是重新编译的次数却增多了。

下面，考虑以下 T-SQL 代码段：

-- dbo.someTable will be used to populate a temp table 
-- subsequently. 
create table dbo.someTable (a int not null, b int not null) 
go 
declare @i int 
set @i = 1 
while (@i <= 2000) 
begin 
    insert into dbo.someTable values (@i, @i+5) 
    set @i = @i + 1 
end 
go 
  
-- This is the stored procedure of main interest. 
create procedure dbo.AlwaysRecompile 
as  
set nocount on 
  
-- create a temp table 
create table #temp1(c int not null, d int not null) 
  
select count(*) from #temp1 
  
-- now populate #temp1 with 2000 rows 
insert into #temp1 
select * from dbo.someTable 
  
-- create a clustered index on #temp1 
create clustered index cl_idx_temp1 on #temp1(c) 
  
select count(*) from #temp1 
go

在 SQL Server 2000 中，当首次执行这个存储过程时，将对第一个“select”语句生成第一个 SP:Recompile 事件。这是一次延迟编译，不是真正的重新编译。第二个 SP:Recompile 事件针对第二个“select”。当发生第一次重新编译时，第二个“select”也会被编译，因为在 SQL Server 2000 中，编译是在批处理级别上进行的。然后，在执行时，#temp1 的架构因新建的聚集索引而发生了变化。所以，产生第二个 SP:Recompile 的原因是架构更改。

因行修改次数而导致的重新编译

考虑下方存储过程及其执行。

use AdventureWorks   -- or say "use pubs" on SQL Server 2000 
go 
create procedure RowCountDemo 
as 
begin 
    create table #t1 (a int, b int) 
 
    declare @i int 
    set @i = 0    while (@i < 20) 
    begin 
       insert into #t1 values (@i, 2*@i - 50) 
 
       select a 
       from #t1  
       where a < 10 or ((b > 20 or a >=100) and (a < 10000)) 
       group by a 
  
       set @i = @i + 1 
    end 
end 
go 
exec RowCountDemo 
go

回想一下，当表在计算阈值时为空，临时表的重新编译阈值为 6。当执行 RowCountDemo 时，在 #t1 包含整 6 行后，可观察到与“statistics changed”（统计被更改）相关的重新编译。通过更改“while”循环的上限，可观察到更多的重新编译。

因 SET 选项更改而导致的重新编译

考虑下列存储过程。

use AdventureWorks 
go 
create procedure SetOptionsDemo as 
begin 
    set ansi_nulls off 
    select p.Size, sum(p.ListPrice) 
    from Production.Product p  
         inner join Production.ProductCategory pc 
          on p.ProductSubcategoryID = pc.ProductCategoryID 
    where p.Color = 'Black' 
    group by p.Size 
end 
go 
exec SetOptionsDemo    -- causes a recompilation 
go 
exec SetOptionsDemo    -- does not cause a recompilation 
go

当执行 SetOptionsDemo 时，在“ansi_nulls”为 ON 的情况下编译“select”查询。当 SetOptionsDemo 开始执行时，该 SET 选项的值将由于“set ansi_nulls off”而发生变化，因而已编译的查询计划将不再“有效”。所以，将在“ansi_nulls”为 OFF 的情况下进行重新编译。第二次执行不会导致重新编译，因为已缓存的计划将在“ansi_nulls”为 OFF 的情况下进行编译。

表明 SQL Server 2005 所需的重新编译较 SQL Server 2000 多的另一个示例

考虑下列存储过程。

use AdventureWorks     -- say "use pubs" on SQL Server 2000 
go 
create procedure CreateThenReference as 
begin 
   -- create two temp tables 
   create table #t1(a int, b int) 
   create table #t2(c int, d int) 
 
   -- populate them with some data 
   insert into #t1 values (1, 1) 
   insert into #t1 values (2, 2) 
   insert into #t2 values (3, 2) 
   insert into #t2 values (4, 3) 
 
         -- issue two queries on them 
   select x.a, x.b, sum(y.c) 
   from #t1 x inner join #t2 y on x.b = y.d 
   group by x.b, x.a 
   order by x.b 
  
   select *  
   from #t1 z cross join #t2 w 
   where w.c != 5 or w.c != 2 
end 
go 
exec CreateThenReference 
go

在 SQL Server 2005 中，CreateThenReference 的第一次执行导致了六项语句级重新编译：其中有四项针对“insert”语句，有两项针对“select”查询。当该存储过程开始执行时，最初的查询计划不包含针对“insert”或“select”语句的计划，因为其所引用（临时表 #t1 和 #t2）的对象还不存在。创建了 #t1 和 #t2 之后，将编译“insert”和“select”的查询计划，而这些编译被视为重新编译。在 SQL Server 2000 中，由于整个存储过程被立即重新编译，因此仅发生一次（存储过程级）重新编译——第一个“insert”开始执行时所引发的重新编译。这时，整个存储过程都被重新编译，而因为 #t1 and #t2 已经存在，可一次性对后续的“insert”和“select”进行编译。显而易见，通过添加更多引用诸如 #t1 和 #t2 等对象的语句，SQL Server 2005 中的语句级重新编译次数可无限增加。

十一、工具与命令

本节介绍了用于观测和调试重新编译的各种工具和命令。

Sys.syscacheobjects 虚拟表

虽然可以从任何数据库进行查询，但该虚拟表理论上仅存在于 master 数据库中。该虚拟表的 cacheobjtype 列特别有趣。当 cacheobjtype = "Compiled Plan"，相应的行将引用一个查询计划。当 cacheobjtype = "Executable Plan"，相应的行将引用一个执行上下文。正如我们前面所说明的，每个执行上下文必须有自己的关联查询计划，反之则不然。所涉及的另一列是 objtype 列：指示其计划被缓存的对象的类型（比如：“Adhoc”、“Prepared”和“Proc”）。setopts 列编码了一个位图，指示在编译计划时生效的 SET 选项。有时，相同的已编译计划（仅 setopts 列有所不同）的多个副本被缓存在一个计划缓存中。这表示不同的连接正在使用几组不同的 SET 选项——通常属于不该发生的情况。usecounts 列保存了自对象被缓存以来已缓存对象被重用的次数。

请参考 BOL 了解有关此虚拟表的更多信息。

DBCC FREEPROCCACHE

此命令可删除计划缓存中的所有已缓存的查询计划和执行上下文。不应在生产服务器上运行该命令，因为它反过来会影响正在运行的应用程序的性能。在对重新编译问题进行故障诊断时，该命令对于控制计划缓存的内容很有用。

DBCC FLUSHPROCINDB( db_id )

此命令可删除特定数据库的计划缓存中的所有已缓存计划。不应在生产服务器上运行该命令，因为它反过来会影响正在运行的应用程序的性能。

事件探查器跟踪事件

下列事件探查器跟踪事件涉及观测和调试计划缓存、编译和重新编译行为。

性能计数器

在调试可能因过度编译和重新编译所导致的性能问题时，涉及下列性能计数器的值。

SQL Server 2005 可缓存提交给其以执行的各种语句类型的查询计划。查询计划缓存可导致查询计划重用，避免编译罚点，并更好地运用计划缓存。一些编码方法会阻碍查询计划缓存和重用，因此应加以避免。SQL Server 可发现查询计划重用的机会。特别是，查询计划会因下面这两个原因而无法重用：(a) 出现在查询计划中的对象架构会发生变化，从而导致计划无效；(b) 查询计划所引用的表中的数据所发生的变化足以使计划变成非最佳的。SQL Server 可在查询执行时发现这两类情况，并根据需要对整个或部分批处理进行重新编译。不良的 T-SQL 编码方法会增加重新编译的频率，从而反过来影响 SQL Server 的性能。在许多情况下，都可以对这类情况进行调试和纠正。

附录 A：SQL Server 2005 何时不自动参数化查询？

自动参数化是一个过程，SQL Server 通过这个过程将出现在 SQL 语句中的文本常量替换为诸如 @p1 和 @p2 等参数。然后，SQL 语句的已编译计划以参数化的形式被缓存在计划缓存中，以便后续的语句（只是在文本常量的值上有所不同）可重用已缓存的计划。正如第四部分所提到的，只有参数值不影响查询计划选择的 SQL 语句才会被自动参数化。

SQL Server 的 LPE（语言处理和执行）组件可参数化 SQL 语句。当发现文本常量的值不影响查询计划选择时，QP（查询处理器）组件将声明 LPE 的自动参数化尝试是“安全的”，并继续执行自动参数化；否则，将声明自动参数化是“不安全的”，并将其中止。在第 11.5 节提到的一些性能计数器的值（‘SQLServer:SQL 统计’类别）报告了有关自动参数化的统计信息。

下方列表列举了 SQL Server 2005 不对其进行自动参数化的语句类型。

UPDATE Sales.Customer 
SET CustomerType = N'S' 
WHERE CustomerType = @a

WHERE 20 > 5