sys.DM_db_ajuste_recomendações (Transact-SQL)sys.dm_db_tuning_recommendations (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Retorna informações detalhadas sobre recomendações de ajuste.Returns detailed information about tuning recommendations.

No Banco de dados SQL do AzureAzure SQL Database, as exibições de gerenciamento dinâmico não podem expor informações que afetarão a contenção do banco de dados ou informações sobre outros bancos de dados aos quais o usuário tem acesso.In Banco de dados SQL do 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. Para evitar a exposição dessas informações, cada linha que contém dados que não pertencem ao locatário conectado será filtrada.To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.

Nome da colunaColumn name Data typeData type DescriçãoDescription
namename nvarchar(4000)nvarchar(4000) Nome exclusivo da recomendação.Unique name of recommendation.
typetype nvarchar(4000)nvarchar(4000) O nome da opção de ajuste automático que produziu a recomendação, por exemplo, 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) Motivo por que essa recomendação foi fornecida.Reason why this recommendation was provided.
valid_sincevalid_since datetime2datetime2 Na primeira vez em que essa recomendação foi gerada.The first time this recommendation was generated.
última_atualizarlast_refresh datetime2datetime2 A última vez em que essa recomendação foi gerada.The last time this recommendation was generated.
statestate nvarchar(4000)nvarchar(4000) Documento JSON que descreve o estado da recomendação.JSON document that describes the state of the recommendation. Campos a seguir estão disponíveis:Following fields are available:
- currentValue -estado atual da recomendação.- currentValue - current state of the recommendation.
- reason -constante que descreve por que a recomendação está no estado atual.- reason - constant that describes why the recommendation is in the current state.
está_executável_açãois_executable_action bitbit 1 = a recomendação pode ser executada no banco de dados por meio de Transact-SQLTransact-SQL script.1 = The recommendation can be executed against the database via Transact-SQLTransact-SQL script.
0 = a recomendação não pode ser executada no banco de dados (por exemplo: recomendação de apenas ou revertido de informações)0 = The recommendation cannot be executed against the database (for example: information only or reverted recommendation)
está_revertable_açãois_revertable_action bitbit 1 = a recomendação pode ser monitorada e revertida pelo mecanismo de banco de dados automaticamente.1 = The recommendation can be automatically monitored and reverted by Database engine.
0 = a recomendação não pode ser monitorada e revertida automaticamente.0 = The recommendation cannot be automatically monitored and reverted. A maioria dos "executável" ações serão "revertable".Most "executable" actions will be "revertable".
Execute_ação_iniciar_tempoexecute_action_start_time datetime2datetime2 Data em que a recomendação é aplicada.Date the recommendation is applied.
Execute_ação_duraçãoexecute_action_duration timetime Duração da ação executar.Duration of the execute action.
Execute_ação_iniciado_porexecute_action_initiated_by nvarchar(4000)nvarchar(4000) User = Usuário forçados manualmente o plano na recomendação.User = User manually forced plan in the recommendation.
System = Sistema aplicada automaticamente a recomendação.System = System automatically applied recommendation.
Execute_ação_iniciado_tempoexecute_action_initiated_time datetime2datetime2 Data em que a recomendação foi aplicada.Date the recommendation was applied.
Reverter_ação_iniciar_temporevert_action_start_time datetime2datetime2 Data em que a recomendação foi revertida.Date the recommendation was reverted.
revert_action_durationrevert_action_duration timetime Duração da ação de reversão.Duration of the revert action.
Reverter_ação_iniciado_porrevert_action_initiated_by nvarchar(4000)nvarchar(4000) User = Plano recomendado manualmente de usuário.User = User manually unforced recommended plan.
System = O sistema automaticamente revertida recomendação.System = System automatically reverted recommendation.
Reverter_ação_iniciado_temporevert_action_initiated_time datetime2datetime2 Data em que a recomendação foi revertida.Date the recommendation was reverted.
scorescore intint Estimado de valor/impacto dessa recomendação sobre o valor de 0 a 100 escala (quanto maior o melhor)Estimated value/impact for this recommendation on the 0-100 scale (the larger the better)
Detalhesdetails nvarchar(max)nvarchar(max) Documento JSON que contém mais detalhes sobre a recomendação.JSON document that contains more details about the recommendation. Campos a seguir estão disponíveis:Following fields are available:

planForceDetails
- queryId -consulta_id da consulta regredida.- queryId - query_id of the regressed query.
- regressedPlanId -plan_id do plano regredido.- regressedPlanId - plan_id of the regressed plan.
- regressedPlanExecutionCount – O número de execuções da consulta com o plano regredido antes da regressão é detectado.- regressedPlanExecutionCount - Number of executions of the query with regressed plan before the regression is detected.
- regressedPlanAbortedCount – O número de erros detectados durante a execução do plano retornado.- regressedPlanAbortedCount - Number of detected errors during the execution of the regressed plan.
- regressedPlanCpuTimeAverage -Tempo médio de CPU consumido pela consulta retornada antes que a regressão é detectada.- regressedPlanCpuTimeAverage - Average CPU time consumed by the regressed query before the regression is detected.
- regressedPlanCpuTimeStddev -Desvio padrão de tempo de CPU consumido pela consulta retornada antes da regressão é detectado.- regressedPlanCpuTimeStddev - Standard deviation of CPU time consumed by the regressed query before the regression is detected.
- recommendedPlanId -plan_id do plano que deve ser forçado.- recommendedPlanId - plan_id of the plan that should be forced.
- recommendedPlanExecutionCount– O número de execuções da consulta com o plano que deve ser forçado antes que a regressão é detectada.- recommendedPlanExecutionCount- Number of executions of the query with the plan that should be forced before the regression is detected.
- recommendedPlanAbortedCount – O número de erros detectados durante a execução do plano que deve ser forçado.- recommendedPlanAbortedCount - Number of detected errors during the execution of the plan that should be forced.
- recommendedPlanCpuTimeAverage -Tempo médio de CPU consumido pela consulta executada com o plano deve ser forçado (calculado antes que a regressão é detectada).- recommendedPlanCpuTimeAverage - Average CPU time consumed by the query executed with the plan that should be forced (calculated before the regression is detected).
- recommendedPlanCpuTimeStddev Desvio padrão de tempo de CPU consumido pela consulta retornada antes da regressão é detectado.- recommendedPlanCpuTimeStddev Standard deviation of CPU time consumed by the regressed query before the regression is detected.

implementationDetails
- method -O método que deve ser usado para corrigir a regressão.- method - The method that should be used to correct the regression. Valor é sempre TSql.Value is always TSql.
- script - Transact-SQLTransact-SQL script que deve ser executado para forçar o plano recomendado.- script - Transact-SQLTransact-SQL script that should be executed to force the recommended plan.

ComentáriosRemarks

Informações retornadas por sys.dm_db_tuning_recommendations é atualizada quando o mecanismo de banco de dados identifica potenciais regressão de desempenho de consulta e não é persistente.Information returned by sys.dm_db_tuning_recommendations is updated when database engine identifies potential query performance regression, and is not persisted. As recomendações são mantidas apenas até SQL ServerSQL Server for reiniciado.Recommendations are kept only until SQL ServerSQL Server is restarted. Os administradores de banco de dados devem periodicamente gerar cópias de backup de recomendação de ajuste se quiserem mantê-lo após a reciclagem do servidor.Database administrators should periodically make backup copies of the tuning recommendation if they want to keep it after server recycling.

currentValue campo de state coluna pode ter os seguintes valores:currentValue field in the state column might have the following values:

StatusStatus DescriçãoDescription
Active Recomendação estiver ativo e aplicada não ainda.Recommendation is active and not yet applied. Usuário pode tirar o script de recomendação e executá-lo manualmente.User can take recommendation script and execute it manually.
Verifying A recomendação é aplicada por Mecanismo de Banco de DadosDatabase Engine e o processo de verificação interno compara o desempenho do plano forçado com o plano regredido.Recommendation is applied by Mecanismo de Banco de DadosDatabase Engine and internal verification process compares performance of the forced plan with the regressed plan.
Success A recomendação é aplicada com êxito.Recommendation is successfully applied.
Reverted A recomendação é revertida porque não há nenhum ganhos significativos de desempenho.Recommendation is reverted because there are no significant performance gains.
Expired Recomendação expirou e não pode ser aplicada mais.Recommendation has expired and cannot be applied anymore.

Documento JSON na state coluna contém o motivo que descreve por que é a recomendação no estado atual.JSON document in state column contains the reason that describes why is the recommendation in the current state. Valores do campo motivo podem ser:Values in the reason field might be:

ReasonReason DescriçãoDescription
SchemaChanged Recomendação expirou porque o esquema de uma tabela referenciado é alterado.Recommendation expired because the schema of a referenced table is changed.
StatisticsChanged Recomendação expirou devido à alteração de estatística em uma tabela referenciada.Recommendation expired due to the statistic change on a referenced table.
ForcingFailed Plano recomendado não pode ser forçado em uma consulta.Recommended plan cannot be forced on a query. Localizar o last_force_failure_reason no query_store_plan exibição para descobrir o motivo da falha.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 opção está desabilitada pelo usuário durante o processo de verificação.FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Habilitar FORCE_LAST_GOOD_PLAN usando a opção ALTER AUTOMATIC_TUNING definido do banco de dados (Transact-SQL) instrução ou forçar o plano manualmente usando o script na [details] coluna.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 Não é possível forçar o plano na consulta.Plan cannot be forced on the query. Exemplos de consultas sem suporte são cursores e INSERT BULK instrução.Examples of unsupported queries are cursors and INSERT BULK statement.
LastGoodPlanForced A recomendação é aplicada com êxito.Recommendation is successfully applied.
AutomaticTuningOptionNotEnabled Mecanismo de Banco de DadosDatabase Engine identificado regressão de desempenho potencial, mas o FORCE_LAST_GOOD_PLAN opção não está habilitada – veja ALTER AUTOMATIC_TUNING definido do banco de dados (Transact-SQL).identified potential performance regression, but the FORCE_LAST_GOOD_PLAN option is not enabled - see ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Aplicar recomendação manualmente ou habilitar FORCE_LAST_GOOD_PLAN opção.Apply recommendation manually or enable FORCE_LAST_GOOD_PLAN option.
VerificationAborted Processo de verificação foi anulado devido à reinicialização ou do limpeza de Store de consulta.Verification process is aborted due to the restart or Query Store cleanup.
VerificationForcedQueryRecompile Consulta é recompilada porque não há nenhuma melhoria de desempenho significativa.Query is recompiled because there is no significant performance improvement.
PlanForcedByUser Usuário forçado manualmente usando o plano sp_query_store_force_plan (Transact-SQL) procedimento.User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure.
PlanUnforcedByUser Usuário não manualmente forçados plano usando sp_query_store_unforce_plan (Transact-SQL) procedimento.User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure.

Estatística da coluna de detalhes não mostram as estatísticas do plano de tempo de execução (por exemplo, tempo de CPU atual).Statistic in the details column do not show runtime plan statistics (for example, current CPU time). Os detalhes de recomendação são obtidos no momento da detecção de regressão e descrever porque Mecanismo de Banco de DadosDatabase Engine identificado regressão de desempenho.The recommendation details are taken at the time of regression detection and describe why Mecanismo de Banco de DadosDatabase Engine identified performance regression. Use regressedPlanId e recommendedPlanId para consultar modos de exibição de catálogo de Store de consulta para localizar as estatísticas de plano de execução exato.Use regressedPlanId and recommendedPlanId to query Query Store catalog views to find exact runtime plan statistics.

Exemplos de como usar informações de recomendações de ajusteExamples of using tuning recommendations information

Exemplo 1Example 1

A seguir obtém o gerado Transact-SQLTransact-SQL script que força um bom plano para qualquer consulta: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';

Exemplo 2Example 2

A seguir obtém o gerado Transact-SQLTransact-SQL script que força um bom plano para qualquer determinada consulta e informações adicionais sobre o ganho estimado: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;

Exemplo 3:Example 3

A seguir obtém o gerado Transact-SQLTransact-SQL script que força um bom plano para qualquer determinada consulta e informações adicionais que incluem o texto da consulta e os planos de consulta armazenados no Query Store: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;

Para obter mais informações sobre as funções JSON que pode ser usado para valores de consulta no modo de exibição de recomendação, consulte suporte a JSON em Mecanismo de Banco de DadosDatabase Engine.For more information about JSON functions that can be used to query values in the recommendation view, see JSON Support in Mecanismo de Banco de DadosDatabase Engine.

PermissõesPermissions

Requer VIEW SERVER STATE permissão no SQL ServerSQL Server.Requires VIEW SERVER STATE permission in SQL ServerSQL Server.
Requer o VIEW DATABASE STATE permissão para o banco de dados Banco de dados SQL do AzureAzure SQL Database.Requires the VIEW DATABASE STATE permission for the database in Banco de dados SQL do AzureAzure SQL Database.

Consulte tambémSee Also

O ajuste automático 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)
Suporte a JSONJSON Support