sys.dm_db_tuning_recommendations (Transact-SQL)

Aplica-se a: SQL Server 2017 (14.x) e posterior Banco de Dados SQL do Azure Instâ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 na 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 expor informações sobre outros bancos de dados aos quais o usuário tem acesso. Para evitar a exposição dessas 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
razão	nvarchar(4000)	Razão pela qual esta 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: apenas 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á reversí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 = O usuário forçou manualmente o plano 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 de reversão.
revert_action_initiated_by	nvarchar(4000)	User = Plano recomendado não forçado manualmente pelo usuário. System = O sistema reverteu automaticamente a recomendação.
revert_action_initiated_time	datetime2	Data em que a recomendação foi revertida.
score	int	Valor/efeito estimado para esta recomendação na escala de 0 a 100 (quanto maior, melhor)
details	nvarchar(max)	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 regredido antes que a regressão seja detectada. - regressedPlanAbortedCount - Número de erros detectados durante a execução do plano regredido. - regressedPlanCpuTimeAverage - Tempo médio de CPU (em microssegundos) consumido pela consulta regredida antes que a regressão seja detectada. - regressedPlanCpuTimeStddev - Desvio padrão do tempo de CPU consumido pela consulta regredida antes que a regressão seja 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 de CPU (em microssegundos) consumido pela consulta executada com o plano que deve ser forçado (calculado antes que a regressão seja 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 são persistentes. 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 cópias de backup periodicamente 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 foi 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 o motivo da recomendação no estado atual. Os valores no campo de 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 de plano de consulta for detectada no novo esquema.
StatisticsChanged	A recomendação expirou devido à alteração 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 sys.query_store_plan visualização para encontrar o motivo da falha.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN é desativada 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 foi aplicada com êxito.
AutomaticTuningOptionNotEnabled	O Mecanismo de Banco de Dados identificou uma possível regressão de desempenho, mas a FORCE_LAST_GOOD_PLAN opção 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 do Repositório de Consultas.
VerificationForcedQueryRecompile	A consulta é recompilada porque não há melhoria significativa de 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 não forçou 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 ocorrer alguma regressão de plano 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 tempo de execução (por exemplo, tempo de CPU atual). Os detalhes da recomendação são obtidos no momento da detecção da regressão e descrevem por que o Mecanismo de Banco de Dados identificou a regressão de desempenho. Use regressedPlanId e recommendedPlanId para consultar exibições de catálogo do Repositório de Consultas para encontrar 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:

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

O seguinte obtém o script Transact-SQL gerado que força um bom plano para qualquer consulta 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 e informações adicionais que incluem o texto da consulta e os planos de consulta armazenados no 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 no modo de 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

• Ajuste automático
• sys.database_automatic_tuning_options (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Suporte JSON
• sys.dm_os_sys_info (Transact-SQL)