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

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:

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:

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)