_рекомендации по настройке sys.DM DB _ _ (Transact-SQL)sys.dm_db_tuning_recommendations (Transact-SQL)

Применимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) и более поздние версии ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL DatabaseПрименимо к:Applies to: даSQL Server 2017 (14.x);SQL Server 2017 (14.x)yesSQL Server 2017 (14.x);SQL Server 2017 (14.x) and later ДаБаза данных SQL AzureAzure SQL DatabaseYesБаза данных SQL AzureAzure SQL Database

Возвращает подробные сведения о рекомендациях по настройке.Returns detailed information about tuning recommendations.

Динамические административные представления в среде База данных SQL AzureAzure SQL Database не могут предоставлять информацию, которая может повлиять на автономность базы данных, или информацию о других базах данных, к которым имеет доступ пользователь.In База данных SQL AzureAzure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. Во избежание раскрытия этой информации все строки, содержащие данные, не принадлежащие подключенному клиенту, отфильтровываются.To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

Имя столбцаColumn name Data typeData type ОписаниеDescription
namename nvarchar(4000)nvarchar(4000) Уникальное имя рекомендации.Unique name of recommendation.
typetype nvarchar(4000)nvarchar(4000) Имя параметра автоматической настройки, который создал рекомендацию, напримерFORCE_LAST_GOOD_PLANThe name of the automatic tuning option that produced the recommendation, for example, FORCE_LAST_GOOD_PLAN
reasonreason nvarchar(4000)nvarchar(4000) Причина, по которой была предоставлена эта рекомендация.Reason why this recommendation was provided.
действительно _ сvalid_since datetime2datetime2 При первом создании этой рекомендации.The first time this recommendation was generated.
Последнее _ обновлениеlast_refresh datetime2datetime2 Время последнего создания этой рекомендации.The last time this recommendation was generated.
statestate nvarchar(4000)nvarchar(4000) Документ JSON, описывающий состояние рекомендации.JSON document that describes the state of the recommendation. Доступны следующие поля:Following fields are available:
- currentValue— Текущее состояние рекомендации.- currentValue - current state of the recommendation.
- reason— Константа, описывающая причину, по которой рекомендация находится в текущем состоянии.- reason - constant that describes why the recommendation is in the current state.
_исполняемое _ действиеis_executable_action bitbit 1 = рекомендацию можно выполнить в базе данных с помощью Transact-SQLTransact-SQL скрипта.1 = The recommendation can be executed against the database via Transact-SQLTransact-SQL script.
0 = рекомендация не может быть выполнена для базы данных (например, только сведения или отмененная рекомендация).0 = The recommendation cannot be executed against the database (for example: information only or reverted recommendation)
является действием, которое может быть _ отменено _is_revertable_action bitbit 1 = рекомендация может автоматически отслеживаться и возвращаться ядром СУБД.1 = The recommendation can be automatically monitored and reverted by Database engine.
0 = рекомендация не может быть автоматически отслеживаться и отменена.0 = The recommendation cannot be automatically monitored and reverted. Большинство " исполняемых " действий будут " восстановлены " .Most "executable" actions will be "revertable".
_ _ время начала действия _ при выполненииexecute_action_start_time datetime2datetime2 Дата применения рекомендации.Date the recommendation is applied.
_Длительность выполнения действия _execute_action_duration timetime Длительность действия выполнения.Duration of the execute action.
выполнить _ действие _ , инициированное _execute_action_initiated_by nvarchar(4000)nvarchar(4000) User= Пользователь вручную принудительно запланировать рекомендации.User = User manually forced plan in the recommendation.
System= Система автоматически применила рекомендацию.System = System automatically applied recommendation.
время выполнения, _ _ инициированное действием _execute_action_initiated_time datetime2datetime2 Дата применения рекомендации.Date the recommendation was applied.
_ _ время начала отката действия _revert_action_start_time datetime2datetime2 Дата отката рекомендации.Date the recommendation was reverted.
отменить _ _ Длительность действияrevert_action_duration timetime Длительность действия отката.Duration of the revert action.
отменить _ действие _ , инициированное _revert_action_initiated_by nvarchar(4000)nvarchar(4000) User= Пользователь вручную не рекомендовал Рекомендуемый план.User = User manually unforced recommended plan.
System= Система автоматически отменяет рекомендацию.System = System automatically reverted recommendation.
_время отмены _ инициации действия _revert_action_initiated_time datetime2datetime2 Дата отката рекомендации.Date the recommendation was reverted.
понятьscore intint Предполагаемое значение и влияние на эту рекомендацию по шкале 0-100 (чем больше, тем выше)Estimated value/impact for this recommendation on the 0-100 scale (the larger the better)
Дополнительноdetails nvarchar(max)nvarchar(max) Документ JSON, содержащий дополнительные сведения о рекомендации.JSON document that contains more details about the recommendation. Доступны следующие поля:Following fields are available:

planForceDetails
- queryId— _ идентификатор запроса регрессионного запроса.- queryId - query_id of the regressed query.
- regressedPlanId— plan_id регрессионного плана.- regressedPlanId - plan_id of the regressed plan.
- regressedPlanExecutionCount— Число выполнений запроса с регрессионным планом до обнаружения регрессии.- regressedPlanExecutionCount - Number of executions of the query with regressed plan before the regression is detected.
- regressedPlanAbortedCount— Число обнаруженных ошибок во время выполнения регрессивного плана.- regressedPlanAbortedCount - Number of detected errors during the execution of the regressed plan.
- regressedPlanCpuTimeAverage— Среднее время ЦП (в микросекундах), затраченное на регрессионный запрос до обнаружения регрессии.- regressedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the regressed query before the regression is detected.
- regressedPlanCpuTimeStddev— Стандартное отклонение времени ЦП, потребляемого регрессионным запросом до обнаружения регрессии.- regressedPlanCpuTimeStddev - Standard deviation of CPU time consumed by the regressed query before the regression is detected.
- recommendedPlanId— plan_id плана, который должен быть принудительно вынужден.- recommendedPlanId - plan_id of the plan that should be forced.
- recommendedPlanExecutionCount— Число выполнений запроса с планом, который должен быть принудительно завершен до обнаружения регрессии.- recommendedPlanExecutionCount- Number of executions of the query with the plan that should be forced before the regression is detected.
- recommendedPlanAbortedCount— Число обнаруженных ошибок во время выполнения плана, который должен быть принудительно выполнен.- recommendedPlanAbortedCount - Number of detected errors during the execution of the plan that should be forced.
- recommendedPlanCpuTimeAverageСреднее время ЦП (в микросекундах), затраченное на выполнение запроса с планом, который должен быть принудительно завершен (вычислено до обнаружения регрессии).- recommendedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the query executed with the plan that should be forced (calculated before the regression is detected).
- recommendedPlanCpuTimeStddevСтандартное отклонение времени ЦП, потребляемого регрессионным запросом до обнаружения регрессии.- recommendedPlanCpuTimeStddev Standard deviation of CPU time consumed by the regressed query before the regression is detected.

implementationDetails
- method— Метод, который должен использоваться для исправления регрессии.- method - The method that should be used to correct the regression. Значение всегда равно TSql .Value is always TSql.
- script - Transact-SQLTransact-SQLСкрипт, который должен быть выполнен для принудительного применения рекомендуемого плана.- script - Transact-SQLTransact-SQL script that should be executed to force the recommended plan.

ПримечанияRemarks

Информация, возвращаемая, sys.dm_db_tuning_recommendations обновляется, когда ядро СУБД определяет потенциальную регрессию производительности запросов и не сохраняется.Information returned by sys.dm_db_tuning_recommendations is updated when database engine identifies potential query performance regression, and is not persisted. Рекомендации сохраняются только до SQL ServerSQL Server перезапуска.Recommendations are kept only until SQL ServerSQL Server is restarted. Администраторы баз данных должны периодически создавать резервные копии рекомендаций по настройке, если они хотят обеспечить их работу после повторного использования сервера.Database administrators should periodically make backup copies of the tuning recommendation if they want to keep it after server recycling.

currentValueполе в state столбце может иметь следующие значения:currentValue field in the state column might have the following values:

СостояниеStatus ОписаниеDescription
Active Рекомендация активна и еще не применена.Recommendation is active and not yet applied. Пользователь может принять сценарий рекомендаций и выполнить его вручную.User can take recommendation script and execute it manually.
Verifying Рекомендация применяется, Компонент Database EngineDatabase Engine а внутренний процесс проверки сравнивает производительность принудительного плана с регрессивным планом.Recommendation is applied by Компонент Database EngineDatabase Engine and internal verification process compares performance of the forced plan with the regressed plan.
Success Рекомендация успешно применена.Recommendation is successfully applied.
Reverted Рекомендация отменена, так как нет значительного выигрыша в производительности.Recommendation is reverted because there are no significant performance gains.
Expired Срок действия рекомендации истек, и он больше не может быть применен.Recommendation has expired and cannot be applied anymore.

Документ JSON в state столбце содержит причину, которая описывает, почему является рекомендацией в текущем состоянии.JSON document in state column contains the reason that describes why is the recommendation in the current state. Значения в поле Причина могут быть:Values in the reason field might be:

ПричинаReason ОписаниеDescription
SchemaChanged Срок действия рекомендации истек из-за изменения схемы ссылочной таблицы.Recommendation expired because the schema of a referenced table is changed. Новая рекомендация будет создана, если в новой схеме обнаружена новая регрессия плана запроса.New recommendation will be created if a new query plan regression is detected on the new schema.
StatisticsChanged Срок действия рекомендации истек из-за изменения статистики в связанной таблице.Recommendation expired due to the statistic change on a referenced table. Новая рекомендация будет создана, если будет обнаружена новая регрессия плана запроса на основе новой статистики.New recommendation will be created if a new query plan regression is detected based on new statistics.
ForcingFailed Рекомендуемый план не может быть принудительно применен к запросу.Recommended plan cannot be forced on a query. Найдите last_force_failure_reason в представлении sys. query_store_plan , чтобы узнать причину сбоя.Find the last_force_failure_reason in the sys.query_store_plan view to find the reason of the failure.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLANпараметр отключен пользователем в процессе проверки.FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Включите FORCE_LAST_GOOD_PLAN параметр, используя инструкцию ALTER database SET AUTOMATIC_TUNING (инструкции TRANSACT-SQL) или принудительный запуск плана вручную с помощью скрипта в [details] столбце.Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement or force the plan manually using the script in [details] column.
UnsupportedStatementType Невозможно принудительно применить план к запросу.Plan cannot be forced on the query. Примерами неподдерживаемых запросов являются курсоры и INSERT BULK операторы.Examples of unsupported queries are cursors and INSERT BULK statement.
LastGoodPlanForced Рекомендация успешно применена.Recommendation is successfully applied.
AutomaticTuningOptionNotEnabled Компонент Database EngineDatabase Engineобнаружена потенциальная регрессия производительности, но FORCE_LAST_GOOD_PLAN параметр не включен — см. раздел ALTER database SET AUTOMATIC_TUNING (TRANSACT-SQL).identified potential performance regression, but the FORCE_LAST_GOOD_PLAN option is not enabled - see ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Примените рекомендацию вручную или включите FORCE_LAST_GOOD_PLAN параметр.Apply recommendation manually or enable FORCE_LAST_GOOD_PLAN option.
VerificationAborted Процесс проверки прерван из-за перезапуска или очистки хранилища запросов.Verification process is aborted due to the restart or Query Store cleanup.
VerificationForcedQueryRecompile Запрос перекомпилируется, так как нет существенного улучшения производительности.Query is recompiled because there is no significant performance improvement.
PlanForcedByUser Пользователь вручную принудительно затребовал план с помощью sp_query_store_force_plan (процедуры)Transact-SQL .User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure. Ядро СУБД не будет применять рекомендацию, если пользователь явно решил принудительно применить некоторый план.Database engine will not apply the recommendation if user explicitly decided to force some plan.
PlanUnforcedByUser Пользователь вручную не принудительно вынудить план с помощью sp_query_store_unforce_plan (процедуры)Transact-SQL .User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure. Так как пользователь явно отменяет Рекомендуемый план, ядро СУБД продолжает использовать текущий план и создает новую рекомендацию, если в будущем происходит некоторое снижение плана.Since the user explicitly reverted the recommended plan, database engine will keep using the current plan and generate a new recommendation if some plan regression occurs in future.

Статистика в столбце сведений не показывает статистику плана времени выполнения (например, текущее время ЦП).Statistic in the details column do not show runtime plan statistics (for example, current CPU time). Сведения о рекомендациях выполняются во время обнаружения регрессии и описываются причины Компонент Database EngineDatabase Engine снижения производительности.The recommendation details are taken at the time of regression detection and describe why Компонент Database EngineDatabase Engine identified performance regression. Используйте regressedPlanId и recommendedPlanId для запроса представлений каталога хранилища запросов , чтобы найти точную статистику плана времени выполнения.Use regressedPlanId and recommendedPlanId to query Query Store catalog views to find exact runtime plan statistics.

Примеры использования сведений о рекомендациях по настройкеExamples of using tuning recommendations information

Пример 1Example 1

Следующий пример получает созданный Transact-SQLTransact-SQL скрипт, который вызывает хороший план для любого заданного запроса:The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query:

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';

Пример 2Example 2

Следующий пример получает созданный Transact-SQLTransact-SQL скрипт, который принудительно вызывает хороший план для любого заданного запроса и дополнительные сведения о предполагаемом выигрыше:The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query and additional information about the estimated gain:

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;

Пример 3Example 3

Следующий пример получает созданный Transact-SQLTransact-SQL скрипт, который принудительно вызывает хороший план для любого заданного запроса и дополнительные сведения, включающие текст запроса и планы запросов, хранимые в хранилище запросов:The following gets the generated Transact-SQLTransact-SQL script that forces a good plan for any given query and additional information that includes the query text and the query plans stored in Query Store:

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 в Компонент Database EngineDatabase Engine .For more information about JSON functions that can be used to query values in the recommendation view, see JSON Support in Компонент Database EngineDatabase Engine.

РазрешенияPermissions

Требуется VIEW SERVER STATE разрешение в SQL ServerSQL Server .Requires VIEW SERVER STATE permission in SQL ServerSQL Server.
Требуется VIEW DATABASE STATE разрешение для базы данных в База данных SQL AzureAzure SQL Database .Requires the VIEW DATABASE STATE permission for the database in База данных SQL AzureAzure SQL Database.

См. такжеSee Also

Автоматическая настройка Automatic Tuning
sys. database_automatic_tuning_options (Transact-SQL) sys.database_automatic_tuning_options (Transact-SQL)
sys. database_query_store_options (Transact-SQL) sys.database_query_store_options (Transact-SQL)
Поддержка JSONJSON Support