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 quaisquer mudanças observadas durante o processo de ajuste. Alguns contadores fornecem dados que só são pertinentes ao uso do diagnóstico interno do SQL Server. Esses contadores são marcados como "Somente interno”.
Nome |
Tipo de dados |
Descrição |
|---|---|---|
contador |
nvarchar(8000) |
Nome do evento de estatísticas do otimizador. |
ocorrência |
bigint |
Número de ocorrências do evento de otimização para este contador. |
valor |
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 remota, 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;