参数敏感计划优化
适用于：
SQL Server 2022 (16.x) 及更高版本、Azure SQL 数据库和 Azure SQL 托管实例
参数敏感计划 (PSP) 优化是智能查询处理系列功能的一部分。 它解决了参数化查询的单个缓存计划对于所有可能的传入参数值都不是最佳方案的情况。 这是数据分布不均匀的情况。 有关详细信息，请参阅参数敏感度和参数和执行计划的重复使用。
有关此问题场景的现有解决方法的详细信息，请参阅：
PSP 优化自动为单个参数化语句启用多个活动缓存计划。 缓存执行计划将根据客户提供的运行时参数值适应不同的数据大小。
了解参数化
在 SQL Server 数据库引擎中，在 Transact-SQL (T-SQL) 语句中使用参数或参数标记可以提高关系引擎将新的 T-SQL 语句与现有的、以前编译的执行计划相匹配并促进计划重复利用的能力。 有关详细信息，请参阅简单参数化。
通过指定将数据库中的所有 SELECT、INSERT、UPDATE 和 DELETE 语句参数化，还可以覆盖 SQL Server 的默认简单参数化行为（但会受到某些限制）。 有关详细信息，请参阅强制参数化。
PSP 优化实现
在初始编译期间，列统计信息直方图可识别非统一分布，并计算最具风险的参数化谓词，其中最多有三个可用的谓词。 换句话说，如果同一查询中的多个谓词满足条件，PSP 优化将选择前三个。 PSP 功能限制要评估的谓词数，以避免计划缓存和计划过多的查询存储（如果启用了查询存储）膨胀。
对于符合条件的计划，初始编译将生成一个调度程序计划，其中包含名为调度程序表达式的 PSP 优化逻辑。 调度程序计划将根据基数范围边界值谓词映射到查询变体。
术语
调度程序表达式
根据运行时参数值评估谓词的基数，并将执行路由到不同的查询变体。
调度程序计划
为原始查询缓存包含调度程序表达式的计划。 调度程序计划实质上是功能选择的谓词的集合，其中包含一些额外的详细信息。 对于选择的每个谓词，调度程序计划中包含的一些详细信息是高边界值和低边界值。 这些值用于将参数值划分为不同的存储桶或范围。 调度程序计划还包含用于计算边界值的统计信息。
查询变体
当调度程序计划根据运行时参数值评估谓词的基数时，它会将这些值放入存储桶中，并生成单独的子查询进行编译和执行。 这些子查询称为查询变体。 查询变体在计划缓存和查询存储中有自己的计划。
谓词基数范围
在运行时，根据运行时参数值计算每个谓词的基数。 调度程序在编译时将基数值放入三个谓词基数范围。 例如，PSP 优化功能可以创建三个表示低、中和高基数范围的范围，如下图所示。
换句话说，当最初编译参数化查询时，PSP 优化功能会生成一个称为调度程序计划的 shell 计划。 调度程序表达式具有根据参数的运行时值将查询放入查询变体的逻辑。 实际执行开始时，调度程序执行两个步骤：
调度程序针对给定的参数集计算其调度程序表达式，以计算基数范围。
调度程序将这些范围映射到特定的查询变体，并编译和执行这些变体。 由于具有多个查询变体，PSP 优化功能可实现单个查询的多个计划。
可以在调度计划的 ShowPlan XML 中看到基数范围边界：
<Dispatcher>
<ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
<StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" />
<Predicate>
<ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@AgentId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</ParameterSensitivePredicate>
</Dispatcher>
PSP 优化生成的提示将追加到查询变体的 ShowPlan XML 中的 SQL 语句。 提示不能直接使用，如果未手动添加，则不会进行分析。 提示包含以下元素：
option ( PLAN PER VALUE ( ObjectID = (int), QueryVariantID = (int), predicate_range ( [databaseName].[schemaName].[tableName].[columnName] = @paramName, lowBoundaryValue, highBoundaryValue ) ) )
- ObjectID 来自当前语句所属的模块（即存储过程、函数、触发器）；假设该语句是从模块中生成的。 如果语句是动态或临时 SQL（即
sp_executesql）的结果，则 ObjectID 元素等于0。 - QueryVariantID 大致相当于 PSP 优化选择的所有谓词的范围组合。 例如，如果查询有两个符合 PSP 条件的谓词，并且每个谓词有三个范围，则有 9 个编号为 1-9 的查询变体范围。
- 谓词范围是从调度程序表达式生成的谓词基数范围信息。
在查询变体的 ShowPlan XML 中（Dispatcher 元素中）：
<Batch>
<Statements>
<StmtSimple StatementText="SELECT PropertyId,
 AgentId,
 MLSLinkId,
 ListingPrice,
 ZipCode,
 Bedrooms,
 Bathrooms
FROM dbo.Property
WHERE AgentId = @AgentId
ORDER BY ListingPrice DESC option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1, predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090085E4372DFC69BB9E7EBA421561DE8E1E0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="0.021738" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x476167A000F589CD" QueryPlanHash="0xDE982107B7C28AAE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<Dispatcher>
<ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06">
<StatisticsInfo LastUpdate="2019-09-07T15:32:16.89" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_Property_AgentId]" Table="[Property]" Schema="[dbo]" Database="[PropertyMLS]" />
<Predicate>
<ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@AgentId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</ParameterSensitivePredicate>
</Dispatcher>
</StmtSimple>
</Statements>
</Batch>
注解
PSP 优化功能目前仅适用于相等谓词。
如果存在重大的数据分布更改，调度程序计划会自动重新生成。 与任何其他查询计划类型一样，查询变体计划会根据需要独立重新编译，但会受默认的重新编译事件影响。 有关重新编译的详细信息，请参阅重新编译执行计划。
sys.query_store_plan (Transact-SQL) 查询存储系统目录视图已更改，以区分常规编译计划、调度程序计划和查询变体计划。 新的查询存储系统目录视图 sys.query_store_query_variant (Transact-SQL) 包含有关原始参数化查询（也称为父查询）、调度程序计划及其子查询变体之间的父子关系的信息。
当同一个表中有多个谓词时，PSP 优化会根据基础统计直方图选择数据倾斜最大的谓词。 例如，对于
SELECT * FROM table WHERE column1 = @predicate1 AND column2 = @predicate2，因为column1 = @predicate1和column2 = @predicate2来自同一个表，table1，所以只有最倾斜的谓词将由该功能计算。 但是，如果示例查询涉及运算符（如UNION），PSP 将计算多个谓词。 例如，如果查询具有类似于SELECT * FROM table WHERE column1 = @predicate UNION SELECT * FROM table WHERE column1 = @predicate的特征，则 PSP 在本例中最多选取两个谓词，因为系统会将此应用场景视为两个不同的表。 通过表别名自联接的查询中也可以观察到同样的行为。查询变体的 ShowPlan XML 类似于下面的示例，其中选择的两个谓词都将各自的信息添加到
PLAN PER VALUE PSP相关提示中。<Batch> <Statements> <StmtSimple StatementText="SELECT b.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty a join PropertyDetails b on a.PropertyId = b.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id
UNION
 SELECT c.PropertyId, 
 AgentId, 
 MLSLinkId, 
 ListingPrice, 
 ZipCode, 
 Bedrooms, 
 Bathrooms 
FROM dbo.AgentProperty c join PropertyDetails d on c.PropertyId = d.PropertyId
WHERE AgentId = @AgentId and Property_id=@Property_id option (PLAN PER VALUE(ObjectID = 981578535, QueryVariantID = 9, predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0),predicate_range([PropertyMLS].[dbo].[AgentProperty].[AgentId] = @AgentId, 100.0, 1000000.0)))" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x090051FBCD918F8DFD60D324887356B422D90000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="false" StatementSubTreeCost="29.2419" StatementEstRows="211837" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x6D2A4E407085C01E" QueryPlanHash="0x72101C0A0DD861AB" CardinalityEstimationModelVersion="160" BatchModeOnRowStoreUsed="true"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <Dispatcher> <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06"> <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" /> <Predicate> <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [a].[AgentId]=[@AgentId]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[a]" Column="AgentId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@AgentId" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </ParameterSensitivePredicate> <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1e+06"> <StatisticsInfo LastUpdate="2022-08-11T20:42:35.02" ModificationCount="0" SamplingPercent="100" Statistics="[NCI_AgentProperty_AgentId]" Table="[AgentProperty]" Schema="[dbo]" Database="[PropertyMLS]" /> <Predicate> <ScalarOperator ScalarString="[PropertyMLS].[dbo].[AgentProperty].[AgentId] as [c].[AgentId]=[@AgentId]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[AgentProperty]" Alias="[c]" Column="AgentId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@AgentId" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </ParameterSensitivePredicate> </Dispatcher> <QueryPlan CachedPlanSize="160" CompileTime="17" CompileCPU="17" CompileMemory="1856" QueryVariantID="9">可以使用以下一种或多种方法来影响 PSP 优化功能使用的当前偏度阈值：
基数估算器 (CE) 跟踪标志，例如跟踪标志 9481（全局、会话或查询级别）
数据库范围的配置选项，试图降低使用中的 CE 模型，或影响 CE 模型对多个谓词的独立性所作的假设。 这在不存在多列统计信息的情况下尤其有用，这会影响 PSP 优化评估这些谓词的候选资格的能力。
有关详细信息，请参阅使用 SQL Server 2014 基数估算器优化查询计划白皮书中的增加多个谓词的相关性假设部分。 新的 CE 模型试图假设谓词的连接和析取具有一定的相关性和较低的独立性。 使用旧 CE 模型可能会影响多列联接方案中谓词选择性的计算方式。 此操作仅适用于特定场景，不建议在大多数工作负载中使用旧 CE 模型。
PSP 优化当前将每个查询变体作为新的准备语句进行编译和执行，这就是如果调度程序计划基于模块（即存储过程、触发器、函数、视图等），查询变体会失去与任何父模块
object_id关联的原因之一。 作为准备语句，object_id并不能直接映射到sys.objects的对象中，而本质上是基于批处理文本的内部哈希计算出来的。 有关详细信息，请参阅sys.dm_exec_plan_attributesDMV 文档中返回的表部分。查询变体计划放置在计划缓存对象存储 (
CACHESTORE_OBJCP) 中，而调度程序计划放置在 SQL 计划缓存存储 (CACHESTORE_SQLCP) 中。 但是，PSP 功能会将查询变体父查询的object_id存储在 ObjectID 属性中，该属性是 PLAN PER VALUE 提示的一部分，如果父查询是模块的一部分并且不是动态或临时的 T-SQL，PSP 会添加到 ShowPlan XML 中。 缓存过程、函数和触发器的聚合性能统计信息可以继续用于各自的用途。 细粒度的执行相关统计信息（如在类似于sys.dm_exec_query_statsDMV 的视图中发现的统计信息）仍包含查询变体的数据，但是，object_id查询变体和sys.objects表中对象之间的关联当前不一致，无需对每个查询变体的 ShowPlan XML 进行额外处理，在这些查询变体中需要更细粒度的运行时统计信息。 如果启用了查询存储，则可以从查询存储获取查询变体的运行时和等待统计信息，而无需使用其他 ShowPlan XML 分析技术。由于 PSP 查询变体作为新的准备语句执行，因此在不分解 ShowPlan XML 和应用文本模式匹配技术（即额外的 XQuery 处理）的情况下，
object_id不会在各种计划缓存相关的sys.dm_exec_*DMV 中自动公开。 当前只有 PSP 优化调度程序计划发出相应的父对象 ID。 查询存储中公开了object_id，因为查询存储允许比计划缓存层次结构提供的更多的关系模型。 有关详细信息，请参阅查询存储系统目录视图 sys.query_store_query_variant (Transact-SQL)。
注意事项
若要启用 PSP 优化，请在执行查询时为连接到的数据库启用数据库兼容性级别 160。
若要深入了解 PSP 优化功能，建议通过打开查询存储来启用查询存储集成。 下面的示例为名为
MyNewDatabase的预先存在的数据库打开了查询存储：
ALTER DATABASE [MyNewDatabase]
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = AUTO
);
注意
从 SQL Server 2022 (16.x) 开始，现在默认为所有新创建的数据库启用查询存储。
若要在数据库级别禁用 PSP 优化，请使用
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF数据库范围的配置。若要在查询级别禁用 PSP 优化，请使用
DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION查询提示。如果跟踪标志 4136、
PARAMETER_SNIFFING数据库范围的配置或USE HINT('DISABLE_PARAMETER_SNIFFING')查询提示禁用参数探查，则将为关联的工作负载和执行上下文禁用 PSP 优化。 有关详细信息，请参阅提示 (Transact-SQL) - 查询和 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)。计划缓存中存储的每个调度程序的唯一计划变体数量受到了限制，避免出现缓存膨胀。 未记录内部阈值。 由于每个 SQL 批处理都有可能创建多个计划，并且每个查询变体计划在计划缓存中都有一个独立的条目，因此可以达到默认的最大允许计划条目数。 如果计划缓存逐出率明显较高，或者
CACHESTORE_OBJCP和CACHESTORE_SQLCP缓存存储的大小过大，则应考虑应用跟踪标志 174。max_plans_per_query配置选项限制了查询存储存储中可为查询存储的唯一计划变体数。 由于查询变体可以有多个计划，因此查询存储中每个查询总共可以有 200 个计划。 此数字包括属于父查询的所有调度程序的所有查询变体计划。 考虑增加max_plans_per_query查询存储配置选项。- 下面举例说明了唯一计划的数量如何超出默认查询存储
max_plans_per_query限制。 假设你有一个查询 ID 为 10 的查询，它包含两个调度程序计划，每个调度程序计划各有 20 个查询变体（总共 40 个查询变体）。 查询 ID 10 的计划总数为 40 个，包括查询变体和两个调度计划。 父查询本身（查询 ID 10）也可能有 5 个常规（非调度程序）计划。 这样就有 47 个计划（40 个来自查询变体、2 个调度程序和 5 个与 PSP 无关的计划）。 此外，如果每个查询变体平均也有 5 个计划，那么在这种情况下，父查询的查询存储中可能有超过 200 个计划。 这还取决于此示例父查询可能引用的数据集中的严重数据倾斜。
- 下面举例说明了唯一计划的数量如何超出默认查询存储
对于映射到给定调度程序的每个查询变体：
query_plan_hash是唯一的。 此列在sys.dm_exec_query_stats以及其他动态管理视图和目录表中可用。plan_handle是唯一的。 此列提供在sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plans、其他动态管理视图和函数以及目录表中。- 对于映射到同一调度程序的其他变体，
query_hash是相同的，因此，可以确定区别仅在于输入参数值的查询的聚合资源使用情况。 此列提供在sys.dm_exec_query_stats、sys.query_store_query以及其他动态管理视图和目录表中。 - 由于在编译过程中向查询文本添加的特殊 PSP 优化标识符，因此，
sql_handle是唯一的。 此列提供在sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_cached_plans、其他动态管理视图和函数以及目录表中。 查询存储中提供了与sys.query_store_query目录表中的last_compile_batch_sql_handle列相同的句柄信息。 query_id在查询存储中是唯一的。 此列提供在sys.query_store_query和其他查询存储目录表中。
在查询存储中强制执行计划
使用相同的 sp_query_store_force_plan 和 sp_query_store_unforce_plan 存储过程对调度程序或变体计划进行操作。
如果变体是强制执行的，父调度程序则不会强制执行。 如果调度程序是强制执行的，则只有该调度程序中的变体才被视为有资格使用：
- 其他调度程序中之前强制执行的变体将变为非活动状态，但会保留强制状态，直到它们的调度程序再次被强制执行
- 已处于非活动状态的同一调度程序中之前强制执行的变体将再次被强制执行
查询存储查询提示行为
将查询存储提示添加到查询变体（子查询）时，将会以与非 PSP 查询相同的方式应用提示。 如果查询存储中的父查询也应用了提示，查询变体提示具有更高的优先级。
如果将查询存储提示添加到父查询，而子查询（查询变体）没有现有的查询存储提示，则子查询（查询变体）将继承父查询的提示。
如果从父查询中移除查询存储查询提示，则子查询（查询变体）也会移除提示。
如果将
RECOMPILE提示添加到父查询，则在从计划缓存中移除任何现有查询变体计划后，系统将生成非 PSP 计划，因为 PSP 功能不适用于带RECOMPILE提示的查询。可以使用扩展事件
query_store_hints_application_success和query_store_hints_application_failed事件来观察查询存储提示结果。 对于 sys.query_store_query_hints 表，它包含有关已应用的查询提示的信息。 如果提示仅应用于父查询，则系统目录包含父查询的提示信息，但不包含其子查询的提示信息，尽管子查询继承了父查询的提示。
下表汇总了具有查询提示和计划强制行为的 PSP：
| 查询变体提示或计划 | 父级具有用户应用的提示 | 父级具有反馈应用的提示 | 父级已手动强制计划 | 父级有 APC 1 强制计划 |
|---|---|---|---|---|
| 通过用户提示 | 查询变体提示 | 查询变体提示 | 查询变体提示 | 空值 |
| 通过反馈提示 | 查询变体提示 | 查询变体提示 | 查询变体提示 | 空值 |
| 用户强制计划 | 查询变体 强制计划 |
查询变体 强制计划 |
查询变体 强制计划 |
查询变体 强制计划 |
| APC 强制计划 | 查询变体 强制计划 |
查询变体 强制计划 |
查询变体 强制计划 |
查询变体 强制计划 |
| 无提示或强制计划 | 父用户的提示 | 无提示 | 无操作 | 无操作 |
1 自动调整功能的自动计划更正组件
扩展事件
parameter_sensitive_plan_optimization_skipped_reason：跳过参数敏感计划功能时发生。 使用此事件来监视跳过 PSP 优化的原因。以下查询显示了跳过 PSP 的所有可能的原因：
SELECT map_value FROM sys.dm_xe_map_values WHERE [name] ='psp_skipped_reason_enum' ORDER BY map_key;parameter_sensitive_plan_optimization：当查询使用 PSP 优化功能时发生。 仅调试通道。 一些相关的领域可能包括：- is_query_variant：描述这是调度程序计划（父计划）还是查询变体计划（子计划）
- predicate_count: PSP 选择的谓词数
- query_variant_id：显示查询变体 ID。 值为 0 表示对象是调度程序计划（父计划）。
SQL Server 审核行为
PSP 优化为调度程序计划语句以及与调度程序关联的任何查询变体提供审核数据。 SQL Server Audit 中的 additional_information 列还为查询变体提供了适当的 T-SQL 堆栈信息。 以 MyNewDatabase 数据库为例，如果该数据库有一个名为 T2 的表和一个名为 usp_test 的存储过程，则执行 usp_test 存储过程后，审核日志可能包含以下条目：
| action_id | object_name | 语句 | additional_information |
|---|---|---|---|
| AUSC | <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><session><![CDATA[Audit_Testing_Psp$A]]></session><action>event enabled</action><startup_type>manual</startup_type><object><![CDATA[audit_event]]></object></action_info> |
||
| EX | usp_test | exec usp_test 300 | |
| SL | T2 | select * from dbo.t2 where ID=@id | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| SL | T2 | select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 1, predicate_range([MyNewDatabase].[dbo].[T2].[ID] = @id, 100.0, 100000.0))) | tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| EX | usp_test | exec usp_test 60000 | |
| SL | T2 | select * from dbo.t2 where ID=@id | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
| SL | T2 | select * from dbo.t2 where ID=@id option (PLAN PER VALUE(ObjectID = 933578364, QueryVariantID = 3, predicate_range([TestDB_FOR_PSP].[dbo].[T2].[ID] = @id, 100.0, 100000.0))) | <tsql_stack><frame nest_level = '1' database_name = 'MyNewDatabase' schema_name = 'dbo' object_name = 'usp_test'/></tsql_stack> |
已知问题
| 问题 | 发现日期 | 状态 | 解决日期 |
|---|---|---|---|
| 在某些条件下，SQL Server 2022 (16.x) 中的查询存储中会出现访问冲突异常。 启用 PSP 优化查询存储集成后，可能会遇到 Access 冲突异常。 有关详细信息，请参阅参数敏感计划优化，为什么？中的更新。 | 2023 年 3 月 | 已解决 | 2023 年 8 月 (CU 7) |
已解决
在某些条件下，SQL Server 2022 中的查询存储会出现访问冲突异常。
注意
从 SQL Server 2022 (16.x) 累积更新 7 开始，已发布了多个针对可能导致访问冲突的争用条件的修补程序。 如果在应用 SQL Server 2022 (16.x) 的累积更新 7 之后，出现与带有查询存储集成的 PSP 优化相关的访问冲突，请考虑以下解决方法部分。
出现此问题的原因是，当执行查询的运行时统计信息从查询存储的内存表示形式（在 MEMORYCLERK_QUERYDISKSTORE_HASHMAP 内存分配器中找到）保留到查询存储的磁盘版本时，可能会导致争用条件。 显示为 Runtime Stats 的运行时统计信息会在内存中保留一段时间，该时间用 SET QUERY_STORE 语句的 DATA_FLUSH_INTERVAL_SECONDS 选项定义（默认值为 15 分钟）。 可以使用“Management Studio 查询存储”对话框为“数据刷新间隔（分钟）”输入一个值，该值在内部转换为秒。 如果系统出现内存压力，运行时统计信息会在按照 DATA_FLUSH_INTERVAL_SECONDS 选项定义的时间之前刷新到磁盘。 当与查询存储查询计划清理（也即 STALE_QUERY_THRESHOLD_DAYS 和/或 MAX_STORAGE_SIZE_MB 查询存储选项）相关的其他查询存储后台线程从查询存储进行查询时，就会出现查询变体和/或其关联的调度程序语句可能过早取消引用的情况。 这可能会导致在查询存储中插入或删除查询变体操作期间发生访问冲突。
有关查询存储操作的更多信息，请参阅“查询存储如何收集数据”一文的备注部分。
解决方法：如果你的系统在应用 SQL Server 2022 (16.x) 的累积更新 7 后，在启用 PSP 集成的查询存储中仍遇到访问冲突问题，则可以删除查询存储中的查询变体，或在查询或数据库级别暂时禁用 PSP 功能，直到其他修复程序可用为止。
- 若要在数据库级别禁用 PSP 优化，请使用
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF数据库范围的配置。 - 若要在查询级别禁用 PSP 优化，请使用
DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION查询提示。
若要从查询存储中移除所有查询变体，而不仅仅是 sys.query_store_query_variant (Transact-SQL) 目录视图中出现的查询变体，可以使用类似于以下查询的查询。 将 [<database>] 替换为遇到问题的相应数据库：
USE master;
GO
--Temporarily turn Query Store off in order to remove query variant plans as well as to
--clear the Query Store in-memory representation of Query Store (HashMap) for a particular database
ALTER DATABASE [<database>] SET QUERY_STORE = OFF;
GO
USE [<database>];
GO
DECLARE @QueryIDsCursor CURSOR;
DECLARE @QueryID BIGINT;
BEGIN
-- Getting the cursor for query IDs for query variant plans
SET @QueryIDsCursor = CURSOR FAST_FORWARD FOR
SELECT query_id
FROM sys.query_store_plan
WHERE plan_type = 2 --query variant plans
ORDER BY query_id;
-- Using a non-set based method for this example query
OPEN @QueryIDsCursor
FETCH NEXT FROM @QueryIDsCursor
INTO @QueryID
WHILE @@FETCH_STATUS = 0
BEGIN
-- Deleting query variant(s) from the Query Store
EXEC sp_query_store_remove_query @query_id = @QueryID;
FETCH NEXT FROM @QueryIDsCursor
INTO @QueryID
END;
CLOSE @QueryIDsCursor ;
DEALLOCATE @QueryIDsCursor;
END;
--Turn Query Store back on
ALTER DATABASE [<database>] SET QUERY_STORE = ON;
GO
如果你的查询存储很大，或者你的系统具有大量工作负载和/或大量符合查询存储捕获条件的临时非参数化查询，则关闭查询存储可能需要一些时间。 若要在这些场景中强制关闭查询存储，请改用前面示例 T-SQL 中的 ALTER DATABASE [<database>] SET QUERY_STORE = OFF (FORCED) 命令。 若要查找非参数化查询，请参阅在查询存储中查找非参数化查询。