sys.dm_db_tuning_recommendations (Transact-SQL)

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceSQL database em Microsoft Fabric

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 SQL do Azure.

No Banco de Dados SQL do Azure, as exibições de gerenciamento dinâmico não podem expor informações que possam afetar 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	Tipo de dados	Descrição
name	Nvarchar(4000)	Nome único da recomendação.
type	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 esta recomendação foi gerada.
last_refresh	datetime2	A última vez que esta recomendação foi gerada.
state	Nvarchar(4000)	Documento JSON que descreve o estado da recomendação. Estão disponíveis os seguintes campos: - 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 de Transact-SQL script. 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 automaticamente monitorada e revertida 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 de aplicação da recomendação.
execute_action_duration	time	Duração da ação executada.
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 de aplicação da recomendação.
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 manualmente não forçado pelo usuário. System = Recomendação revertida automaticamente pelo sistema.
revert_action_initiated_time	datetime2	Data em que a recomendação foi revertida.
Pontuação	int	Valor/efeito estimado para esta recomendação na escala 0-100 (quanto maior, melhor)
detalhes	nvarchar (máx.)	Documento JSON que contém mais detalhes sobre a recomendação. Estão disponíveis os seguintes campos: 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 detetada. - regressedPlanAbortedCount - Número de erros detetados durante a execução do plano regredido. - regressedPlanCpuTimeAverage - Tempo médio de CPU (em microssegundos) consumido pela consulta regressada antes da regressão ser detetada. - regressedPlanCpuTimeStddev - Desvio padrão do tempo de CPU consumido pela consulta regressada antes que a regressão seja detetada. - 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 detetada. - recommendedPlanAbortedCount - Número de erros detetados 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 da regressão ser detetada). - recommendedPlanCpuTimeStddev Desvio padrão do tempo de CPU consumido pela consulta regressada antes que a regressão seja detetada. implementationDetails - method - O método que deve ser usado para corrigir a regressão. O valor é sempre TSql. - script - Transact-SQL script que deve ser executado para forçar o plano recomendado.

Observações

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 sqlserver_start_time coluna no 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 periódicas 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:

Situação	Description
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 sucesso.
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 pelo qual a recomendação está no estado atual. Os valores no campo razão podem ser:

Reason	Description
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 detetada 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 do plano de consulta for detetada com base em novas estatísticas.
ForcingFailed	O plano recomendado não pode ser forçado em uma consulta. Encontre o last_force_failure_reason na visualização sys.query_store_plan para encontrar o motivo da falha.
AutomaticTuningOptionDisabled	FORCE_LAST_GOOD_PLAN opção é 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 é aplicada com sucesso.
AutomaticTuningOptionNotEnabled	O Mecanismo de Banco de Dados identificou uma possível regressão de desempenho, mas a opção não está habilitada FORCE_LAST_GOOD_PLAN - consulte ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Aplique a recomendação manualmente ou ative a FORCE_LAST_GOOD_PLAN opção.
VerificationAborted	O processo de verificação é abortado devido à reinicialização ou limpeza do Repositório de Consultas.
VerificationForcedQueryRecompile	A consulta é recompilada porque não há 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 desforç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 coluna não mostram estatísticas do details plano de tempo de execução (por exemplo, o tempo atual da CPU). Os detalhes da recomendação são tomados no momento da deteção de regressão e descrevem por que o Mecanismo de Banco de Dados identificou a regressão de desempenho. Use regressedPlanId e recommendedPlanId consulte as exibições do catálogo do Repositório de Consultas para encontrar estatísticas exatas do plano de tempo de execução.

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

Exemplo 1

O código de exemplo a seguir obtém o script de 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 de 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 de 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 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 na exibição de recomendação, consulte Suporte a JSON no Mecanismo de Banco de Dados.

Permissions

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 para SQL Server 2022 e posterior

Requer VIEW SERVER PERFORMANCE STATE permissão no servidor.

Próximos passos

• 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)