sys.dm_db_tuning_recommendations (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Retorna informações detalhadas sobre recomendações de ajuste automático. Para obter mais informações, consulte Ajuste automático

Para obter mais informações, consulte Monitoramento e ajuste de desempenho no Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

No Banco de Dados SQL do Azure, as exibições de gerenciamento dinâmico não podem expor informações que afetariam a contenção do banco de dados ou exporiam informações sobre outros bancos de dados aos quais o usuário tem acesso. Para evitar expor essas informações, todas as linhas que contêm dados que não pertencem ao locatário conectado são filtradas.

Nome da coluna Data type Descrição
name nvarchar(4000) Nome exclusivo da recomendação.
tipo nvarchar(4000) O nome da opção de ajuste automático que produziu a recomendação, por exemplo, FORCE_LAST_GOOD_PLAN
motivo nvarchar(4000) Motivo pelo qual essa recomendação foi fornecida.
valid_since datetime2 A primeira vez que essa recomendação foi gerada.
last_refresh datetime2 A última vez que essa recomendação foi gerada.
state nvarchar(4000) Documento JSON que descreve o estado da recomendação. Os seguintes campos estão disponíveis:
- currentValue – estado atual da recomendação.
- reason – constante que descreve por que a recomendação está no estado atual.
is_executable_action bit 1 = A recomendação pode ser executada no banco de dados por meio do script Transact-SQL.
0 = A recomendação não pode ser executada no banco de dados (por exemplo: somente informações ou recomendação revertida)
is_revertable_action bit 1 = A recomendação pode ser monitorada e revertida automaticamente pelo mecanismo de banco de dados.
0 = A recomendação não pode ser monitorada e revertida automaticamente. A maioria das ações executáveis será revertível.
execute_action_start_time datetime2 Data em que a recomendação é aplicada.
execute_action_duration time Duração da ação de execução.
execute_action_initiated_by nvarchar(4000) User = Plano forçado manualmente pelo usuário na recomendação.
System = Recomendação aplicada automaticamente pelo sistema.
execute_action_initiated_time datetime2 Data em que a recomendação foi aplicada.
revert_action_start_time datetime2 Data em que a recomendação foi revertida.
revert_action_duration time Duração da ação reverter.
revert_action_initiated_by nvarchar(4000) User = Plano recomendado não forçado manualmente pelo usuário.
System = Recomendação revertida automaticamente pelo sistema.
revert_action_initiated_time datetime2 Data em que a recomendação foi revertida.
score int Valor/efeito estimado para essa recomendação na escala de 0 a 100 (quanto maior, melhor)
details nvarchar(max) Documento JSON que contém mais detalhes sobre a recomendação. Os seguintes campos estão disponíveis:

planForceDetails
- queryId - query_id da consulta regredida.
- regressedPlanId - plan_id do plano regredido.
- regressedPlanExecutionCount - Número de execuções da consulta com plano regressado antes da regressão ser detectada.
- regressedPlanAbortedCount - Número de erros detectados durante a execução do plano regredido.
- regressedPlanCpuTimeAverage - Tempo médio da CPU (em micros segundos) consumido pela consulta regredida antes da regressão ser detectada.
- regressedPlanCpuTimeStddev - Desvio padrão do tempo de CPU consumido pela consulta regredida antes da regressão ser detectada.
- recommendedPlanId - plan_id do plano que deve ser forçado.
- recommendedPlanExecutionCount- Número de execuções da consulta com o plano que deve ser forçado antes que a regressão seja detectada.
- recommendedPlanAbortedCount - Número de erros detectados durante a execução do plano que devem ser forçados.
- recommendedPlanCpuTimeAverage - Tempo médio da CPU (em micros segundos) consumido pela consulta executada com o plano que deve ser forçado (calculado antes da regressão ser detectada).
- recommendedPlanCpuTimeStddev Desvio padrão do tempo de CPU consumido pela consulta regredida antes que a regressão seja detectada.

implementationDetails
- method - O método que deve ser usado para corrigir a regressão. O valor é sempre TSql.
- script – Script Transact-SQL que deve ser executado para forçar o plano recomendado.

Comentários

As informações retornadas por sys.dm_db_tuning_recommendations são atualizadas quando o mecanismo de banco de dados identifica uma possível regressão de desempenho de consulta e não é persistente. As recomendações são mantidas somente até que o mecanismo de banco de dados seja reiniciado. Use a coluna sqlserver_start_time em sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados. Os administradores de banco de dados devem fazer periodicamente cópias de backup da recomendação de ajuste se quiserem mantê-la após a reciclagem do servidor.

O currentValue campo na state coluna pode ter os seguintes valores:

Status Descrição
Active A recomendação está ativa e ainda não foi aplicada. O usuário pode pegar o script de recomendação e executá-lo manualmente.
Verifying A recomendação é aplicada pelo Mecanismo de Banco de Dados e o processo de verificação interna compara o desempenho do plano forçado com o plano regredido.
Success A recomendação é aplicada com êxito.
Reverted A recomendação é revertida porque não há ganhos significativos de desempenho.
Expired A recomendação expirou e não pode mais ser aplicada.

O documento JSON na state coluna contém o motivo que descreve por que é a recomendação no estado atual. Os valores no campo motivo podem ser:

Motivo Descrição
SchemaChanged A recomendação expirou porque o esquema de uma tabela referenciada foi alterado. Uma nova recomendação será criada se uma nova regressão do plano de consulta for detectada no novo esquema.
StatisticsChanged A recomendação expirou devido à alteração de estatística em uma tabela referenciada. Uma nova recomendação será criada se uma nova regressão de plano de consulta for detectada com base em novas estatísticas.
ForcingFailed O plano recomendado não pode ser forçado em uma consulta. Localize o last_force_failure_reason na exibição sys.query_store_plan para encontrar o motivo da falha.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN A opção é desabilitada pelo usuário durante o processo de verificação. Habilite FORCE_LAST_GOOD_PLAN a opção usando a instrução ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) ou force o plano manualmente usando o script na details coluna .
UnsupportedStatementType O plano não pode ser forçado na consulta. Exemplos de consultas sem suporte são cursores e INSERT BULK instrução.
LastGoodPlanForced A recomendação é aplicada com êxito.
AutomaticTuningOptionNotEnabled O Mecanismo de Banco de Dados identificou uma possível regressão de desempenho, mas a opção FORCE_LAST_GOOD_PLAN não está habilitada – consulte ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Aplique a recomendação manualmente ou habilite FORCE_LAST_GOOD_PLAN a opção .
VerificationAborted O processo de verificação é anulado devido à reinicialização ou limpeza Repositório de Consultas.
VerificationForcedQueryRecompile A consulta é recompilada porque não há nenhuma melhoria significativa no desempenho.
PlanForcedByUser O usuário forçou manualmente o plano usando sp_query_store_force_plan procedimento (Transact-SQL ). O mecanismo de banco de dados não aplicará a recomendação se o usuário decidir explicitamente forçar algum plano.
PlanUnforcedByUser O usuário cancelou manualmente o plano usando sp_query_store_unforce_plan procedimento (Transact-SQL ). Como o usuário reverteu explicitamente o plano recomendado, o mecanismo de banco de dados continuará usando o plano atual e gerará uma nova recomendação se alguma regressão de plano ocorrer no futuro.
UserForcedDifferentPlan O usuário forçou manualmente um plano diferente usando sp_query_store_force_plan procedimento (Transact-SQL ). O mecanismo de banco de dados não aplicará a recomendação se o usuário decidir explicitamente forçar algum plano.
TempTableChanged Uma tabela temporária que foi usada no plano é alterada.

As estatísticas na details coluna não mostram estatísticas do plano de runtime (por exemplo, hora atual da CPU). Os detalhes da recomendação são obtidos no momento da detecção de regressão e descrevem por que o Mecanismo de Banco de Dados identificou a regressão de desempenho. Use regressedPlanId e recommendedPlanId para consultar Repositório de Consultas exibições de catálogo para localizar estatísticas exatas do plano de runtime.

Exemplos de uso de informações de recomendações de ajuste

Exemplo 1

O código de exemplo a seguir obtém o script Transact-SQL gerado que força um bom plano para qualquer consulta específica:

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 2

A seguir, obtém o script Transact-SQL gerado que força um bom plano para qualquer consulta específica e informações adicionais sobre o ganho estimado:

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

O seguinte obtém o script Transact-SQL gerado que força um bom plano para qualquer consulta específica e informações adicionais que incluem o texto da consulta e os planos de consulta armazenados em Repositório de Consultas:

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 funções JSON que podem ser usadas para consultar valores na exibição de recomendação, consulte Suporte a JSON no Mecanismo de Banco de Dados.

Permissões

Requer VIEW SERVER STATE permissão no SQL Server.

Requer a VIEW DATABASE STATE permissão para o banco de dados no banco de dados SQL do Azure.

Permissões do SQL Server 2022 e posteriores

Requer a permissão VIEW SERVER PERFORMANCE STATE no servidor.

Próximas etapas