sys.dm_db_tuning_recommendations（Transact-SQL）

适用于： Microsoft Fabric 中的 SQL Server 2017 （14.x） 及更高版本 Azure SQL 数据库Azure SQL 托管实例SQL 数据库

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

注解

当数据库引擎标识潜在的查询性能回归且不会持久保存时，将更新返回 sys.dm_db_tuning_recommendations 的信息。 数据库引擎仅在重新启动之前保留建议。 使用 sqlserver_start_time 中的 列查找上次数据库引擎启动时间。 如果数据库管理员希望在服务器回收后保留它，数据库管理员应定期创建优化建议的备份副本。

currentValue列中的state字段可能具有以下值：

列中的 state JSON 文档包含描述建议处于当前状态的原因。 原因字段中的值可能是：

列中的 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] BIGINT '$.queryId',
    regressed_plan_id BIGINT '$.regressedPlanId',
    last_good_plan_id BIGINT '$.recommendedPlanId'
) AS details
WHERE JSON_VALUE(STATE, '$.currentValue') = 'Active';

示例 2

以下查询获取生成的 Transact-SQL 脚本，该脚本强制为任何给定查询提供良好的计划，以及有关估计增益的其他信息：

SELECT reason,
       score,
       JSON_VALUE(details, '$.implementationDetails.script') AS script,
       planForceDetails.*,
       (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000 AS estimated_gain,
       IIF (regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO') AS error_prone
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (details, '$.planForceDetails') WITH (
    [query_id] BIGINT '$.queryId',
    regressedPlanId BIGINT '$.regressedPlanId',
    recommendedPlanId BIGINT '$.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,
       JSON_VALUE(STATE, '$.currentValue') AS current_state,
       JSON_VALUE(STATE, '$.reason') AS current_state_reason,
       JSON_VALUE(details, '$.implementationDetails.script') AS script,
       (regressedPlanExecutionCount + recommendedPlanExecutionCount) * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage) / 1000000 AS estimated_gain,
       IIF (regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO') AS error_prone
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (details, '$.planForceDetails') WITH (
    [query_id] BIGINT '$.queryId',
    regressedPlanId BIGINT '$.regressedPlanId',
    recommendedPlanId BIGINT '$.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 函数的详细信息，请参阅 SQL Server 中的 JSON 数据。

权限

SQL Server 2022 （16.x） 和早期版本需要 VIEW SERVER STATE SQL Server 中的权限。

SQL Server 2022 （16.x） 和 Azure SQL 数据库需要 VIEW SERVER PERFORMANCE STATE 对服务器具有权限。

相关内容

• 自动优化
• sys.database_automatic_tuning_options（Transact-SQL）
• sys.database_query_store_options（Transact-SQL）
• SQL Server 中的 JSON 数据
• sys.dm_os_sys_info（Transact-SQL）