SQL Server 2022中如何配置智能查询处理？

Question

在SQL Server 2022中启用智能查询处理（Intelligent Query Processing, IQP）后，某些复杂查询的执行计划并未如预期使用批处理模式，导致性能提升不明显。常见问题包括：如何确认IQP功能已正确启用？自适应查询处理、批处理模式内存授予反馈和行模式标量UDF内联等特性在何种兼容性级别下生效？是否需要手动开启跟踪标志或配置数据库范围内的设置？特别是在多版本工作负载共存环境中，如何通过DMV监控IQP优化的实际应用效果并排除配置冲突？

Answer 1

1. 智能查询处理（IQP）基础概念与启用验证

智能查询处理（Intelligent Query Processing, IQP）是SQL Server 2017引入并在后续版本中不断强化的一组查询优化技术，旨在通过自适应执行、批处理模式扩展和内存授予反馈等机制提升复杂查询性能。在SQL Server 2022中，IQP功能进一步增强，但其生效依赖于多个配置层级的协同。

要确认IQP是否已正确启用，首先需检查数据库兼容性级别：

特性	最低兼容性级别	说明
自适应查询处理（Adaptive Joins）	140 (SQL Server 2017)	运行时选择Hash Join或Nested Loop
批处理模式内存授予反馈	150 (SQL Server 2019)	动态调整批处理模式下的内存分配
行模式标量UDF内联	150 (SQL Server 2019)	将标量函数转换为等价的内联表达式
内存感知批处理排序	160 (SQL Server 2022)	避免溢出到tempdb的排序操作

-- 检查当前数据库兼容性级别 SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; -- 若低于160，建议升级以充分利用SQL Server 2022 IQP特性 ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

2. 数据库范围配置与跟踪标志分析

即使兼容性级别满足要求，某些IQP特性仍需显式启用数据库范围配置（Database Scoped Configuration）。例如，行模式标量UDF内联默认开启，但可能被手动关闭。

-- 查看数据库范围内的IQP相关设置 SELECT name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name IN ( 'MAXDOP_FOR_SECONDARY', 'LEGACY_CARDINALITY_ESTIMATION', 'PARAMETER_SNIFFING', 'QUERY_OPTIMIZER_HOTFIXES', 'ROW_MODE_MEMORY_GRANT_FEEDBACK', 'BATCH_MODE_ON_ROWSTORE', 'DEFERRED_COMPILATION_TV' );

关键配置项说明：

• BATCH_MODE_ON_ROWSTORE：允许在非列存储表上使用批处理模式，对复杂OLAP类查询至关重要。
• ROW_MODE_MEMORY_GRANT_FEEDBACK：启用行模式下的内存反馈机制，减少过度授予。
• RESUMABLE_INDEX_BUILD：虽非IQP核心，但在大表维护后影响统计信息新鲜度，间接影响计划选择。

部分场景下仍需启用全局跟踪标志（Trace Flag），如TF 2371用于动态统计更新阈值，但应优先使用兼容性级别和数据库范围配置替代。

3. 执行计划未使用批处理模式的根因排查

即便启用了，执行计划仍可能停留在行模式，原因包括：

1. 缺少列存储索引：传统堆或B树表需依赖“批处理模式在行存储”（Batch Mode on Rowstore）技术。
2. 谓词或连接条件不支持批处理运算符。
3. 并行度限制（MAXDOP=1）导致优化器放弃批处理路径。
4. 内存压力或资源调控器策略限制批处理执行。
5. 统计信息陈旧，导致基数估算偏差，影响成本模型判断。
6. 存在不兼容操作符，如CLR函数、特定聚合函数等。
7. 查询结构复杂，涉及大量嵌套视图或CTE，优化器保守选择行模式。
8. 实例级或会话级设置了抑制批处理的选项。

graph TD A[复杂查询性能不佳] --> B{是否启用兼容性160?} B -->|否| C[升级兼容性级别] B -->|是| D{是否启用BATCH_MODE_ON_ROWSTORE?} D -->|否| E[ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON] D -->|是| F[检查执行计划是否含Batch Mode Operator] F -->|否| G[分析缺失列存储/CPU/Memory瓶颈] G --> H[添加聚集列存储索引或优化资源]

4. 动态管理视图（DMV）监控IQP实际应用效果

在多版本工作负载共存环境中，不同数据库可能处于不同兼容性级别，需通过DMV识别IQP优化的实际触发情况。

-- 监控批处理模式内存授予反馈的应用 SELECT database_id, object_id, index_id, execution_count, avg_duration, last_duration, is_memory_grant_feedback_adjusted FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) WHERE is_memory_grant_feedback_adjusted = 1; -- 查询使用批处理模式的语句 SELECT t.text, p.query_plan, ps.avg_elapsed_time, ps.avg_logical_io FROM sys.dm_exec_query_stats AS ps CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS p CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS t WHERE CAST(p.query_plan AS NVARCHAR(MAX)) LIKE '%BatchMode%';

结合以下系统视图进行综合诊断：

• sys.dm_db_tuning_recommendations：获取引擎推荐的索引或配置变更。
• sys.dm_exec_valid_use_hints：查看可用查询提示，辅助测试IQP行为。
• sys.dm_os_performance_counters：监控"Batch Mode"相关计数器变化趋势。