介绍
SQL Server 2022 扩展了智能查询处理 (IQP) 功能系列
最受期待的功能之一是参数敏感计划优化。
我开始测试这个新功能。在这篇文章中，您将找到一些信息来了解它的工作原理并进行首次测试。
参数敏感计划的问题
参数敏感计划，也称为“参数嗅探”，是一种由数据分布不均匀引起的场景，参数化查询的单个缓存执行计划对某些参数值表现不佳。
有几个选项可用于处理参数敏感计划查询：
- 使用 RECOMPILE 查询提示强制为所有执行执行新的计划编译
- 使用 OPTIMIZE FOR 提示为特定参数值生成执行计划
- 使用查询存储的最后一个已知良好计划
下面提到的所有方法都需要人工干预，要么在 SQL 代码级别添加查询提示，要么由 DBA 强制执行特定的执行计划。
新功能：PSP优化
这一新的 PSP 优化功能将在 SQL Server 2022 中引入，并在兼容级别 160 时默认启用。
即使查询存储将在 2022 年默认启用，PSP 优化也不需要启用查询存储，这与其他一些 IQP 功能不同。
对于符合条件的计划，初始编译会生成一个 调度程序计划 ，其中包含称为调度程序表达式的 PSP 优化逻辑。 调度程序计划根据基数范围边界值谓词映射到 查询变体。
思路如下；符合条件的查询将获得包含调度程序表达式的调度程序计划。每组重要的参数都有其查询变体，即针对这些参数优化的执行计划
PSP 优化演示
要求
SQL Server 2022 和兼容级别 160
对于此演示，您显然需要SQL Server 2022。我现在正在使用第一个公共预览版 CPT 2.0。
如上所述，此功能的先决条件是兼容级别 160。
启用该功能
PSP 优化功能默认启用。您可以使用以下命令启用/禁用它：
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON跳过 PSP 优化的原因
我很难制作一个触发 PSP 优化的场景。
使用记录在案的 XE 事件，我发现 PSP 跳过我的查询的一些原因：SkewnessThresholdNotMet、UnsupportedComparisonType或ConjunctThresholdNotMet
我不知道这些阈值是什么。我只是为演示使用了一个更大的表和一个更简单的查询。
目前在 XE “psp_skipped_reason_enum” 中列出了 32 个原因，您可以通过此查询获得：
SELECT name, map_value
FROM sys.dm_xe_map_values
WHERE name ='psp_skipped_reason_enum'
ORDER BY map_key
演示
我使用了稍微修改过的bigTransactionHistory表来获得以下数据分布，从而产生参数嗅探场景。
我使用不同的参数和启用了 PSP 优化运行以下查询两次。
EXEC sp_executesql
N'
select TransactionId, Quantity, ActualCost, TransactionDate
from dbo.bigTransactionHistory
where TransactionDate = @date'
, N'@date datetime'
, '2004-06-01 00:00:00';
GO
EXEC sp_executesql
N'
select TransactionId, Quantity, ActualCost, TransactionDate
from dbo.bigTransactionHistory
where TransactionDate = @date'
, N'@date datetime'
, '2022-06-01 00:00:00';
GO我得到了 2 个不同的执行计划，没有强制重新编译或自己强制执行计划。
在查询的两次执行期间触发了 paramter_sensitive_plan_optimization 扩展事件。我们可以注意到variant_id 信息。
执行计划显示添加到查询文本中的新提示选项“PLAN PER VALUE”：
select TransactionId, Quantity, ActualCost, TransactionDate
from dbo.bigTransactionHistory
where TransactionDate = @date option (PLAN PER VALUE(QueryVariantID = 1, predicate_range([AdventureWorks].[dbo].[bigTransactionHistory].[TransactionDate] = @date, 100.0, 10000.0)))
select TransactionId, Quantity, ActualCost, TransactionDate
from dbo.bigTransactionHistory
where TransactionDate = @date option (PLAN PER VALUE(QueryVariantID = 3, predicate_range([AdventureWorks].[dbo].[bigTransactionHistory].[TransactionDate] = @date, 100.0, 10000.0)))
根据运行查询时提供的参数值，SQL Server 将选择在运行时使用的计划。
XML 执行计划中有一个新的“Dispatcher”部分，其中包含调度程序“表达式”。
即使 PSP 工作不需要查询存储，启用它也很有用，因为您将在新的 DMV 中获得有关查询变体的信息：sys.query_store_query_variant
查询存储报告不会同时显示所有变体的聚合视图。寻找 query_id 4 没有显示任何内容。这在 SSMS 的下一个版本中可能会有用。
DMV sys.dm_exec_query_stats 中的 query_hash 对所有变体都是通用的，因此可以确定仅因输入参数值不同的查询的聚合资源使用情况。
计划缓存显示每个变体和调度程序的计划。
SELECT
p.usecounts, p.cacheobjtype
, p.objtype, p.size_in_bytes
, t.[text]
, qp.query_plan
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp
WHERE t.[text] like '%TransactionDate%'
AND p.objtype = 'Prepared'
ORDER BY p.objtype DESC
虽然 Dispatcher 计划是最大的，但它只包含上面提到的 XML Dispatcher 部分。
结论
参数敏感计划优化按照 SQL Server 2022 CTP2.0 中的描述工作。关于这个功能有很多东西要学。
我们尚不确切知道请求有资格使用此功能的条件是什么。如果有的话，我们还不知道副作用是什么。
这是一个非常有前途的功能，可以帮助稳定数据库性能并使数据库性能在某些情况下更加可预测。
作者：史蒂文·诺德
文章来源：https://www.dbi-services.com/blog/sql-server-2022-parameter-sensitive-plan-optimization/
