探秘重编译（Recompilations）（2/2）

2017-11-14 1061

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS SQL Server，基础系列 2核4GB

RDS SQL Server Serverless，2-4RCU 50GB 3个月

简介：

表变量（Table Variables）

表变量总局限于提交到SQL Server的批处理语句范围。当你在批处理语句范围外引用表变量时，SQL Server就会返回你一条错误信息。这是和临时表相比第1个重大区别。下列代码向你展示了如何创建和使用表变量——只在简单存储过程的上下文里。

 1 CREATE PROCEDURE DemonstrateTableVariablesNoRecompiles
 2 AS
 3 BEGIN
 4     DECLARE @tempTable TABLE
 5     (
 6         ID INT IDENTITY(1, 1) PRIMARY KEY,
 7         FirstName CHAR(4000),
 8         LastName CHAR(4000)
 9     )
10     
11     INSERT INTO @TempTable (FirstName, LastName)
12     SELECT TOP 1000 name, name FROM master.dbo.syscolumns
13     
14     SELECT * FROM @TempTable
15 END
16 GO

表变量的好处是它们不会引起任何重编译。当你执行这个存储过程并用SQL Server Profiler跟踪时，不会发现重编译事件。

1 EXEC dbo.DemonstrateTableVariablesNoRecompiles

为什么使用表变量就可以这样呢？首先表变量就是个变量——名副其实。当你定义你的表变量时，意味着你不会改变你的数据库架构。因此基于数据酷架构改变的重编译就可以避免。另外表变量是没有统计信息的。因此没有统计信息需要维护，第2个引起重编译原因也就消失了。

首先，这2样听起来都很棒，但当我们进一步分析时，就会发现它的重大缺点。我们来看看。表变量近乎就是个变量。在临时表里，表变量还是持续的。是的，你没看错：当你使用表变量时，会涉及到临时表里的物理I/O操作。这个可以用动态管理视图sys.dm_db_session_space_usage来验证，它是在会话级别跟踪临时表的使用率。我们来看下面的代码（请【新建查询】执行下列代码）：

 1 -- Create a table variable
 2 DECLARE @tempTable TABLE
 3 (
 4     ID INT IDENTITY(1, 1) PRIMARY KEY,
 5     FirstName CHAR(4000),
 6     LastName CHAR(4000)
 7 )
 8 
 9 -- Insert 4 records into the table variable
10 INSERT INTO @tempTable (FirstName, LastName) VALUES
11 (
12     'Woody',
13     'Tu'
14 ),
15 (
16     'Woody',
17     'Tu'
18 ),
19 (
20     'Woody',
21     'Tu'
22 ),
23 (
24     'Woody',
25     'Tu'
26 )
27 
28 -- Retrieve the data from the table variable.
29 -- The execution plan estimates 1 row.
30 SELECT * FROM @tempTable
31 GO
32 
33 -- Review the space used in TempDb.
34 -- Our table variable currently needs 5 pages in TempDb.
35 -- The 5 needed pages from the table variable are already marked for deallocation (column "user_objects_dealloc_page_count")
36 SELECT * FROM sys.dm_db_session_space_usage
37 WHERE session_id = @@SPID
38 GO

从图中可以看出，这个表变量在临时表里需要分配5个页。因为这个表变量已经超过范围，这5个页面也已被标记为重分配（deallocation）。你要知道这个副作用。

表变量也没有统计信息。因此这里没有重编译发生。但是作为一个副作用，查询优化器始终认为估计行数为1.这个会非常，非常糟糕。如果你从表变量连接你数据库里另外一张表。在那个情况下，查选优化器在执行计划里引入嵌套循环连接（Nested Loop Join）运算符，引用的表变量作为外表，因为估计行数是1。如果事实上返回行是10000或更多的话，整个执行计划就谈不上最优。我们来看下面的例子（点击工具栏的显示包含实际的执行计划）：

 1 CREATE PROCEDURE BadPerformingQuery
 2 AS
 3 BEGIN
 4     DECLARE @tempTable TABLE
 5     (
 6         ID INT IDENTITY(1, 1) PRIMARY KEY,
 7         FirstName CHAR(4000),
 8         LastName CHAR(4000)
 9     )
10     
11     INSERT INTO @TempTable (FirstName, LastName)
12     SELECT TOP 20000 name, name FROM master.dbo.syscolumns
13     
14     -- The physical Join Operator will be a Nested Loop,
15     -- because Nested Loop is optimized for 1 row in the outer loop.
16     SELECT * FROM AdventureWorks2008R2.Person.Person p
17     INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID
18 END
19 GO

我们仔细看下聚集索引扫描（ Clustered Index Scan）运算符的属性信息，你会看到这里的估计行数是1，而实际行数却是12622。

你可以通过自SQL Server 2005起引入的语句级别的重编译（Statement-Level Recompilation）来修正这个基数预估错误。

 1 -- Use a statement-level recompilation to fix the problem with the 
 2 -- cardinality estimation.
 3 ALTER PROCEDURE BadPerformingQuery
 4 AS
 5 BEGIN
 6     DECLARE @tempTable TABLE
 7     (
 8         ID INT IDENTITY(1, 1) PRIMARY KEY,
 9         FirstName CHAR(4000),
10         LastName CHAR(4000)
11     )
12     
13     INSERT INTO @TempTable (FirstName, LastName)
14     SELECT TOP 20000 name, name FROM master.dbo.syscolumns
15     
16     -- The physical Join Operator will be a Nested Loop,
17     -- because Nested Loop is optimized for 1 row in the outer loop.
18     SELECT * FROM AdventureWorks2008R2.Person.Person p
19     INNER JOIN @tempTable t ON t.ID = p.BusinessEntityID
20     OPTION (RECOMPILE)
21 END
22 GO