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

核心提示：下面，考虑以下 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)godeclare @i

下面，考虑以下 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 )

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

事件探查器跟踪事件

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

•	‘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 个参数。