sys.dm_db_tuning_recommendations (Transact-SQL)

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

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

Дополнительные сведения см. в разделе "Мониторинг и настройка производительности" в Базе данных SQL Azure и Управляемом экземпляре SQL Azure.

В Базе данных SQL Azure динамические административные представления не могут предоставлять сведения, влияющие на доступность базы данных или предоставление сведений о других базах данных, к которым у пользователя есть доступ. Чтобы избежать предоставления этой информации, отфильтровывается каждая строка, содержащая данные, которые не принадлежат подключену клиенту.

Имя столбца Тип данных Описание
name nvarchar(4000) Уникальное имя рекомендации.
тип nvarchar(4000) Имя параметра автоматической настройки, создающего рекомендацию, например. FORCE_LAST_GOOD_PLAN
Причина 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 — Среднее время ЦП (в микро секундах), потребляемое регрессией запроса до обнаружения регрессии.
- regressedPlanCpuTimeStddev — Стандартное отклонение ЦП, затраченное регрессией запроса до обнаружения регрессии.
- recommendedPlanId - plan_id плана, который должен быть вынужден.
- recommendedPlanExecutionCount— Количество выполнений запроса с планом, который должен быть вынужден до обнаружения регрессии.
- recommendedPlanAbortedCount — количество обнаруженных ошибок во время выполнения плана, которое должно быть принудительно.
- recommendedPlanCpuTimeAverage — среднее время ЦП (в микро секундах), используемое запросом, выполняемым с планом, который должен быть вынужден (вычисляется до обнаружения регрессии).
- recommendedPlanCpuTimeStddev Стандартное отклонение ЦП, затраченное регрессией запроса до обнаружения регрессии.

implementationDetails
- method — метод, который следует использовать для исправления регрессии. Значение всегда TSqlравно.
- script — Скрипт Transact-SQL, который должен быть выполнен для принудительного принудительного выполнения рекомендуемого плана.

Замечания

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

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

Состояние Description
Active Рекомендация активна и еще не применяется. Пользователь может выполнять скрипт рекомендаций и выполнять его вручную.
Verifying Рекомендации применяются ядром СУБД и внутренним процессом проверки сравнивают производительность принудительного плана с регрессивным планом.
Success Рекомендация успешно применяется.
Reverted Рекомендация отменена, так как нет значительных показателей производительности.
Expired Срок действия рекомендации истек и больше не может применяться.

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

Причина Description
SchemaChanged Срок действия рекомендации истек, так как схема указанной таблицы изменена. При обнаружении регрессии нового плана запроса на новую схему будет создана новая рекомендация.
StatisticsChanged Срок действия рекомендации истек из-за изменения статистики в указанной таблице. Новая рекомендация будет создана, если обнаружена регрессия нового плана запроса на основе новой статистики.
ForcingFailed Рекомендуемый план нельзя принудительно принудить к запросу. last_force_failure_reason Найдите в представлении sys.query_store_plan причину сбоя.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN параметр отключен пользователем во время проверки. Включение FORCE_LAST_GOOD_PLAN параметра с помощью инструкции ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) или принудительного выполнения плана вручную с помощью скрипта в столбце 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 не отображает статистику плана выполнения (например, текущее время ЦП). Сведения о рекомендации принимаются во время обнаружения регрессии и описывают, почему ядро СУБД определило регрессию производительности. Используйте 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 на сервере.

Далее