sys.dm_db_tuning_recommendations (Transact-SQL)

Применимо к: SQL Server 2017 (14.x) и более поздних версий базы данных SQLAzure Для Управляемого экземпляра SQL Azureв Microsoft Fabric

Возвращает подробные сведения о рекомендациях по автоматической настройке. Дополнительные сведения см. в разделе "Автоматическая настройка".

Замечания

Информация, возвращаемая sys.dm_db_tuning_recommendations ядром СУБД, определяет потенциальную регрессию производительности запросов и не сохраняется. Ядро СУБД сохраняет рекомендации только до перезапуска. Узнать время последнего запуска ядра СУБД можно в столбце sqlserver_start_time из sys.dm_os_sys_info. Администраторы базы данных должны периодически создавать резервные копии рекомендации по настройке, если они хотят сохранить ее после перезапуска сервера.

Поле currentValue в столбце state может иметь следующие значения:

Документ JSON в state столбце содержит причину, которая описывает, почему рекомендация находится в текущем состоянии. Значения в поле причины могут быть:

Статистика в столбце details не отображает статистику плана выполнения (например, текущее время ЦП). Сведения о рекомендации принимаются во время обнаружения регрессии и описывают, почему ядро СУБД идентифицированная регрессия производительности. Используйте 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, которые можно использовать для запроса значений в представлении рекомендаций, см. в данных JSON в SQL Server.

Разрешения

ДЛЯ SQL Server 2022 (16.x) и более ранних версий требуется VIEW SERVER STATE разрешение на SQL Server.

SQL Server 2022 (16.x) и База данных SQL Azure требуют VIEW SERVER PERFORMANCE STATE разрешения на сервере.

Связанный контент

• Автоматическая настройка
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Данные JSON в SQL Server
• sys.dm_os_sys_info (Transact-SQL)