sys.dm _ _ _ Transact- (数据库优化SQL)

适用于:SQL Server 2017 (14.x) 及更高版本 是Azure SQL 数据库

返回有关优化建议的详细信息。

在 Azure SQL 数据库 中,动态管理视图不能公开将影响数据库包含的信息,也不能公开有关用户可以访问的其他数据库的信息。 为了避免公开此信息,将筛选掉包含不属于已连接租户的数据的每一行。

列名 Data type 描述
name nvarchar(4000) 建议的唯一名称。
type nvarchar(4000) 生成了建议自动优化选项的名称,例如, FORCE_LAST_GOOD_PLAN
reason nvarchar(4000) 提供此建议的原因。
有效 _ ,因为 datetime2 首次生成此建议时。
上次 _ 刷新 datetime2 上次生成此建议的时间。
state nvarchar(4000) 描述建议状态的 JSON 文档。 以下字段可用:
- currentValue - 建议的当前状态。
- reason - 常量,描述建议为何当前状态。
可执行 _ _ 操作 bit 1 = 可以通过脚本对数据库执行 Transact-SQL 建议。
0 = 无法针对数据库数据库执行 (例如:仅信息或还原的建议)
可 _ 还原 _ 操作 bit 1 = 数据库引擎可以自动监视和还原建议。
0 = 无法自动监视和还原建议。 大多数 " 可执行 " 操作都是 " 可还原的 " 。
执行 _ 操作 _ _ 开始时间 datetime2 应用建议的日期。
执行 _ 操作 _ 持续时间 time 执行操作持续时间。
执行 _ _ 操作发起 _ 者 nvarchar(4000) User = 建议中的用户手动强制计划。
System = 系统自动应用的建议。
执行 _ 操作 _ 启动 _ 时间 datetime2 应用建议的日期。
还原 _ 操作 _ _ 开始时间 datetime2 恢复建议的日期。
还原 _ 操作 _ 持续时间 time 还原操作持续时间。
由 _ _ 启动的还原 _ 操作 nvarchar(4000) User = 用户手动取消强制的建议计划。
System = 系统自动还原建议。
还原 _ 操作 _ 启动 _ 时间 datetime2 恢复建议的日期。
score int 此建议在 0-100 刻度上的估计值/影响 (越大越好)
细节 nvarchar(max) 包含有关建议的详细信息的 JSON 文档。 以下字段可用:

planForceDetails
- queryId - _ 回归查询的查询 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 建议已过期,不再应用。

列中的 JSON 文档 state 包含描述为何处于当前状态的原因。 "原因" 字段中的值可能是:

原因 说明
SchemaChanged 建议过期,因为引用的表的架构已更改。 如果在新架构上检测到新的查询计划回归,则将创建新的建议。
StatisticsChanged 由于所引用表的统计信息发生变化,建议过期。 如果基于新统计信息检测到新的查询计划回归,则将创建新的建议。
ForcingFailed 不能对查询强制执行建议的计划。 last_force_failure_reasonsys.query_store_plan视图中找到,查找失败的原因。
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN 在验证过程中,用户禁用了选项。 FORCE_LAST_GOOD_PLAN使用ALTER DATABASE SET AUTOMATIC_TUNING (SQL)语句启用选项,或使用列中的脚本手动强制执行计划 details
UnsupportedStatementType 无法对查询强制执行计划。 不受支持的查询的示例包括游标和 INSERT BULK 语句。
LastGoodPlanForced 已成功应用建议。
AutomaticTuningOptionNotEnabled 数据库引擎已识别潜在的性能回归,但 FORCE_LAST_GOOD_PLAN 未启用选项-请参阅ALTER DATABASE SET AUTOMATIC_TUNING (transact-sql SQL)。 手动应用建议或启用 FORCE_LAST_GOOD_PLAN 选项。
VerificationAborted 由于重新启动或查询存储清理,验证过程将中止。
VerificationForcedQueryRecompile 由于没有显著的性能改进,因此重新编译查询。
PlanForcedByUser 用户使用sp_query_store_force_plan (SQL)过程手动强制计划。 如果用户显式决定强制实施某个计划,则数据库引擎将不应用建议。
PlanUnforcedByUser 用户使用sp_query_store_unforce_plan (SQL transact-sql)过程手动 unforced 计划。 由于用户显式还原了推荐的计划,数据库引擎将继续使用当前计划并生成新的建议(如果将来出现某种计划回归)。
UserForcedDifferentPlan 用户使用sp_query_store_force_plan (SQL)过程手动强制实施不同计划。 如果用户显式决定强制实施某个计划,则数据库引擎将不应用建议。
TempTableChanged 在计划中使用的临时表已更改。

列中的统计信息 details 不显示运行时计划统计信息 (例如,当前 CPU 时间) 。 建议的详细信息是在回归检测时进行的,并描述为何 数据库引擎 发现性能回归。 使用 regressedPlanIdrecommendedPlanId 查询 查询存储目录视图 以查找确切的运行时计划统计信息。

使用优化建议信息的示例

示例 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 支持 数据库引擎 。

权限

需要 VIEW SERVER STATE 中的权限 SQL Server 。
需要 VIEW DATABASE STATE 中数据库的权限 Azure SQL 数据库 。

另请参阅

自动优化
(的 sys.database_automatic_tuning_options SQL)
(的 sys.database_query_store_options SQL)
(sys.dm_os_sys_info 的 JSON 支持 SQL)