sys.dm_db_tuning_recommendations (Transact-SQL)
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
Comentários
https://aka.ms/ContentUserFeedback.
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja:Submeter e ver comentários