sys.dm_db_tuning_recommendations (Transact-SQL)

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

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

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

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

Имя столбца	Тип данных	Description
name	nvarchar(4000)	Уникальное имя рекомендации.
type	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 на сервере.

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

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