SQL Server查询计划优化器是否有性能控制参数及查询方法？

SQL Server没有和PostgreSQL的遗传查询优化器（GEQO）完全对等的组件，但有一系列参数和机制可以控制查询优化器的行为，用来平衡优化耗时和生成计划的质量，尤其在多表连接场景下：

核心控制参数与机制 #

• MAXDOP（最大并行度）：限制查询执行时使用的CPU核心数，当处理多表连接这类复杂查询时，过高的并行度会增加优化器的规划开销，调整这个参数可以减少优化时间，避免资源过度消耗。
• QUERY_OPTIMIZER_HOTFIXES：启用查询优化器的补丁修复逻辑，会改变优化器的决策路径，间接影响计划质量和优化耗时。
• OPTIMIZE FOR AD HOC WORKLOADS：针对临时查询优化计划缓存策略，减少重复生成计划的开销，适合存在大量临时查询的场景。
• 查询级提示（USE HINT）：虽然不是实例级参数，但可以针对单个查询控制优化器行为，比如：
  • OPTIMIZE FOR UNKNOWN：让优化器基于统计信息而非具体参数值生成计划，避免参数嗅探导致的次优计划，同时减少针对特定参数值的规划时间；
  • FORCE ORDER：强制优化器按照查询中表的顺序进行连接，避免尝试大量连接顺序组合，大幅减少规划耗时；
  • MAXDOP n：指定单个查询的并行度，灵活控制该查询的优化和执行开销。

查询参数值的方法 #

1. 实例级配置参数查询 #

通过系统视图sys.configurations可以查询所有实例级可配置参数，筛选相关参数的语句如下：

SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN ('max degree of parallelism', 'query optimizer hotfixes', 'optimize for ad hoc workloads');

这个视图对应PostgreSQL中SHOW ALL查看实例参数的场景，能获取参数的配置值、生效值和描述。

2. 会话级参数查询 #

如果要查看当前会话的参数设置（比如临时修改后的生效值），可以用：

SELECT name, value
FROM sys.dm_exec_session_settings
WHERE session_id = @@SPID;

3. 跟踪标志查询 #

部分高级优化控制通过跟踪标志实现（比如TF 4138禁用参数嗅探、TF 2371调整统计信息更新阈值），查看当前启用的跟踪标志用：

DBCC TRACESTATUS;

4. 查询计划中的优化逻辑查看 #

要确认特定查询的优化行为（比如是否使用了指定提示），可以查看执行计划，或者用动态管理视图提取计划：

SELECT query_plan
FROM sys.dm_exec_query_plan(/* 替换为目标查询的plan_handle */);

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