为何SQL Server查询优化器为相似查询选择完全不同的执行计划？

这是一个非常典型的SQL Server查询优化器启发式搜索逻辑导致的执行计划差异问题，我来帮你拆解原因并给出不修改索引/表结构的优化方案：

测试环境回顾 #

首先先确认下我们的测试表创建逻辑（方便其他读者复现场景）：

-- 生成含4个属性的100万行测试表
WITH x AS (
 SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
), t1 AS (
 SELECT ones.n + 10 * tens.n + 100 * hundreds.n + 1000 * thousands.n + 10000 * tenthousands.n + 100000 * hundredthousands.n as id
 FROM x ones, x tens, x hundreds, x thousands, x tenthousands, x hundredthousands
)
SELECT id,
 id % 50 predicate_col,
 row_number() over (partition by id % 50 order by id) join_col,
 LEFT('Value ' + CAST(CHECKSUM(NEWID()) AS VARCHAR) + ' ' + REPLICATE('*', 1000), 1000) as padding
INTO TestTable
FROM t1
GO
-- 将id设为主键（同时创建聚集索引）
ALTER TABLE TestTable ALTER COLUMN id int not null
GO
ALTER TABLE TestTable ADD CONSTRAINT pk_TestTable_id PRIMARY KEY (id)
-- 创建非聚集索引
CREATE NONCLUSTERED INDEX ix_TestTable_predicate_col_join_col ON TestTable (predicate_col, join_col)
GO

查询与执行计划差异 #

我们有两个仅谓词不同的查询，却得到了完全不同的执行计划：

Q1（范围谓词：b.predicate_col <= 0） #

select b.id, b.predicate_col, b.join_col, b.padding
from TestTable b
join TestTable a on b.join_col = a.id
where a.predicate_col = 1 and b.predicate_col <= 0
option (maxdop 1)

Q2（等值谓词：b.predicate_col = 0） #

select b.id, b.predicate_col, b.join_col, b.padding
from TestTable b
join TestTable a on b.join_col = a.id
where a.predicate_col = 1 and b.predicate_col = 0
option (maxdop 1)

两者的执行计划对比如下：

• Q1执行计划：先结合键查找与非聚集索引查找，再进行最终连接，性能较差
• Q2执行计划：先连接非聚集索引，再执行键查找获取完整数据，性能更优

为什么会出现这种差异？ #

你提到行数估算完全准确，并且通过跟踪标志发现优化器找到第一个可行计划后就停止搜索——这正是核心原因：

SQL Server的查询优化器是成本驱动的启发式优化器，它不会遍历所有可能的执行计划（那样编译时间会不可接受），而是在搜索过程中一旦找到一个“成本足够低”的可行计划，就会停止搜索，不再探索其他潜在的更优计划。

对于Q2的等值谓词b.predicate_col = 0：
这个条件可以直接匹配非聚集索引ix_TestTable_predicate_col_join_col的首列，优化器很容易快速识别出“先筛选b表目标行→再和a表连接→最后键查找取全量数据”这条路径，并且计算出的成本非常低，于是直接选中这个最优计划。

对于Q1的范围谓词b.predicate_col <= 0：
虽然行数估算准确，但优化器的启发式规则会让它在搜索早期先尝试另一条路径：先处理a表的过滤（a.predicate_col = 1），再将a表结果与b表的非聚集索引连接，最后对连接结果做键查找。这条路径的成本计算虽然比Q2的计划高，但优化器认为它已经达到了“可接受”的阈值，于是停止继续搜索其他更优的路径（比如Q2采用的先筛选b表的路径）。

简单说：优化器的搜索终止策略，导致它在Q1场景下没有探索到更优的计划就停在了第一个可行但成本更高的选项上。

不修改物理结构的优化方案 #

既然不能改动索引或表结构，我们可以通过查询提示或查询重写来引导优化器选择更优的执行计划：

方法1：使用FORCE ORDER查询提示 #

强制优化器按照查询中表的书写顺序处理连接，也就是先处理b表的过滤逻辑，再与a表连接：

select b.id, b.predicate_col, b.join_col, b.padding
from TestTable b
join TestTable a on b.join_col = a.id
where a.predicate_col = 1 and b.predicate_col <= 0
option (maxdop 1, FORCE ORDER)

方法2：显式重写查询，引导优先筛选b表 #

用CTE或子查询先明确筛选出b表的目标行，让优化器优先处理这部分逻辑：

with filtered_b as (
    select id, predicate_col, join_col, padding
    from TestTable
    where predicate_col <= 0
)
select fb.id, fb.predicate_col, fb.join_col, fb.padding
from filtered_b fb
join TestTable a on fb.join_col = a.id
where a.predicate_col = 1
option (maxdop 1)

方法3：使用跟踪标志8675（谨慎使用） #

这个未公开的跟踪标志会让优化器进行更全面的计划搜索（接近全穷举），但注意：

• 仅建议用于测试，生产环境长期使用可能增加编译时间
• 需要具备服务器级权限才能使用

select b.id, b.predicate_col, b.join_col, b.padding
from TestTable b
join TestTable a on b.join_col = a.id
where a.predicate_col = 1 and b.predicate_col <= 0
option (maxdop 1, QUERYTRACEON 8675)

内容的提问来源于stack exchange，提问作者Radim Bačva