sys.dm_db_tuning_recommendations (Transact-SQL)

项目
07/05/2024

适用于：SQL Server 2017 (14.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

返回有关自动优化建议的详细信息。有关详细信息，请参阅自动优化

有关详细信息，请参阅Azure SQL 数据库和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 刻度（越大越好）的估计值/效果
详细信息	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 文档包含描述当前状态下建议的原因。原因字段中的值可能是：

Reason	说明
`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 支持。

权限

VIEW SERVER STATE需要 SQL Server 中的权限。

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

SQL Server 2022 及更高版本的权限

要求具有对服务器的 VIEW SERVER PERFORMANCE STATE 权限。

通过