Azure Synapse Analytics重建表后查询性能差异原因及优化

这个性能差异是Azure Synapse Analytics专用SQL池的执行计划缓存机制、统计信息生成逻辑，结合大体积JSON解析场景的特性共同导致的，和表本身的存储结构没有直接关系：

• 重建json_dataset表后的首次执行阶段，表上不存在任何历史缓存执行计划，也没有预生成的统计信息。此时查询优化器会针对刚导入的全量JSON数据做快速采样，生成完全匹配当前数据规模、数据分布的执行计划：会将多层OPENJSON解析任务均匀分发到所有计算节点并行执行，内存授予也会匹配大JSON字段的解析需求，没有磁盘溢出开销，因此仅需50秒即可完成全量解析。
• 首次执行完成后，优化器会将本次生成的执行计划持久化到计划缓存中。后续再次执行存储过程时，默认会直接复用缓存的计划，不会重新生成。而Synapse默认的自动统计信息更新对大字段（存储JSON字符串的长文本类型）的采样率极低，仅扫描不到1%的数据页，生成的统计信息会严重低估JSON嵌套解析的计算复杂度、高估路径提取的过滤效率，一旦自动统计信息更新触发，缓存的执行计划就会和实际数据需求完全失配：比如将多层OPENJSON解析调度到单个计算节点串行执行、分配的内存不足导致大量临时数据溢出到本地磁盘，执行时长就会回升到20分钟。
• 你的场景本身会放大这个问题：存储过程包含80个不同JSON路径的提取逻辑、代码量达5000行，属于超大规模的表达式树，Synapse查询优化器对这类超长T-SQL的计划缓存适配本身就存在已知缺陷，只要缓存计划不是基于准确统计信息生成的，极大概率生成劣化的串行执行计划。

按优先级落地以下操作即可让所有执行都保持首次运行的性能：

• 强制存储过程每次执行重编译，跳过缓存计划复用。在存储过程定义头部添加WITH RECOMPILE选项，或者每次执行时携带重编译参数：

  EXEC 你的解析存储过程名 WITH RECOMPILE;
  

  5000行T-SQL的重编译开销仅为秒级，和20分钟的执行时长相比可以完全忽略，从根源上避免命中劣化的缓存计划。
• 每次加载完JSON数据、执行存储过程前，手动对json_dataset表做全量统计信息更新，不要依赖默认的自动采样：

  UPDATE STATISTICS json_dataset WITH FULLSCAN, ALL;
  

  200MB数据的全量统计更新仅需数秒，能保证优化器拿到100%准确的数据分布信息，生成和重建表后首次执行完全一致的并行解析计划。
• 固定json_dataset表的存储配置减少干扰：因为该表仅用于全量读取解析JSON，不需要做点查询或关联过滤，直接配置为ROUND_ROBIN分布的堆表即可，不需要创建任何二级索引，避免索引碎片、索引统计信息漂移带来的额外性能波动。
• 如果上述操作后仍存在偶发的性能劣化，可以在存储过程开头增加一行毫秒级完成的元数据操作，强制清空和该表相关的所有缓存计划，不需要移动表数据：

  -- 元数据操作，无数据拷贝开销
  ALTER TABLE json_dataset REBUILD WITH (DATA_COMPRESSION = PAGE);
  

注：不要尝试通过扩大资源类、增加并发槽位的方式解决这个问题，该性能差异完全是执行计划劣化导致的，和计算资源配额无关。

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