sys.dm_db_tuning_recommendations (Transact-SQL)
适用于: SQL Server 2017 (14.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例
返回有关自动优化建议的详细信息。 有关详细信息,请参阅 自动优化
有关详细信息,请参阅 Azure SQL Database 和 Azure SQL 托管实例 中的监视和性能优化。
在 Azure SQL 数据库中,动态管理视图不能公开影响数据库包含的信息,也不能公开用户有权访问的其他数据库的相关信息。 为了避免公开此信息,将筛选出包含不属于已连接租户的数据的每一行。
列名 | Data type | 描述 |
---|---|---|
name | nvarchar(4000) | 建议的唯一名称。 |
type | nvarchar(4000) | 生成建议的自动优化选项的名称,例如 FORCE_LAST_GOOD_PLAN |
reason | nvarchar(4000) | 提供此建议的原因。 |
valid_since | datetime2 | 首次生成此建议。 |
last_refresh | datetime2 | 上次生成此建议的时间。 |
State | nvarchar(4000) | 描述建议状态的 JSON 文档。 以下字段可用: - currentValue - 建议的当前状态。- reason - 描述建议处于当前状态的原因的常量。 |
is_executable_action | bit | 1 = 可以通过 Transact-SQL 脚本对数据库执行建议。 0 = 无法对数据库执行建议 (例如:仅信息或已还原的建议) |
is_revertable_action | bit | 1 = 数据库引擎可以自动监视和还原建议。 0 = 无法自动监视和还原建议。 大多数 可执行 操作都是 可还原的。 |
execute_action_start_time | datetime2 | 应用建议的日期。 |
execute_action_duration | time | 执行操作的持续时间。 |
execute_action_initiated_by | nvarchar(4000) | User = 建议中的用户手动强制计划。System = 系统自动应用的建议。 |
execute_action_initiated_time | datetime2 | 应用建议的日期。 |
revert_action_start_time | datetime2 | 建议还原的日期。 |
revert_action_duration | time | 还原操作的持续时间。 |
revert_action_initiated_by | nvarchar(4000) | User = 用户手动取消强制建议的计划。System = 系统自动还原建议。 |
revert_action_initiated_time | datetime2 | 建议还原的日期。 |
score | int | 此建议在 0-100 刻度上的估计值/影响 (越大越好) |
details | nvarchar(max) | 包含有关建议的更多详细信息的 JSON 文档。 以下字段可用:planForceDetails - queryId - query_id回归的查询。- regressedPlanId - 回归计划的plan_id。- regressedPlanExecutionCount - 在检测到回归之前,使用回归计划执行查询的次数。- regressedPlanAbortedCount - 在执行回归计划期间检测到的错误数。- regressedPlanCpuTimeAverage -) 回归查询在检测到回归之前消耗的平均 CPU 时间 (微秒。- regressedPlanCpuTimeStddev - 检测到回归之前回归查询所消耗的 CPU 时间的标准偏差。- recommendedPlanId - 应强制plan_id计划。- recommendedPlanExecutionCount - 使用在检测到回归之前应强制计划的查询执行次数。- recommendedPlanAbortedCount - 在执行计划期间检测到应强制的错误数。- recommendedPlanCpuTimeAverage - 使用计划执行的查询消耗的平均 CPU 时间 (微秒) ,在检测到回归) 之前,应强制 (计算。- recommendedPlanCpuTimeStddev 在检测到回归之前,回归查询所消耗的 CPU 时间的标准偏差。implementationDetails - method - 用于更正回归的方法。 值始终 TSql 为 。- script - 应执行以强制执行建议计划的 Transact-SQL 脚本。 |
备注
当数据库引擎识别出潜在的查询性能回归时,将更新 返回 sys.dm_db_tuning_recommendations
的信息,并且不会持久保存。 仅在重启数据库引擎之前保留建议。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time
列查找上次数据库引擎启动时间。 如果数据库管理员希望在服务器回收后保留优化建议,应定期创建优化建议的备份副本。
列中 currentValue
的 state
字段可能具有以下值:
状态 | 说明 |
---|---|
Active |
建议处于活动状态,尚未应用。 用户可以采用建议脚本并手动执行。 |
Verifying |
建议由数据库引擎应用,内部验证过程将强制计划的性能与回归计划进行比较。 |
Success |
已成功应用建议。 |
Reverted |
建议已还原,因为性能没有显著提升。 |
Expired |
建议已过期,无法再应用。 |
列中的 state
JSON 文档包含说明为何建议处于当前状态的原因。 原因字段中的值可能是:
原因 | 说明 |
---|---|
SchemaChanged |
建议已过期,因为引用表的架构已更改。 如果在新架构上检测到新的查询计划回归,则会创建新建议。 |
StatisticsChanged |
由于引用表的统计信息更改,建议已过期。 如果基于新统计信息检测到新的查询计划回归,则会创建新建议。 |
ForcingFailed |
建议的计划不能强制执行查询。 last_force_failure_reason 在sys.query_store_plan视图中查找 以查找失败原因。 |
AutomaticTuningOptionDisabled |
FORCE_LAST_GOOD_PLAN 选项在验证过程中被用户禁用。 使用 ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) 语句启用FORCE_LAST_GOOD_PLAN 选项,或使用列中的脚本details 手动强制计划。 |
UnsupportedStatementType |
无法对查询强制计划。 不支持的查询的示例包括游标和 INSERT BULK 语句。 |
LastGoodPlanForced |
已成功应用建议。 |
AutomaticTuningOptionNotEnabled |
数据库引擎发现了潜在的性能回归,但 FORCE_LAST_GOOD_PLAN 选项未启用 - 请参阅 ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) 。 手动应用建议或启用 FORCE_LAST_GOOD_PLAN 选项。 |
VerificationAborted |
由于重启或查询存储清理,验证过程已中止。 |
VerificationForcedQueryRecompile |
查询已重新编译,因为性能没有显著改进。 |
PlanForcedByUser |
用户使用 sp_query_store_force_plan (Transact-SQL) 过程手动强制实施计划。 如果用户明确决定强制实施某个计划,则数据库引擎不会应用建议。 |
PlanUnforcedByUser |
用户使用 sp_query_store_unforce_plan (Transact-SQL) 过程手动取消强制执行计划。 由于用户显式还原了建议的计划,数据库引擎将继续使用当前计划,并在将来发生计划回归时生成新建议。 |
UserForcedDifferentPlan |
用户使用 sp_query_store_force_plan (Transact-SQL) 过程手动强制实施不同的计划。 如果用户明确决定强制实施某个计划,则数据库引擎不会应用建议。 |
TempTableChanged |
计划中使用的临时表将更改。 |
列中的 details
统计信息不显示运行时计划统计信息 (例如,当前 CPU 时间) 。 建议详细信息是在进行回归检测时获取的,并描述数据库引擎识别性能回归的原因。 使用 regressedPlanId
和 recommendedPlanId
查询查询存储目录视图,以查找确切的运行时计划统计信息。
使用优化建议信息的示例
示例 1
以下示例代码获取生成的 Transact-SQL 脚本,该脚本强制任何给定查询制定良好的计划:
SELECT name,
reason,
score,
JSON_VALUE(details, '$.implementationDetails.script') AS script,
details.*
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(details, '$.planForceDetails') WITH (
[query_id] INT '$.queryId',
regressed_plan_id INT '$.regressedPlanId',
last_good_plan_id INT '$.recommendedPlanId'
) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';
示例 2
下面获取生成的 Transact-SQL 脚本,该脚本强制任何给定查询制定良好的计划,以及有关估计收益的其他信息:
SELECT reason,
score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
planForceDetails.*,
estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
[query_id] INT '$.queryId',
regressedPlanId INT '$.regressedPlanId',
recommendedPlanId INT '$.recommendedPlanId',
regressedPlanErrorCount INT,
recommendedPlanErrorCount INT,
regressedPlanExecutionCount INT,
regressedPlanCpuTimeAverage FLOAT,
recommendedPlanExecutionCount INT,
recommendedPlanCpuTimeAverage FLOAT
) AS planForceDetails;
示例 3
下面获取生成的 Transact-SQL 脚本,该脚本强制任何给定查询和附加信息,包括查询文本和存储在 查询存储 中的查询计划:
WITH cte_db_tuning_recommendations
AS (
SELECT reason,
score,
query_id,
regressedPlanId,
recommendedPlanId,
current_state = JSON_VALUE(STATE, '$.currentValue'),
current_state_reason = JSON_VALUE(STATE, '$.reason'),
script = JSON_VALUE(details, '$.implementationDetails.script'),
estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) *
(regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000,
error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (
[query_id] INT '$.queryId',
regressedPlanId INT '$.regressedPlanId',
recommendedPlanId INT '$.recommendedPlanId',
regressedPlanErrorCount INT,
recommendedPlanErrorCount INT,
regressedPlanExecutionCount INT,
regressedPlanCpuTimeAverage FLOAT,
recommendedPlanExecutionCount INT,
recommendedPlanCpuTimeAverage FLOAT
)
)
SELECT qsq.query_id,
qsqt.query_sql_text,
dtr.*,
CAST(rp.query_plan AS XML) AS RegressedPlan,
CAST(sp.query_plan AS XML) AS SuggestedPlan
FROM cte_db_tuning_recommendations AS dtr
INNER JOIN sys.query_store_plan AS rp
ON rp.query_id = dtr.query_id
AND rp.plan_id = dtr.regressedPlanId
INNER JOIN sys.query_store_plan AS sp
ON sp.query_id = dtr.query_id
AND sp.plan_id = dtr.recommendedPlanId
INNER JOIN sys.query_store_query AS qsq
ON qsq.query_id = rp.query_id
INNER JOIN sys.query_store_query_text AS qsqt
ON qsqt.query_text_id = qsq.query_text_id;
有关可用于查询建议视图中值的 JSON 函数的详细信息,请参阅数据库引擎中的 JSON 支持 。
权限
需要SQL ServerVIEW SERVER STATE
权限。
VIEW DATABASE STATE
需要 Azure SQL 数据库中的数据库的权限。
SQL Server 2022 及更高版本的权限
要求具有对服务器的 VIEW SERVER PERFORMANCE STATE
权限。
后续步骤
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈