问题背景 #

把原本适配Oracle的遗留SQL（后转为MS SQL版本）从SQL Server 2012迁移到2016后，碰到了致命的性能问题：

• 测试环境里，查询速度比2012慢至少30倍；
• 生产环境跑了24小时以上还没完成；
• 只输入2条记录，居然生成了20万行结果，数据膨胀率高得离谱；
• 两个版本的执行计划不仅复杂，还完全不一样。

目前已经找到临时解决办法：把2016服务器上目标数据库的兼容性级别改成110（对应SQL Server 2012的兼容模式），问题就消失了。

环境信息 #

SELECT @@VERSION
-- 输出结果：
MS SQL Server 2016 (SP1-CU3) (KB4019916) - 13.0.4435.0 (X64) 
Apr 27 2017 17:36:12 
Copyright Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

兼容性级别切换语句：

ALTER DATABASE ADRO SET COMPATIBILITY_LEVEL =110 -- 切换到SQL Server 2012兼容模式
-- ALTER DATABASE ADRO SET COMPATIBILITY_LEVEL =130 -- 恢复到SQL Server 2016原生模式

示例查询语句 #

SELECT 
    Min(EVENT_INSR.PK_ID) AS PK_ID,
    EVENT_INSR.CUST_ID AS CUST_ID,
    EVENT_INSR.PROV_ID AS PROV_ID,
    EVENT_INSR.EVNT_DATE AS SERVICE_DT,
    CASE WHEN REGION_TYPE = 'P' THEN 'P' ELSE 'S' END AS REGION_TYPE,
    Max(CASE WHEN INS_PROV.PROV_ID IS NOT NULL OR INS_TYPE_ID IN (1220,3428,2321) THEN 1 ELSE 0 END) AS PROV_EYE,
    Max(CASE WHEN EVENT_TYPE = 3331 AND EVENT_INSR.PROV_ID <> 'N/A' AND CLAIM_TYPE = 12 THEN ISNULL(DISCH_DT,EVNT_DATE) END) AS S1,
    Max(CASE WHEN EVENT_TYPE = 1293 AND EVENT_INSR.PROV_ID <> 'N/A' AND CLAIM_TYPE = 15 THEN EVNT_DATE END) AS S2
    -- .... 还有30多个类似的MAX(...)聚合项
FROM INS_MASTER_DATES_X, EVENT_INSR
LEFT OUTER JOIN INS_PROV ON (EVENT_INSR.PROV_ID=INS_PROV.PROV_ID)
INNER JOIN CUST_ORDER ON (EVENT_INSR.CUST_ID=CUST_ORDER.CUST_ID)
INNER JOIN REGIST_ID_IN ON (EVENT_INSR.REGIST_ID=REGIST_ID_IN.REGIST_ID)
WHERE (EVNT_DATE <= RY_END) AND (CUST_ORDER.DAY_ORDER < 5)
GROUP BY EVENT_INSR.CUST_ID, EVENT_INSR.PROV_ID, EVENT_INSR.EVNT_DATE, CASE WHEN REGION_TYPE = 'P' THEN 'P' ELSE 'S' END

注：FROM子句用了非ANSI标准的逗号连接语法，接下来计划重点分析两个版本的执行计划差异。

问题分析与后续优化方向 #

1. 兼容级别引发的优化器行为变化
   SQL Server 2016（兼容级别130）带来了新的查询优化器特性，比如新基数估算器、批处理模式等，但这套遗留的Oracle风格SQL可能没法被新优化器高效解析，导致生成了低效的执行计划——比如错误的基数估计引发全表扫描、意外笛卡尔积，最终导致数据爆炸。切回110兼容级别后，优化器退回到2012版本的行为逻辑，性能也就恢复了。

2. 非ANSI连接语法的隐患
   查询里用了FROM A,B这种旧风格的连接写法，很容易因为连接条件不明确引发意外的笛卡尔积，这很可能是数据膨胀的元凶之一。建议把所有连接改成标准的JOIN语法，明确写出连接条件，避免优化器误解查询逻辑。

3. 复杂聚合逻辑的优化空间
   查询里有30多个MAX(CASE...)的聚合项，这种逻辑在新优化器下可能会被处理成多次表扫描或者低效的聚合操作。可以考虑：

• 先做数据过滤再执行聚合，减少聚合处理的数据量；
• 评估是否能用窗口函数替代部分聚合逻辑；
• 给参与过滤、聚合、连接的字段创建合适的组合索引，比如EVNT_DATE、CUST_ID、PROV_ID这些字段的组合索引。

4. 执行计划的深度对比分析
   下一步核心要做的就是对比两个兼容级别下的执行计划：

• 检查基数估计是否准确，看实际行数和估计行数的差异；
• 找出是否存在笛卡尔积、全表扫描、高成本的排序操作；
• 分析优化器选择的连接类型（嵌套循环、哈希连接、合并连接）是否合理。

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