内存优化表出现Table Spool的原因及抑制方法

算力阿峰

2026-6-21

SQL查询优化：解决Table Spool高耗时问题

我正在优化一条SQL查询以提升执行速度，当前执行计划显示Table Spool占用了约一半的执行时间，整体执行时长为10分11秒，其他运算符仅需数秒。

待优化查询语句

select distinct a1.* from
(
    select distinct
    al.AL_ParentCode as Code,
    c.Code as ContrCode
    from #ALevels as al
    inner join ActiveContrForAddressees as c
    on al.AL_Val = c.MarketType
    and al.AL_Type = 1
    and al.AL_ParentCode = '91'
    inner join Dealer2Dealer as d2d
    on
    (
        d2d.DealerID = c.DealerID
        or d2d.DealerID = c.MerchandiserCode
        or d2d.DealerID = c.StrikerCode
    )
) as a1
inner join
(
    select distinct
    al.AL_ParentCode as Code,
    c.Code as ContrCode
    from #ALevels as al
    inner join ActiveContrForAddressees as c
    on al.AL_Type = 6
    and al.AL_ParentCode = '91'
    inner join Dealer2Dealer as d2d
    on al.AL_Val = d2d.ParentDealerID
    and
    (
        c.DealerID = d2d.DealerID
        or c.MerchandiserCode = d2d.DealerID
        or c.StrikerCode = d2d.DealerID
    )
) as a2
on a1.ContrCode = a2.ContrCode
inner join
(
    select distinct
    al.AL_ParentCode as Code,
    c.Code as ContrCode
    from #ALevels as al
    inner join ActiveContrForAddressees as c
    on al.AL_Val = c.ObjType
    and al.AL_Type = 9
    and al.AL_ParentCode = '91'
    inner join Dealer2Dealer as d2d
    on
    (
        d2d.DealerID = c.DealerID
        or d2d.DealerID = c.MerchandiserCode
        or d2d.DealerID = c.StrikerCode
    )
) as a3
on a2.ContrCode = a3.ContrCode
inner join
(
    select distinct
    al.AL_ParentCode as Code,
    c.Code as ContrCode
    from #ALevels as al
    inner join Dealer2Dealer as d2d
    on al.AL_Val = d2d.ParentDealerType
    and al.AL_Type = 12
    inner join ActiveContrForAddressees as c
    on
    (
        d2d.DealerID = c.DealerID
        or d2d.DealerID = c.MerchandiserCode
        or d2d.DealerID = c.StrikerCode
    )
    and al.AL_ParentCode = '91'
) as a4
on a3.ContrCode = a4.ContrCode

表结构与现有索引

临时表#ALevels

预先填充数据后创建非聚集索引：

create nonclustered index idx_ALevels
on #ALevels (
    AL_ParentCode, AL_Type, AL_Val
) include (AL_ParentType)

内存优化表ActiveContrForAddressees

表定义：

create table ActiveContrForAddressees (
    Code nvarchar(25) not null primary key nonclustered hash with (bucket_count = 150000),
    DealerID nchar(10) null,
    MerchandiserCode nvarchar(20) null,
    StrikerCode nvarchar(255) null,
    MarketType nvarchar(20) null,
    CustomerType nvarchar(20) null,
    CustSubClass nvarchar(20) null,
    CustClass nvarchar(20) null,
    Seasonal nvarchar(20) null,
    ObjType nvarchar(20) null,
    RegionCode nvarchar(60) null,
    InternalCategory int null,
    Chain nvarchar(100) null,
    index idx_ACFA nonclustered (
        Code, DealerID, MerchandiserCode, StrikerCode,
        MarketType, CustomerType, CustSubClass, CustClass,
        Seasonal, ObjType, RegionCode, InternalCategory, Chain
    )
) with (
    memory_optimized = on
)

该表通过RefreshActiveContr存储过程刷新，源表数据变化时执行删除插入并更新统计信息。

内存优化表Dealer2Dealer

表定义：

create table Dealer2Dealer (
    ParentDealerID nvarchar(255) not null,
    ParentDealerType nvarchar(255) null,
    DealerID nvarchar(255) not null,
    primary key nonclustered /*hash*/ (DealerID, ParentDealerID)
    --with (bucket_count = 1024)
) with (
    memory_optimized = on
)

该表通过类似过程刷新，删除数据后重新插入并更新统计信息。

已尝试的优化动作

调整查询中a2部分的连接顺序，执行时间无明显变化
尝试使用内存优化表变量，但因查询通过字符串拼接后exec执行，出现表变量作用域问题（提示"Must declare the table variable @temp"）；且内存优化表最多支持8个索引，动态查询可能用到不同列，无法创建执行计划建议的索引

优化建议

1. 消除冗余的`DISTINCT`

原查询中每个子查询都使用了DISTINCT，最终外层又加了一次DISTINCT，这会强制多次数据去重，可能触发不必要的Table Spool。可以先验证子查询是否真的需要去重：

检查每个子查询的连接逻辑，确认是否会产生重复行，若不会则移除子查询内的DISTINCT，仅保留外层的DISTINCT（或根据实际情况判断是否需要）

2. 拆分OR连接条件

所有子查询中都存在OR连接条件（如d2d.DealerID = c.DealerID or d2d.DealerID = c.MerchandiserCode or d2d.DealerID = c.StrikerCode），OR条件会导致SQL Server无法有效利用索引，容易触发Table Spool来处理半连接/反连接逻辑。可以将OR拆分为UNION ALL：
以a1子查询为例，改写为：

select al.AL_ParentCode as Code, c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
    on al.AL_Val = c.MarketType
    and al.AL_Type = 1
    and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
    on d2d.DealerID = c.DealerID
union all
select al.AL_ParentCode as Code, c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
    on al.AL_Val = c.MarketType
    and al.AL_Type = 1
    and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
    on d2d.DealerID = c.MerchandiserCode
union all
select al.AL_ParentCode as Code, c.Code as ContrCode
from #ALevels as al
inner join ActiveContrForAddressees as c
    on al.AL_Val = c.MarketType
    and al.AL_Type = 1
    and al.AL_ParentCode = '91'
inner join Dealer2Dealer as d2d
    on d2d.DealerID = c.StrikerCode

然后对整个结果集去重（如果需要），这样可以让SQL Server为每个分支使用更高效的索引扫描/查找，避免Table Spool。

3. 优化内存优化表的索引

针对ActiveContrForAddressees

当前的非聚集索引idx_ACFA包含了几乎所有列，但查询中主要用到的是MarketType、ObjType、DealerID、MerchandiserCode、StrikerCode这些列。可以创建针对性的非聚集索引：

针对a1子查询：创建索引(MarketType, DealerID, MerchandiserCode, StrikerCode) include Code
针对a3子查询：创建索引(ObjType, DealerID, MerchandiserCode, StrikerCode) include Code
注意内存优化表最多支持8个索引，需根据实际查询频率权衡创建。

针对Dealer2Dealer

当前主键是(DealerID, ParentDealerID)，但a2子查询中用到al.AL_Val = d2d.ParentDealerID，可以创建非聚集索引(ParentDealerID) include DealerID，加速该连接逻辑；同时a4子查询用到al.AL_Val = d2d.ParentDealerType，若该列查询频繁，可创建索引(ParentDealerType) include DealerID。

4. 解决动态SQL的表变量作用域问题

若必须使用动态SQL，可将内存优化表变量的声明放在动态SQL内部，或者使用临时表替代（临时表的作用域比表变量更广，可跨exec执行）。例如：

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
DECLARE @temp TABLE (
    Code NVARCHAR(25),
    ContrCode NVARCHAR(25)
) WITH (MEMORY_OPTIMIZED = ON)
-- 后续查询逻辑
'
EXEC sp_executesql @sql

5. 强制禁用Table Spool（谨慎使用）

可以尝试使用查询提示OPTION (QUERYTRACEON 8690)来禁用Table Spool，但需注意这是全局跟踪标志，可能影响其他查询，建议仅在测试环境验证后使用，或结合OPTION (RECOMPILE)一起使用，仅对当前查询生效：

-- 在原查询末尾添加
OPTION (QUERYTRACEON 8690, RECOMPILE)

注意：需要较高权限才能使用跟踪标志，且需充分测试性能变化。

内容的提问来源于stack exchange，提问作者user2177283

内存优化表出现Table Spool的原因及抑制方法

待优化查询语句 #

表结构与现有索引 #

临时表#ALevels #

内存优化表ActiveContrForAddressees #

内存优化表Dealer2Dealer #

已尝试的优化动作 #

优化建议 #

1. 消除冗余的DISTINCT #

2. 拆分OR连接条件 #

3. 优化内存优化表的索引 #