SQL Server Table Spool优化

本系列属于 SQL Server性能优化案例分享 专题

    在执行计划中出现的Spool操作符，往往都具有明显的性能问题，也意味着数据库的设计、编码等可能存在问题，所以本文专门介绍一下这个操作符。

   

Spool介绍

    Spool是内存或者磁盘上的缓存（cache）或临时表。SQL Server用这个结构来提升在执行过程中需要多次执行的复杂的子表达式的性能。注意几个次：一次运行中多次执行、复杂的子表达式。其目的是为了提升性能。

    比如下面的一个演示语句，使用TempDB来创建一个测试表：

USE TempDB
GO
CREATE TABLE dbo.Orders (
	OrderID INT NOT NULL
	,CustomerId INT NOT NULL
	,Total MONEY NOT NULL
	,CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderID)
	)
GO
;WITH N1 (C)AS (
	SELECT 0	
	UNION ALL	
	SELECT 0
	) -- 2 行
,N2 (C)AS (
	SELECT 0
	FROM N1 AS T1 CROSS JOIN N1 AS T2
	) -- 4 行
,N3 (C) AS (
	SELECT 0
	FROM N2 AS T1
	CROSS JOIN N2 AS T2
	) -- 16 行
,N4 (C)AS (
	SELECT 0
	FROM N3 AS T1
	CROSS JOIN N3 AS T2
	) -- 256 行
,Nums (Num)
AS (
	SELECT row_number() OVER ( ORDER BY ( SELECT NULL ) )
	FROM N4
	)
INSERT INTO dbo.Orders (OrderId ,CustomerId ,Total )
SELECT Num,Num % 10 + 1 ,Num
FROM Nums;

    然后打开实际执行并运行下面查询，返回订单信息及每个客户的总销售额。

USE TempDB
GO
select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders

    可以看到如下结果：

    在图中可以看到，SQL Server对Order 表进行了扫描，并且基于CustomerID进行排序。然后使用Table Spool对结果进行缓存。使得后续操作（比如计算总数）中可以直接访问这些缓存数据，同时由于缓存值已经排序，可以避免二次排序。

    如果检查执行计划中的3个Table Spool，如下图，可以看到输出列表是一样的。说明一个事情，虽然在执行计划中出现了3次Table Spool，但是它们实际上是相同的Spool/cache，SQL Server只是创建一次并一直使用而已。下图中只有“节点ID=1”的那个是第一个创建的Table Spool，也就是执行计划中最上方的那个，而右下角那两个均有“主节点ID=1”表明是引用节点ID=1的那个Table Spool。

    从技术上细分，spool操作符有两种：Eager Spool（本文第一张图）和Lazy Spool（上面演示案例），它们的区别只是在于填充数据的方式：

• Eager Spool：在spool被调用的同时检索所有数据。
• Lazy Spool：按需检索数据。

    其他不常见的Spool 操作符还有：Row Count Spool、Non-Clustered Index Spool。

题外话：SQL Server还使用Spool来实现“Halloween Protection”，简单地说，就是准备要修改的数据的位置变动了。这种低概率事件在作者工作过程中也确实出现过，一般使用事务控制来避免。这个是一个计算机领域的问题，并非SQL Server独有，由IBM工程师首先发现，大部分成熟的RDBMS都已经很大程度地避免。至于SQL Server相关内容，可以详见MSDN博客：Halloween Protection

下面来简要分析一下上面的执行计划，为了描述方便，这里按照执行计划顺序对操作符进行标号：

First Step：

Step 1：从Orders表中，通过聚集索引（因为表只有一个聚集索引）扫描，读取所有的OrderId, CustomerID, Total信息。

Step 2：由于需要根据CustomeID进行分组并使用窗口函数计算（sum() over()），而且CustomerID并没有在聚集索引键上，所以需要进行额外排序。

Step 3：Segment操作，把数据拆成多组，因为窗口函数需要对CustomerID进行分组。

Step 4：Table Spool，并且是Lazy Spool，这个操作在TempDB上创建一个临时表，并把segment操作基于不同的组返回的数据存储到这个临时表中。

Second Step：

Step 1：复用前面生成的Table Spool。

Step 2：对Table Spool的数据，按分组使用流聚合操作符汇总数据，本例中的Sum，按分组计算Total的值，并返回“一行”作为输出。

Step 3：对上一步的结果集进行计算，如果对XML执行计划查询“Compute Scalar”关键字，可以看到它在进行一个case when操作：<ScalarOperator ScalarString="CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END">

Step 4：计算标量操作符得到的数据，使用嵌套循环操作符，与Third Step中的Table Spool再次匹配，返回符合条件的结果集。

Step 5：把最终的两个结果集再次使用嵌套循环操作，一行一行地匹配。

 Spool 影响

    Spool的初衷是好的，但是它通常又涉及了worktable，使用“set statistics io on/off”命令包住需要运行的语句就可以发现。

    Worktable在OLTP系统中，意味着使用了低效的I/O操作，比如TempDB（简单来说就是磁盘读写）来运算。

    另外，对于Eager Spool，第一次从结果中查询到所需的数据并放入TempDB之后，后续使用直接从Spool中获取而不通过表上原有的索引，如果Spool数据集过大，是非常低效的。

    还有，由于spool实际上是复制一份数据的副本存储在TempDB中，所以空间问题可能会加重。

优化Spool

    从上面一系列例子中，我们得知首先由于语句进行复杂运算，同时需要多次调用，引出了Spool以便优化，为了优化上面的语句（首先建议先记录下一些信息，比如Set Statistics IO 的信息），我们从执行计划着手，首先找开销最大的部分，即“sort”操作符。从说明上我们可以看到这个操作符是为了对CustomerID排序。从表定义可知这个表只有一个聚集索引。那么下面我们来对CustomerID加一个非聚集索引，由于SELECT中使用到了Total，所以把total加入Include列。

CREATE INDEX IX_Orders_CustomerID on Orders(CustomerID) INCLUDE(total)

    再次执行语句，这次貌似不错，排序操作符不见了，但是依旧还存在spool，而且从IO统计来看，数值一样。感觉还是不够完美。

    但是在多次尝试之后，我发现很多方式反而不如这个高效，因此我用上面创建测试环境的语句再创建了一个orders1表，也 就是说我复制了一份环境，然后做对比：

USE TempDB
GO

set statistics io on
select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders


select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders1
set statistics io off

    打开执行计划，然后一次性执行上面语句，这种方式可以用于对比两个语句的开销情况，开销百分比小的，意味着在大部分情况下更佳。下面是本机的执行情况：

    可以看到，orders表也就是按照上面方式优化了的表，跟orders1表（原始环境）对比，分别是27%和73%，而I/O数值来看是一模一样的：

    那么有理由相信，即使存在spool操作，也并不一定是低效的。因为我通过改写语句成下面这种方式来去除了spool，但是放在一起执行时发现不如上面没改写仅调整索引的语句：

set statistics io on
select OrderId, CustomerID, Total,
(select Sum(Total)  from orders b where a.CustomerId=b.CustomerId)--over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders a

set statistics io off

    为了避免读者认为orders是调整过索引的写法，我再次使用orders1表，三个语句放在一次执行，注意这里orders1是原始环境：

USE TempDB
GO

set statistics io on
select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders


select OrderId, CustomerID, Total
,Sum(Total) over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders1

select OrderId, CustomerID, Total,
(select Sum(Total)  from orders1 b where a.CustomerId=b.CustomerId)--over(partition by CustomerID) as [Total Customer Sales] 
from dbo.Orders1 a
set statistics io off

得到的结果：

总结

    在这篇文章中，我得到几个结论：

1. 不能仅靠单一“值”来判断，在I/O统计数值相等的情况下，可以考虑借用其他手段来评估优化的方案。

2. 我一直跟很多人强调，改索引之前，先检查语句是否可以改写。上面我已经尝试了改写，但是目前就个人水平而言，还没有发现单纯通过改写就可以提高这个例子的方法。所以后面才考虑修改索引。

3. 我以前看书的时候看过很多案例，窗口函数在很多环境下，确实极大地提高效率，所以建议SQL Server从业人员也可以优先考虑一下窗口函数。

4. 大胆假设小心求证。

另外，对于Spool的优化，通常确实是通过优化索引来减缓或去除。不过凡是无绝对，多看看具体问题最重要。