sys.dm_db_tuning_recommendations (Transact-SQL)

项目
06/06/2023

适用于： 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 权限。