摘要：本文阐述了批处理在 SQL Server 2005 中的缓存和重用方式，并就最大限度重用已缓存的计划提供了相应的最佳实务。另外，文中还说明了一些重新编译批处理的方案，并针对减少或消除不必要的重新编译，给出了最佳实务。

本白皮书的目的
此白皮书的目的有几个。阐述了批处理在 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 2000 相比，这一“语句级重新编译”功能将改善 SQL Server 2005 的重新编译行为。尤其，在批处理重新编译过程中，SQL Server 2005 所用的 CPU 时间和内存更少，而得到的编译块也更少。
语句级重新编译有一个优点显而易见：不必再只是为了减少较长的存储过程的重新编译罚点，而将其分成多个较短的存储过程。

计划缓存
在处理重新编译问题之前，本文将用较大的篇幅来讨论查询计划缓存的相关及重要的主题。缓存计划以便重用。如果未缓存查询计划，重用机会将为零。这种计划将在每次执行时被编译，从而导致性能低下。只有在极少数情况下，才不需要进行缓存。本文将在后面指出这些情况。
SQL Server 可缓存许多批处理类型的查询计划。以下列举了这些类型。对于每种类型，我们都说明了重用计划的必要条件。请注意，这些条件不一定充分。稍后，读者将在本文中看到完整的相关内容。
1.
特殊查询。特殊查询是一种包含 SELECT、INSERT、UPDATE 或 DELETE 语句的批处理。SQL Server 要求两个特殊查询的文本完全匹配。文本必须在大小写和空格上都匹配。例如，下列这两个查询不共享相同的查询计划。（出现在本白皮书中的所有 T-SQL 代码段都在 SQL Server 2005 的 AdventureWorks 数据库中。）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
2.
自动参数化查询。对于某些查询，SQL Server 2005 将常量文本值替换为变量，并编译查询计划。如果后续的查询仅在常量的值上有所不同，那么其将与自动参数化查询相匹配。通常，SQL Server 2005 会对那些查询计划不取决于常量文本的特定值的查询进行自动参数化处理。
附录 A 包含一个语句类型列表，SQL Server 2005 将不对其上列出的语句类型进行自动参数化处理。
作为 SQL Server 2005 中自动参数化的一个示例，下列这两个查询可重用查询计划：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
当出现在查询中的常量文本的值会影响查询计划时，将不对该查询进行自动参数化处理。这类查询的查询计划将被缓存，但同时会插入常量，而不是占位符（比如：@p1）。
SQL Server 的“showplan”功能可用于确定是否已对查询进行了自动参数化处理。例如，可在“set showplan_xml on”模式下提交查询。如果结果 showplan 包含诸如 @p1 和 @p2 等占位符，那么将对查询进行自动参数化处理；否则将不进行自动参数化处理。XML 格式的 SQL Server showplan 还包含在编译时（‘showplan_xml’和‘statistics xml’模式）和执行时（仅‘statistics xml’模式）的参数值的相关信息。
3.
sp_executesql 过程。这是促进查询计划重用的方法之一。当使用 sp_executesql 时，用户或应用程序将明确地确定参数。例如：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
可通过逐一列举来指定多个参数。实际的参数值遵照相应的参数定义。根据查询文本（sp_executesql 后的第一个参数）的匹配情况以及查询文本（上例中的 N'@a int'）后面所带的所有参数来预测计划重用机会。不考虑参数值（170 和 1201）的文本是否匹配。因此，在上述例子中，两个 sp_executesql 语句会发生计划重用。
4.
预备的查询.该方法——类似于 sp_executesql 方法——还促进了查询计划重用。在“准备”时发送批处理文本。SQL Server 2005 通过返回一个句柄（可用于在执行时调用批处理）进行响应。在执行时，一个句柄和一些参数值会被发送到服务器。ODBC 和 OLE DB 通过 SQLPrepare/SQLExecute 和 ICommandPrepare 显露该功能。例如，使用 ODBC 的代码段可能如下所示：SQLPrepare(hstmt, "SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
WHERE SalesOrderID
5.
存储过程（含触发器）。存储过程设计用于促进计划重用。计划重用基于存储过程或触发器的名称。（但是，无法直接调用触发器。）SQL Server 在内部将存储过程的名称转化为 ID，而随后的计划重用将根据该 ID 的值来进行。触发器的计划缓存和重新编译行为与存储过程略有不同。我们将在本文档的适当位置指出这些不同之处。
当首次编译一个存储过程时，执行调用所提供的参数的值被用于优化该存储过程中的语句。这个过程被称为“参数嗅探”。如果这些值都是典型的，那么针对该存储过程的所有调用将从一个高效的查询计划中获益。本文随后将讨论可用于防止缓存带有[屏蔽]型的存储过程参数值的查询计划。
6.
批处理.如果批处理文本完全匹配，那么将对相应的批处理进行查询计划重用。文本必须在大小写和空格上都匹配。
7.
通过 EXEC ( ...) 执行查询。SQL Server 2005 可缓存通过 EXEC 提交的字符串以便执行。这些字符串称为“动态 SQL”。例如：EXEC ( 'SELECT *' + ' FROM Production.Product pr??
INNER JOIN Production.ProductPhoto ph' + '??
ON pr.ProductID = ph.ProductPhotoID' +??
' WHERE pr.MakeFlag = ' + @mkflag )
计划重用基于在执行语句时将变量（比如：上例中的 @mkflag ）替换为其实际值后得到的连锁字符串。
多级缓存
认识到多“级”缓存匹配将[屏蔽]进行，这一点很重要。举个例子。假设批处理 1（非存储过程）包含下列语句（及其他）：EXEC dbo.procA
批处理 2（也不是存储过程）与批处理 1 在文本上不相匹配，但包含引用相同存储过程的 “EXEC dbo.procA”。这里，批处理 1 和批处理 2 的查询计划不相匹配。然而，只要在这两个批处理的一个中执行 “EXEC dbo.procA”，同时在执行当前批处理之前执行了另一个批处理，而 procA 的查询计划仍存在于计划缓存中，就有可能实现 procA 的查询计划重用。但是，每次单独执行 procA 都会得到执行上下文。该执行上下文要么刚刚生成（如果正在使用所有现有的执行上下文），要么被重用（如果未使用的执行上下文可用）。即使使用 EXEC 执行了动态 SQL，或者在批处理 1 和批处理 2 内部执行了自动参数化语句，也有可能产生某种类型的重用。总之，下列三类批处理会启动他们自己的“级别”（无论任何包含级别中是否存在缓存匹配，都会在这些级别中产生缓存匹配）：
?
存储过程执行（比如：“EXEC dbo.stored_proc_name”）
?
动态 SQL 执行（比如：“EXEC query_string”）
?
自动参数化查询
对于上述规则，存储过程是一个例外。例如，如果两个不同的存储过程都包含“EXEC procA”语句，那么就不会产生 procA 的查询计划和执行上下文重用。
查询计划和执行上下文
当一个可缓存的批处理被提交给 SQL Server 2005 进行执行时，该批处理会被编译，而它的一个查询计划会被放到计划缓存中。查询计划是一种只读的可重入结构（由多个用户共享）。任何时候，查询计划在计划缓存中最多只能有两个实例：一个用于所有的串行执行，另一个用于所有的并行执行。并行执行的副本适用于所有的并行级别。（严格说来，如果相同的用户使用带有相同会话选项的两个不同会话设置的两个相同的查询同时达到 SQL Server 2005，在执行时将存在两个查询计划。但是，当执行结束时，仅有一个查询计划会保留在计划缓存中。）
执行上下文是从查询计划中派生的。执行上下文是为生成查询结果而“执行”的。执行上下文也被缓存和重用。当前执行批处理的每位用户将拥有一个执行上下文，其中保存了特定于其执行的数据（比如：参数值）。虽然被重用，但是执行上下文并不是可重入的（例如，它们是单线程的）。也就是说，在任何时候，一个执行上下文只能执行一个由会话提交的批处理，而在执行时，相应的上下文不会提供给任何其他会话或用户。
查询计划与从中派生的执行上下文之间的关系如下图所示。其中，有一个查询计划，从中派生了三个执行上下文。这些执行上下文包含参数值和特定于用户的信息。对于参数值和特定于用户的信息而言，查询计划都不是明确的。




模式名称
存在已缓存的查询计划时
存在已缓存的查询计划时
不存在已缓存的查询计划时
不存在已缓存的查询计划时

查询计划
执行上下文
查询计划
执行上下文
showplan_text, showplan_all, showplan_xml
被重用（无编译）
被重用
被缓存（编译）
生成一个执行上下文，对其进行缓存但不使用它
statistics profile, statistics xml, statistics io, statistics time
被重用（无编译）
不被重用生成并使用一个全新的执行上下文，但不对其进行缓存
被缓存（编译）
生成并使用一个全新的执行上下文，但不对其进行缓存
noexec
被重用（无编译）
被重用
被缓存（编译）
不生成执行上下文（由于“noexec”模式）。
parseonly（例如，在查询分析器或 Management Studio 中按“分析”按钮）
无
无
无
无
查询计划和执行上下文的相关成本
对于每个查询计划和执行上下文，都会保留一个成本。该成本会在一定程度上控制计划或上下文在计划缓存中的存在时间。在 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 成本） + 内存成本
该成本各部分的计算方法如下。
?
两个 I/O 的成本为 1 个记号，最大成本为 19 个记号。
?
另个上下文开关的成本为 1 个记号，最大成本为 8 个记号。
?
十六个内存页 (128 KB) 的成本为 1 个记号，最大成本为 4 个记号。
在 SQL Server 2005 中，计划缓存不同于数据缓存。此外，还有一些特定于功能的缓存。在 SQL Server 2005 中，惰性写入器进程不会增加成本。相反，只要计划缓存的大小达到缓冲池大小的 50%，下一个计划缓存访问就会以 1 为单位减少所有计划的记号数。请注意，由于这种减少情况是随为查找计划而访问计划缓存的线程而产生的，因此可认为这一减少是以惰性方式进行的。在 SQL Server 2005 中，如果所有缓存大小的总和达到或超过了缓冲池大小的 75%，那么将激活一个专用的资源监视器线程，它将减少所有缓存中的所有对象的记号数。（所以该线程的行为方式与 SQL Server 2000 中的惰性写入器线程大致相同。）查询计划重用导致查询计划成本被重置为初识值。

文本其他部分的内容说明
到此，读者应该清楚：为了获得良好的 SQL Server 批处理执行性能，需要执行下列这两项操作：
?
应尽可能重用查询计划。这可避免不必要的查询编译成本。计划重用还会带来更高的计划缓存使用率，而反过来又会实现更好的服务器性能。
?
应避免可能造成查询编译次数增多的操作。减少重新编译次数可节省服务器资源（CPU 和内存），并增加批处理执行次数，同时达到预期性能。
下一节描述了有关查询计划重用的详细信息。同时，在适当位置给出了可实现更好的计划重用的最佳实务。再下一节中，我们介绍了可能导致重新编译次数增多的一些常见方案，并给出了避免发生这种情况的最佳实务。
查询计划重用
计划缓存包含查询计划和执行上下文。概念上，查询计划同与之相关联的执行上下文相链接。批处理 S 的查询计划重用[屏蔽]于 S 本身（比如：查询文本或存储过程名称）以及该批处理的一些外部因素（比如：生成 S 的用户名，生成 S 的 SET 选项，与 S 相关联的连接的 SET 选项等等）。有一些外部因素会影响计划重用：只要两个相同的查询在这些因素之一上有所不同，就将无法使用常见计划。而其他外部因素则不会影响计划重用。
大多数影响计划重用的因素都罗列在 sys.syscacheobjects 虚拟表中。下方列表描述了“典型用法”方案中的因素。在某些情况下，条目只会指出何时计划未被缓存（并因此永不被重用）。
通常，如果导致查询计划被缓存的连接的服务器、数据库和连接设置与当前连接的相应设置相同，就会重用查询计划。其次，批处理所引用的对象不要求名称解析。例如，Sales.SalesOrderDetail 不要求名称解析，而 SalesOrderDetail 则相反，因为名为 SalesOrderDetail 的表会存在于多个数据库中。大体上，完全合格的对象名称会为计划重用提供更多的机会。
影响计划重用的因素
请注意，查询计划如果还未被缓存，就不能被重用。 所以，我们将仅明确指出无可缓存性，即表示无重用。
1.
如果一个存储过程在数据库 D1 中执行，那么在不同的数据库 D2 中执行相同的存储过程时，就不会重用其查询计划。请注意，这一行为仅适用于存储过程，并不适用于特殊查询、预备的查询或动态 SQL。
2.
对于触发器执行，受执行影响的行数（1 比 n）——按被插入或被删除的表中的行数度量——是确定计划缓存命中的一个显著因素。请注意，该行为特别针对触发器，并不适用于存储过程。
在 SQL Server 2005 INSTEAD OF 触发器中，“1-plan”由影响 0 和 1 行的执行共享，而对于 non-INSTEAD OF ("after") 触发器，“1-plan”仅由影响 1 行的执行使用，同时“n-plan”由影响 0 和 n 行 (n > 1) 的执行使用。
3.
从不缓存大容量插入语句，但却缓存与大容量插入相关联的触发器。
4.
不缓存包含任何长于 8 KB 的文本的批处理。因此，无法缓存这种批处理的查询计划。（应用常量折叠之后，测量文本的长度。）
5.
标有“复制标记”（与某位复制用户相关联）的批处理与没有该标记的批处理不相匹配。
6.
从 SQL Server 2005 的通用语言运行时 (CLR) 调用的批处理与从 CLR 外部提交的相同批处理不相匹配。然而，两个由 CLR 提交的批处理可重用相同的计划。相同的情况适用于：
?
CLR 触发器和非-CLR 触发器
?
通知查询和非通知查询
7.
不缓存通过 sp_resyncquery 提交的查询的查询计划。所以，如果重新提交了（通过或不通过 sp_resyncquery 提交）该查询，就需要重新进行编译。
8.
SQL Server 2005 允许在 T-SQL 批处理上定义游标。如果批处理被当作单独的语句提交，那么就不会对游标重用（部分）计划。
9.
下列 SET 选项会影响计划重用。
编号
SET 选项名
1
ANSI_NULL_DFLT_OFF
2
ANSI_NULL_DFLT_ON
3
ANSI_NULLS
4
ANSI_PADDING
5
ANSI_WARNINGS
6
ARITHABORT
7
CONCAT_NULL_YIELDS_NULL
8
DATEFIRST
9
DATEFORMAT
10
FORCEPLAN
11
LANGUAGE
12
NO_BROWSETABLE
13
NUMERIC_ROUNDABORT
14
QUOTED_IDENTIFIER
此外，ANSI_DEFAULTS 也会影响计划重用，因为其可用于同时更改下列 SET 选项（其中有一些会影响计划重用）：ANSI_NULLS、ANSI_NULL_DFLT_ON、ANSI_PADDING、ANSI_WARNINGS、CURSOR_CLOSE_ON_COMMIT、IMPLICIT_TRANSACTIONS、QUOTED_IDENTIFIER.
上面这些 SET 选项会影响计划重用，因为 SQL Server 2000 和 SQL Server 2005 都能执行“常量折叠”（在编译时评估常量表达式以实现一些优化），而且这些选项的设置会影响这类表达式的结果。
部分上述 SET 选项的设置罗列在 sys.syscacheobjects 虚拟表中（比如：“langid”和“dateformat”。
请注意，可使用几种方法更改部分上述 SET 选项的值：
?
使用 sp_configure 存储过程（针对服务器范围的更改）
?
使用 sp_dboption 存储过程（针对数据库范围的更改）
?
使用 ALTER DATABASE 语句的 SET 子句
在 SET 选项存在冲突时，用户级和连接级 SET 选项值优先于数据库和服务器级 SET 选项值。另外，如果某个数据库级 SET 选项有效，那么对于引用多个数据库（可能拥有不同的 SET 选项值）的批处理，数据库（该批处理在其上下文中执行）的 SET 选项优先于其他数据库的 SET 选项。
最佳实务：为了避免与 SET 选项相关的重新编译，在连接时创建 SET 选项，并确保其在连接期间不会发生变化。
10.
带有不合格对象名的批处理不会重用查询计划。例如，在“SELECT * FROM MyTable”中，如果 Alice 发出了相应的查询并拥有带有相应名称的表，MyTable 可能会正常地解析到 Alice。同样，MyTable 可能解析到 Bob.MyTable。在这种情况下，SQL Server 不会重用查询计划。但是，如果 Alice 发出了“SELECT * FROM dbo.MyTable”，就不会存在不确定性，因为对象被唯一指定，并可重用查询计划。（请参见 sys.syscacheobjects 中的 uid 列。该列给出了生成计划的连接的用户 ID。只有带有相同用户 ID 的查询计划才可被重用。当 uid = -2 时，表示该查询不依赖于隐式名称解析，并可在不同的用户 ID 间共享。）
11.
当通过“CREATE PROCEDURE ...WITH RECOMPILE”选项创建存储过程时，无论何时执行该存储过程，都不会缓存其查询计划。不存在计划重用的可能性：每次执行这种过程都会导致重新编译。
12.
最佳实务：“CREATE PROCEDURE ...WITH RECOMPILE”可用于标记通过各种参数调用的存储过程，而其最佳的查询计划高度依赖于在调用期间所提供的参数值。
13.
当使用“EXEC ...WITH RECOMPILE”执行存储过程 P 时，P 被重新编译。即使 P 的一个查询计划预先存在于计划缓存中并可被重用，也不会发生重用。不缓存为 P 全新编译的查询计划。
最佳实务：当通过[屏蔽]型参数值执行存储过程时，“EXEC ...WITH RECOMPILE”可用于确保新的查询计划不会替代使用典型参数值编译的现有的已缓存计划。
“EXEC ...WITH RECOMPILE”还可与用户定义的函数一起使用，但只有在存在 EXEC 关键字时才可以。
1.
为了避免通过不同的参数值执行的查询存在多个查询计划，应使用 sp_executesql 存储过程执行该查询。如果同一个查询计划有益于所有或大多数参数值，那么这种方法将很有用。
2.
可缓存临时存储过程（针对会话范围及全局），因而可进行重用。
3.
在 SQL Server 2005 中，不缓存创建或更新统计（手动或自动）的查询的计划。

导致重新编译的原因
回想一下，当 SQL Server 在批处理 B 中执行语句后，重新编译了某些（或所有）语句，这时 B 被重新编译。产生重新编译的原因可分为两大类：
?
与正确性相关的原因。如果不进行重新编译会导致错误的结果或操作，那么就必须对批处理进行重新编译。与正确性相关的原因又可分为两类。
?
对象的架构。 批处理 B 可能会引用许多对象（表、视图、索引、统计、UDF 等等），而如果自 B 上次编译后，某些对象的架构发生了变化，那么就需要重新编译 B 以保证语句正确。
?
SET 选项。 一些 SET 选项会影响查询结果。如果某个影响计划重用的 SET 选项的设置在批处理内被更改，就会发生重新编译。
?
与计划最优性相关的原因。自 B 上次编译之后，B 所引用的表中的数据可能发生巨大的变化。在这种情况下，可能会对 B 进行重新编译，以便获得更快捷的查询执行计划。
下面两节将详细介绍这两个类别。
导致批处理重新编译的与正确性相关的原因
后面列举了一些导致与正确性相关的重新编译的具体操作。因为必须进行这类重新编译，所以用户可以选择不进行这些操作，或者在 SQL Server 运行的非高峰期执行这些操作。
对象的架构
1.
无论批处理引用的任何对象在何时发生了架构更改，批处理都会被重新编译。“架构更改”的定义如下：
?
将列添加或放到表或视图中
?
将约束、默认值或规则添加或放到表中，或者进行相反操作
?
将索引添加到表或索引视图中
?
放置表或索引视图上定义的索引（只要该索引被相应的查询计划所用）
?
(SQL Server 2000)。手动在表上更新或放置统计（不是创建！）将导致重新编译任何使用该表的查询计划。上述查询计划将下次开始执行时进行重新编译。
?
(SQL Server 2005)。手动创建或放置表上定义的统计（不是更新！）将导致重新编译任何使用该表的查询计划（借助表架构版本更改来实现）。上述查询计划将下次开始执行时进行重新编译。
?
请注意，在 SQL Server 2000 或 SQL Server 2005 中，自动创建或自动更新的统计不会导致架构更改，但会强制进行与正确性相关的重新编译。在 SQL Server 2005 中，相同的语句适用于手动更新的统计。但是，这些操作会导致与计划最优性相关的重新编译（针对载入这些统计的查询计划），第 7.2 节对此有所详细描述。
2.
在存储过程或触发器上运行 sp_recompile 会导致它们在下一次执行时被重新编译。在表或视图上运行 sp_recompile 时，所有引用该表或视图的存储过程都将在下一次运行时被重新编译。sp_recompile 通过递增上述对象的磁盘上的架构版本来完成重新编译。
3.
下列操作会刷新整个计划缓存，从而导致对之后所提交的批处理进行全新编译：
?
分离数据库
?
将数据库升级到 SQL Server 2000（在 SQL Server 2000 上）
?
将数据库升级到 SQL Server 2005（在 SQL Server 2005 服务器上）
?
DBCC FREEPROCCACHE 命令
?
RECONFIGURE 命令
?
ALTER DATABASE ...MODIFY FILEGROUP 命令
?
使用 ALTER DATABASE ... 修改排序COLLATE 命令
下列操作将刷新引用特定数据库的计划缓存条目，并随之导致全新编译。
?
DBCC FLUSHPROCINDB 命令
?
ALTER DATABASE ...MODIFY NAME = 命令
?
ALTER DATABASE ...SET ONLINE 命令
?
ALTER DATABASE ...SET OFFLINE 命令
?
ALTER DATABASE ...SET EMERGENCY 命令
?
DROP DATABASE 命令
?
数据库自动关闭时
?
当通过 CHECK OPTION 创建视图时，在其中创建该视图的数据库的计划缓存条目将被刷新。
?
运行 DBCC CHECKDB 时，将创建指定数据库的一个副本。作为 DBCC CHECKDB 执行的一部分，将执行针对该副本的一些查询，并缓存其计划。在 DBCC CHECKDB 执行结束时，将删除该副本以及针对其的查询的查询计划。
“引用特定数据库的计划缓存条目”这一概念需要解释一下。数据库 ID 是计划缓存的键之一。假设执行了下列命令序列。
use master
go

go
假定在计划缓存中缓存了 Q。与 Q 的计划相关联的数据库 ID 将成为“master”而非“db1”的数据库 ID。
当 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 的查询处理器中的统计（直方图）来跟踪数据更改。所以，与计划最优性相关的原因同统计密切相关。
开始详细介绍与计划最优性相关的原因之前，让我们列出不会发生与计划最优性相关的重新编译的情况。
?
当计划属于“常用计划”时。当查询优化器决定查询所引用的给定表及其上的索引时，将产生常用计划，只能有一个计划。显而易见，在这种情况下进行重新编译并没有什么用。当然，生成过常用计划的查询不一定总是生成这类计划。例如，可能在基础表上创建新的索引，从而就有多个访问路径供查询优化器使用。如第 7.1 节所提到的，所添加的这类索引将被删除，而与正确性相关的重新编译可能将常用计划替换为非常用计划。
?
当查询包含“KEEPFIXED PLAN”提示时，不会出于与计划最优性相关的原因重新编译该查询的计划。
?
当查询计划引用的所有表都为只读时，该计划将被重新编译。
?
这一点只适用于 SQL Server 2000。假定某个查询计划被编译（不是重新编译），而作为编译的一部分，查询处理器将决定更新表 T 上的统计 S。查询处理器试图在 T 上获取一个特殊的“统计锁”。如果其他进程正在更新 T 上的某个统计（不一定是 S！），查询处理器就无法在 T 上获得统计锁。在这种情况下，查询处理器不会更新 S。另外，上述查询计划不会再因为与计划最优性相关的原因而被重新编译。这就像通过“KEEPFIXED PLAN”提示提交查询一样。
?
这种情况与上面着重提到的一个情况相同，只是此处的查询计划被缓存了。换句话说，这种情况涉及重新编译，而前面的那个情况与之相反，仅涉及编译。对于这种重新编译情形，假设查询处理器试图在 T 上获得一个“统计锁”，但失败了。在这种情况下，查询处理器会跳过对统计 S 的更新；使用过时的统计 S；并同平常一样，通过其他重新编译步骤/检查继续进行操作。因而，借助可能较慢的查询执行计划可避免重新编译。
深入介绍查询编译
下方流程图简单明了地描述了 SQL Server 中的批处理编译和重新编译过程。主要的处理步骤如下所示（本文档后面将对各个步骤进行详细介绍）：
1.
SQL Server 开始编译一个查询。（正如前面提到的，批处理是一个编译和缓存单位，而批处理中的各个语句则是逐一进行编译。）
2.
可能有助于生成最佳查询计划的所有“引起关注的”统计都将从磁盘载入内存。
3.
如果任何统计过时了，那么将逐一对其进行更新。查询编译将等待更新的结束。对于这一步骤，SQL Server 2000 和 SQL Server 2005 间的一个重要不同之处在于：在 SQL Server 2005 中，可能会有选择地对统计进行异步更新。也就是说，统计更新线程不阻止查询编译线程。编译线程将用状态统计继续操作。
4.
生成查询计划。查询中所引用的所有表的重新编译阈值随查询计划一起被保存。
5.
这时，查询执行在理论上已经开始。现在测试查询计划以查找与正确性相关的原因。相关原因在第 7.1 中有所描述。
6.
如果就任何与正确性相关的原因而言，计划不正确，那么将开始进行重新编译。请注意，由于查询执行在理论上已经开始了，因此刚刚开始的编译即为重新编译。
7.
如果计划“正确”，那么各种重新编译阈值将与表基数或各种表修改计数器（SQL Server 2000 中的 rowmodctr 或 SQL Server 2005 中的 colmodctr）相比较。
8.
如果根据步骤 7 中进行的比较认定有任何统计过时了，那么将进行重新编译。
9.
如果步骤 7 中的所有比较都成功完成，那么将开始实际的查询执行。
与计划最优性相关的重新编译：整体情况
每个 SELECT、INSERT、UPDATE 和 DETELE 语句都访问一个或多个表。表内容因 INSERT、UPDATE 和 DELETE 等操作而发生变化。SQL Server 的查询处理器设计用于通过潜在地生成不同的查询计划（每个查询计划在生成时都是最佳的），来适应这种变化。使用表基数直接跟踪表内容，并使用表列上的统计（直方图）进行间接跟踪。
每个表都有一个与之相关联的重新编译阈值 (RT)。RT 是表中列数的一个函数。在查询编译期间，查询处理器将不载入或载入若干个在查询中引用的表上的统计。这些统计被称为引人关注的统计。对于查询中引用的每个表，已编译的查询计划包含：
?
重新编译阈值
?
列出查询编译期间载入的所有统计的列表对于每个统计，将保存计算表修改次数的计数器的快照值。在 SQL Server 2000 中，该计数器被称为 rowmodctr，而在 SQL Server 2005 中则称为 colmodctr。每个表列中都存在一个单独的 colmodctr（非永久性计算列除外）。
阈值交叉测试——执行其用于决定是否要重新编译查询计划——由下列公式定义：
| 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。请注意，“载入的”集同时包含：
?
用作操作符（显示在 P 中的）的基数评估器的统计
?
用作查询计划（在查询优化期间加以考虑但为了支持 P 而被抛弃）中的基数评估器的统计
换而言之，查询优化器出于某个原因或其他原因，将载入的统计认作是“令人关注的”。
回想一下，统计可以手动或自动创建或更新。还会因执行下列命令而导致统计更新：
?
CREATE INDEX ...WITH DROP EXISTING
?
sp_createstats 存储过程
?
sp_updatestats 存储过程
?
DBCC DBREINDEX（但不是 DBCC INDEXDEFRAG！）
重新编译阈值 (RT)
表的重新编译阈值可部分决定引用该表的查询的重新编译频率。RT 取决于表类型（永久或临时）以及编译查询计划时表中的行数（基数）。在批处理中引用的所有表的重新编译阈值都将随该批处理的查询计划一起保存。
RT 的计算方法如下所示。(n 表示编译查询计划时表的基数。）
?
永久表
?
如果 n，则 RT = 500。
?
如果 n>500，则 RT = 500 + 0.20 * n。
?
临时表
?
如果 n，则 RT = 6。
?
如果 6，则 RT = 500。
?
如果 n > 500，则 RT = 500 + 0.20 * n。
?
表变量
?
不存在 RT。所以，由于表变量的基数发生了变化而不会产生重新编译。
表修改计数器（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 值的方式。
语句
SQL Server 2000
SQL Server 2005
INSERT
rowmodctr += 1
所有 colmodctr += 1
DELETE
rowmodctr += 1
所有 colmodctr += 1
UPDATE
rowmodctr += 2 或 3。“2”的说明：1 表示删除 + 1 表示插入。
如果更新针对非键列：colmodctr += 1 针对所有已更新的列。
如果更新针对键列：colmodctr += 2 针对所有列。
大容量插入
不更改。
与 n INSERT 相同。所有 colmodctr += n。(n 是大容量插入的行数。）
表截断
不更改。
与 n DELETE 相同。所有 colmodctr += n。(n 是表的基数。）
两种特殊情况
与计划最优性相关的重新编译在下列两种特殊情况中的处理方式有所不同。
特殊情况 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 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 中，将在编译或重新编译下列批处理类型时嗅探参数值：
?
存储过程
?
通过 sp_executesql 提交的查询
?
预备的查询
在 SQL Server 2005 中，这一操作被扩展到使用 OPTION(RECOMPILE) 查询提示提交的查询上。对于这种查询（可以是 SELECT、INSERT、UPDATE 或 DELETE），将同时嗅探本地变量的参数值和当前值。在批处理中，所嗅探到的（参数和本地变量的）值后面紧跟着带有 OPTION(RECOMPILE) 提示的语句。尤其对于参数来说，不会嗅探批处理调用所附带的值。
识别重新编译
SQL Server 的事件探查器使得识别导致重新编译的批处理变得很简单。启动一个新的事件探查器跟踪，并在存储过程事件类别下，选择下列事件。（为了减少所生成的数据量，建议用户取消选定其他事件。）
?
SP:Starting
?
SP:StmtStarting
?
SP:Recompile
?
SP:Completed
此外，为了检测与统计更新相关的重新编译，可选择“对象”类别下的“自动统计”事件。
现在，启动 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
暂停事件探查器跟踪，并将看到下列事件。
EventClass
TextData
EventSubClass
SP:Starting
exec DemoProc1

SP:StmtStarting
-- DemoProc1 create table #t1 (a int, b int)

SP:StmtStarting
-- DemoProc1 select * from #t1

SP:Recompile

Deferred compile
SP:StmtStarting
-- DemoProc1 select * from #t1

SP:Completed
exec DemoProc1

SP:Starting
exec DemoProc1

SP:StmtStarting
-- DemoProc1 create table #t1 (a int, b int)

SP:StmtStarting
-- DemoProc1 select * from #t1

SP:Completed
exec DemoProc1

该事件序列指示“select * from #t1”为导致重新编译的语句。EventSubClass 列指出了进行重新编译的原因。在这种情况下，当 DemoProc1 在开始执行之前被编译，就可对“create table”语句进行变异。后续的“select”语句可能不会被编译，因为其引用了一个在初始编译时不存在的临时表 #t1。因此，DemoProc1 的已编译计划是不完整的。当 DemoProc1 开始执行时，随即创建了 #t1，然后就可以对“select”语句进行编译。由于 DemoProc1 已经执行，因此根据我们对重新编译的定义，这一编译可视为重新编译。此重新编译的真正原因是“延迟编译”。
请注意，有趣的一点是：当再次执行 DemoProc1 时，查询计划将不再是不完整的。重新编译已经将 DemoProc1 的一个完整的查询计划插入计划缓存中。所以，第二次执行过程中未发生任何重新编译。
SQL Server 2000 在这方面的情况也相同。
通过选择下列这组跟踪事件，也可以识别导致重新编译的批处理。
?
SP:Starting
?
SP:StmtCompleted
?
SP:Recompile
?
SP:Completed
如果在选择了这组新的跟踪事件后执行了刚才所述的例子，那么跟踪输出将如下所示。
EventClass
TextData
EventSubClass
SP:Starting
exec DemoProc1

SP:StmtCompleted
-- DemoProc1 create table #t1 (a int, b int)

SP:Recompile

Deferred compile
SP:StmtCompleted
-- DemoProc1 select * from #t1

SP:Completed
exec DemoProc1

SP:Starting
exec DemoProc1

SP:StmtCompleted
-- DemoProc1 create table #t1 (a int, b int)

SP:StmtCompleted
-- DemoProc1 select * from #t1

SP:Completed
exec DemoProc1

在此请注意，导致重新编译的语句将在 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'
上述查询的输出如下。（仅输出不带有阴影的列；带有阴影的列用于提供其他详细信息。）
SubclassName
SubclassValue
重新编译的详细原因
Schema changed
1
架构、绑定或权限在编译和执行间被更改。
Statistics changed
2
统计被更改。
Deferred compile
3
因 DNR（延迟名称解析）导致重新编译。在编译时未找到对象，对运行时延迟检测。
Set option change
4
批处理中的 Set 选项被更改。
Temp table changed
5
临时表架构、绑定或权限被更改。
Remote rowset changed
6
远程行集架构、绑定或权限被更改。
Query notification environment changed
8
（SQL Server 2005 新增！）
Partition view changed
9
SQL Server 2005 有时将[屏蔽]于数据的隐含谓词添加到一些索引视图中的查询的 WHERE 子句。如果基础数据发生变化，那么这些隐含谓词将无效，而相关联的缓存查询计划需要重新编译。 （SQL Server 2005 新增！）
在 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
在事件探查器跟踪输出中，可观测到下列事件。
EventClass
TextData
EventSubClass
SP:Starting
exec MixDDLDML

SP:StmtStarting
-- MixDDLDML create table tab1 (a int)???????--DDL

SP:StmtStarting
-- MixDDLDML select * from tab1???-- DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- MixDDLDML select * from tab1???-- DML

SP:StmtStarting
-- MixDDLDML create index nc_tab1idx1 on tab1(a)????-- DDL

SP:StmtStarting
-- MixDDLDML select * from tab1???-- DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- MixDDLDML select * from tab1???-- DML

SP:StmtStarting
-- MixDDLDML create table tab2 (a int)???????--DDL

SP:StmtStarting
-- MixDDLDML select * from tab2???-- DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- MixDDLDML select * from tab2???-- DML

SP:Completed
exec MixDDLDML

这里说明了 MixDDLDML 是如何编译的。
1.
在首次编译（不是重新编译）MixDDLDML 时，将为其生成一个主干计划。因为不存在表 tab1 和 tab2，所以无法生成三个“select”语句的计划。主干计划包含两个“create table”语句和一个“create index”语句的计划。
2.
当开始执行过程时，将创建表 tab1。由于不存在针对第一个“select * from tab1”的计划，因而将发生语句级重新编译。（在 SQL Server 2000 中，也将通过这一重新编译为第二个“select * from tabl”生成一个计划。）
3.
第二个“select * from tab1”将导致重新编译，因为还不存在相应查询的计划。在 SQL Server 2000 中，也会发生这类重新编译，但具体原因有所不同：由于在“tab1”上创建了非[屏蔽]索引，“tab1”的架构发生了变化。
4.
接着，创建了“tab2”。“select * from tab2”引发了重新编译，因为不存在相应查询的计划。
总之，在这个例子中，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
在事件探查器跟踪输出中，可观察到下列事件。
EventClass
TextData
EventSubClass
SP:Starting
exec DDLBeforeDML

SP:StmtStarting
-- DDLBeforeDML create table tab1 (a int)???????-- DDL

SP:StmtStarting
-- DDLBeforeDML create index nc_tab1idx1 on tab1(a)????-- DDL

SP:StmtStarting
-- DDLBeforeDML create table tab2 (a int)???????-- DDL

SP:StmtStarting
-- DDLBeforeDML select * from tab1???--DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- DDLBeforeDML select * from tab1???--DML

SP:StmtStarting
-- DDLBeforeDML select * from tab1???--DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- DDLBeforeDML select * from tab1???--DML

SP:StmtStarting
-- DDLBeforeDML select * from tab2???????????-- DML

SP:Recompile

Deferred compile
SP:StmtStarting
-- DDLBeforeDML select * from tab2???????????-- DML

SP:Completed
exec DDLBeforeDML

在 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
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
? ? begin
? ?? ? insert into #t1 values (@i, 2*@i - 50)

? ?? ? select a
? ?? ? from #t1??
? ?? ? where a??20 or a >=100) and (a
? ?? ? 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 )
此命令可删除特定数据库的计划缓存中的所有已缓存计划。不应在生产服务器上运行该命令，因为它反过来会影响正在运行的应用程序的性能。
事件探查器跟踪事件
下列事件探查器跟踪事件涉及观测和调试计划缓存、编译和重新编译行为。
?
‘Cursors:CursorRecompile’（SQL Server 2005 新增），用于观测与游标相关的批处理所导致的重新编译。
?
‘Objects:Auto Stats’，用于观测 SQL Server 的“自动统计”功能所导致的统计更新。
?
‘Performance:Show Plan All For Query Compile’（SQL Server 2005 新增），对于跟踪批处理编译很有用。不区分编译和重新编译。以文本格式生成 showplan 数据（类似使用“set showplan_all on”选项所生成的 showplan 数据）。
?
‘Performance:Show Plan XML For Query Compile’（SQL Server 2005 新增），对于跟踪批处理编译很有用。不区分编译和重新编译。以 XML 格式生成 showplan 数据（类似使用“set showplan_xml on”选项所生成的 showplan 数据）。
?
‘Stored Procedures:SP:Recompile’激发（发生重新编译时）。“Stored Procedures”类别中的其他事件也很有用——比如：SP:CacheInsert、SP:StmtStarting、SP:CacheHit、SP:Starting 等等。
性能计数器
在调试可能因过度编译和重新编译所导致的性能问题时，涉及下列性能计数器的值。
性能对象
计数器
SQLServer:缓冲管理器
缓存命中率、惰性写入/秒、过程高速缓存页数、总页数
SQLServer:高速缓存管理器
缓存命中率、高速缓存对象计数、高速缓存页数、高速缓存使用计数/秒
SQLServer:内存管理器
SQL 高速缓存内存 (KB)
SQLServer:SQL 统计
自动参数化尝试/秒、批请求/秒、自动参数化失败/秒、安全自动参数化/秒、SQL 编译/秒、SQL 重新编译/秒、不安全的自动参数化/秒

总结
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 不对其进行自动参数化的语句类型。
?
带有 IN 子句的查询不会被自动参数化。例如：
?
WHERE ProductID IN (707, 799, 905)
?
BULK INSERT 语句。
?
带有一个含变量的 SET 子句的 UPDATE 语句。例如：
UPDATE Sales.Customer
SET CustomerType = N'S'
WHERE CustomerType = @a
?
带有 UNION 的 SELECT 语句。
?
带有 INTO 子句的 SELECT 语句。
?
带有 FOR BROWSE 子句的 SELECT 或 UPDATE 语句。
?
带有使用 OPTION 子句指定的查询提示的语句
?
其 SELECT 列表包含 DISTINCT 的 SELECT 语句。
?
带有 TOP 子句的语句。
?
WAITFOR 语句。
?
带有 FROM 子句的 DELETE 或 UPDATE 语句。
?
当 FROM 子句含有下列之一时：
?
多个表
?
TABLESAMPLE 子句
?
表值函数或表值变量
?
全文表
?
OPENROWSET
?
XMLUNNEST
?
OPENXML
?
OPENQUERY
?
IROWSET
?
OPENDATASOURCE
?
表提示或索引提示
?
当 SELECT 查询包含一个子查询时
?
当 SELECT 语句包含 GROUP BY、HAVING 或 COMPUTE BY 时
?
用 WHERE 子句中的 OR 加入的表达式。
?
expr??non-null-constant 形式的比较谓词。
?
全文谓词。
?
当 INSERT、UPDATE 或 DELETE 中的目标表是一个表值函数时。
?
通过 EXEC 字符串提交的语句。
?
通过 sp_executesql、sp_prepare 和 sp_prepexec 提交的语句，不带有在 TF 447 下自动参数化的参数。
?
当要求查询通知时。
?
当查询包含通用表表达式列表时。
?
当查询包含 FOR UPDATE 子句时。
?
当 UPDATE 包含 ORDER BY 子句时。
?
当查询包含 GROUPING 子句时。
?
形式如下的 INSERT 语句：INSERT INTO T DEFAULT VALUES。
?
INSERT ...EXEC 语句。
?
当查询包含两个常量的对比时。例如：
WHERE 20 > 5
?
通过自动参数化，可创建超过 1000 个参数。