SQL Server 2012升级至2016后窗口函数性能劣化问题咨询

问题背景 #

将SQL Server 2012升级至2016后，使用ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)、LAG(...) OVER (PARTITION BY ... ORDER BY ...)这类窗口函数的查询性能极差。通过MIN/MAX函数+多表连接的方式替换窗口函数后，性能得到显著提升。

性能对比示例 #

性能不佳的窗口函数写法

FROM 
    table1 AS t1
LEFT JOIN 
    (SELECT
         t2.ID,
         ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t2.ROW_ID DESC) AS RowNum
     FROM 
         table2 AS t2
     WHERE 
         t2.flag = -1) AS t2Rollup ON t2Rollup.RowNum = 1 
                                   AND t2Rollup.ID = t1.ID
WHERE 
    t1.ID LIKE '%042'

优化后的MIN/MAX+连接写法

FROM 
    table1 AS t1
LEFT JOIN 
    (SELECT
         t2.ID,
         MAX(t2.ROW_ID) AS MaxRowID
     FROM 
         table2 AS t2
     WHERE 
         t2.flag = -1
     GROUP BY 
         t2.ID) AS MaxRow ON MaxRow.ID = t1.ID
LEFT JOIN 
    table2 AS t2Rollup ON t2Rollup.ID = MaxRow.ID
                       AND t2Rollup.ROW_ID = MaxRow.MaxRowID
WHERE 
    t1.ID LIKE '%042'

执行计划显示，窗口函数写法会对远超最终查询所需的行进行排序（例如对t1.ID不以042结尾的行也执行排序），导致大量时间消耗在排序操作上。

额外背景：升级后曾开启Legacy Cardinality Estimation，后续关闭该选项并将数据库兼容性级别改为SQL Server 2012（110），此后多个存储过程出现窗口函数性能问题。

可能的数据库/服务器配置排查点 #

1. 数据库范围的基数估计配置 #

即使将兼容性级别改回110，数据库级的基数估计设置可能残留冲突。执行以下查询检查：

SELECT name, value, is_value_default
FROM sys.database_scoped_configurations 
WHERE name IN ('LEGACY_CARDINALITY_ESTIMATION', 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110');

• 若LEGACY_CARDINALITY_ESTIMATION未设为默认值，可能导致优化器行为不一致；
• QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110若被禁用，会覆盖兼容性级别的设置。

2. 统计信息状态 #

SQL Server 2016的统计信息采样率、更新机制与2012存在差异，即使回退兼容性级别，旧的统计信息可能无法被优化器正确识别。对涉及的表执行全量统计更新：

UPDATE STATISTICS table1 WITH FULLSCAN;
UPDATE STATISTICS table2 WITH FULLSCAN;

3. 数据库级别的会话设置 #

部分会话级设置（如ANSI_WARNINGS、ARITHABORT）会影响窗口函数的执行计划生成。检查数据库级默认设置：

SELECT name, is_ansi_nulls_on, is_ansi_padding_on, is_ansi_warnings_on, is_arithabort_on
FROM sys.databases WHERE name = 'YourDatabaseName';

确保这些设置与原SQL Server 2012实例一致。

4. 服务器级跟踪标志 #

某些跟踪标志会强制改变优化器行为，即使兼容性级别已回退。执行以下查询检查启用的跟踪标志：

DBCC TRACESTATUS;

重点关注：

• TF 2312：启用新基数估计器（优先级高于兼容性级别）；
• TF 4199：启用优化器补丁集合，可能改变窗口函数的执行逻辑；
• TF 9481：强制使用旧基数估计器（若之前开启后未彻底关闭）。

5. 并行度（MAXDOP）设置 #

SQL Server 2016默认MAXDOP设置与2012不同，窗口函数的排序操作依赖并行执行提升性能。检查服务器级和数据库级MAXDOP：

-- 服务器级
SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism';

-- 数据库级
SELECT name, value FROM sys.database_scoped_configurations WHERE name = 'MAXDOP';

确保MAXDOP设置与原2012实例一致，避免排序操作无法并行执行。

验证步骤 #

1. 临时在窗口函数查询中添加提示，强制使用旧基数估计器，看性能是否恢复：

-- 在原查询末尾添加
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

2. 若上述提示有效，说明基数估计相关配置存在问题，优先调整LEGACY_CARDINALITY_ESTIMATION的数据库级设置。

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