sys.dm_exec_query_optimizer_info (Transact-SQL)

Retorna estatísticas detalhadas sobre a operação do otimizador de consulta do SQL Server. Você pode usar esta exibição ao ajustar uma carga de trabalho para identificar problemas ou melhorias na otimização de consulta. Por exemplo, você pode usar o número total de otimizações, o valor de tempo decorrido e o valor de custo final para comparar as otimizações de consulta da carga de trabalho atual e qualquer alteração observadas durante o processo de ajuste. Alguns contadores fornecem dados que só são pertinentes para uso diagnóstico interno do SQL Server. Esses contadores são marcados como "Somente interno”.

Nome

Tipo de dados

Descrição

contador

nvarchar(4000)

Nome do evento de estatísticas do otimizador.

ocorrência

bigint

Número de ocorrências do evento de otimização para este contador.

value

float

Valor de propriedade médio por ocorrência de evento.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Comentários

sys.dm_exec_query_optimizer_info contém as seguintes propriedades (contadores). Todos os valores de ocorrência são cumulativos, e são definidos em 0 na reinicialização de sistema. Todos os valores dos campos de valores são definidos em NULL, na reinicialização de sistema. Todos os valores da coluna de valor especificam um uso médio do valor de ocorrência da mesma linha como o denominador no cálculo da média. Todas as otimizações de consulta estão medidas quando o SQL Server determina mudanças em dm_exec_query_optimizer_info, incluindo as consultas do usuário e geradas pelo sistema. Execução de um plano já armazenado em cache não altera valores em dm_exec_query_optimizer_info, só otimizações são significantes.

Contador

Ocorrência

Valor

otimizações

Número total de otimizações.

Não aplicável

tempo decorrido

Número total de otimizações.

Tempo médio decorrido por otimização de uma instrução individual (consulta), em segundos.

custo final

Número total de otimizações.

Custo estimado médio para um plano otimizado em unidades de custo interno.

plano trivial

Somente interno

Somente interno

tarefas

Somente interno

Somente interno

nenhum plano

Somente interno

Somente interno

pesquisa 0

Somente interno

Somente interno

pesquise 0 vezes

Somente interno

Somente interno

pesquise 0 tarefas

Somente interno

Somente interno

pesquisa 1

Somente interno

Somente interno

pesquise 1 vez

Somente interno

Somente interno

pesquise 1 tarefa

Somente interno

Somente interno

pesquisa 2

Somente interno

Somente interno

pesquise 2 vezes

Somente interno

Somente interno

pesquise 2 tarefas

Somente interno

Somente interno

estágio de ganho 0 para estágio 1

Somente interno

Somente interno

estágio de ganho 1 para estágio 2

Somente interno

Somente interno

tempo limite

Somente interno

Somente interno

limite de memória excedido

Somente interno

Somente interno

insert stmt

Número de otimizações existentes para instruções INSERT.

Não aplicável

delete stmt

Número de otimizações existentes para instruções DELETE.

Não aplicável

update stmt

Número de otimizações existentes para instruções UPDATE.

Não aplicável

contém subconsulta

Número de otimizações para uma consulta que contém ao menos uma subconsulta.

Não aplicável

unnest falhou

Somente interno

Somente interno

tabelas

Número total de otimizações.

Calcule o número médio de tabelas referenciadas por consulta otimizada.

dicas

Número de vezes que alguma dica foi especificada. Dicas contadas incluem: dicas de consulta JOIN, GROUP, UNION e FORCE ORDER, a opção definida FORCE PLAN e dicas de associação.

Não aplicável

dica order

Número de vezes que dica de ordem de força foi especificada.

Não aplicável

dica de associação

Número de vezes que o algoritmo de junção foi forçado por uma dica de associação.

Não aplicável

exibir referência

Número de vezes que uma exibição foi referenciada em uma consulta.

Não aplicável

consulta remota

Número de otimizações em que a consulta referencia ao menos uma fonte de dados remotos, como uma tabela com um nome de quatro partes ou um resultado OPENROWSET.

Não aplicável

DOP máximo

Número total de otimizações.

Valor efetivo médio MAXDOP para um plano otimizado. Por padrão, MAXDOP efetivo é determinado pela opção de configuração do servidor max degree of parallelism, e pode ser substituído para uma consulta específica pelo valor da dica de consulta MAXDOP.

nível máximo de recursão

Número de otimizações em que um nível MAXRECURSION maior que 0 foi especificado com a dica de consulta.

Nível MAXRECURSION médio em otimizações onde um nível máximo de recursão especificado com a dica de consulta.

exibições indexadas carregadas

Somente interno

Somente interno

exibições indexadas correspondentes

Número de otimizações em que uma ou mais exibições indexadas foram correspondidas.

Número médio de exibições correspondentes.

exibições indexadas usadas

Número de otimizações em que uma ou mais exibições indexadas são usadas no plano de saída depois de correspondidas.

Número médio de exibições usadas.

exibições indexadas atualizadas

Número de otimizações de uma instrução DML que produzem um plano que mantém uma ou mais exibições indexadas.

Número médio de exibições mantidas.

solicitação de cursor dinâmico

Número de otimizações em que uma solicitação de cursor dinâmico foi especificada.

Não aplicável

solicitação de cursor de avanço rápido

Número de otimizações em que uma solicitação de cursor de avanço rápido foi especificada.

Não aplicável

merge stmt

Número de otimizações existentes para instruções MERGE.

Não aplicável

Exemplos

A. Exibindo estatísticas de execução do otimizador

Quais são as estatísticas de execução do otimizador atuais para esta instância do SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Exibindo o número total de otimizações

Quantas otimizações foram executadas?

SELECT occurrence AS Optimizations FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';

C. Tempo médio decorrido por otimização

Qual o tempo médio decorrido por otimização?

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info WHERE counter = 'elapsed time';

D. Fracionamento de otimizações que envolvem subconsultas

Que fração de consultas otimizadas continha uma subconsulta?

SELECT (SELECT CAST (occurrence AS float) FROM sys.dm_exec_query_optimizer_info WHERE counter = 'contains subquery') /
       (SELECT CAST (occurrence AS float) 
        FROM sys.dm_exec_query_optimizer_info WHERE counter = 'optimizations')
        AS ContainsSubqueryFraction;