sys.dm_db_tuning_recommendations (Transact-SQL)

Область применения: SQL Server 2017 (14.x) и более поздних версий База данных SQL Azure Управляемый экземпляр SQL Azure

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

Замечания

Информация, возвращаемая обновлением 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] 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 разрешение для базы данных в База данных SQL Azure.

Разрешения для SQL Server 2022 и более поздних версий

Необходимо разрешение VIEW SERVER PERFORMANCE STATE на сервере.

Следующие шаги

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