SQL Server 2005 中的批编译、重新编译和计划缓存问题（3）

核心提示：两种特殊情况与计划最优性相关的重新编译在下列两种特殊情况中的处理方式有所不同，特殊情况 1：在空表或索引视图上创建的统计SQL Server 2005 处理下列情况的方式不同于 SQL Server 2000，SQL Server 2005 中的批编译、重新编译和计划缓存问题（3），用户创建了一个空表 T，然后又在 T

两种特殊情况

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

特殊情况 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 中，将在编译或重新编译下列批处理类型时嗅探参数值：

•	存储过程
•	通过 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 的重新编译工作量没有增加，但是重新编译的次数却增多了。