sys.dm_exec_query_optimizer_info (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

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 alterações observadas durante o processo de ajuste. Alguns contadores fornecem dados relevantes apenas para o uso de diagnóstico interno do SQL Server. Esses contadores são marcados como "Somente interno”.

Observação

Para chamar isso do Azure Synapse Analytics ou do Analytics Platform System (PDW), use o nome sys.dm_pdw_nodes_exec_query_optimizer_info. Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.

Nome	Tipo de dados	Descrição
balcão	nvarchar(4000)	Nome do evento de estatísticas do otimizador.
occurrence	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.
pdw_node_id	int	Aplica-se a: Azure Synapse Analytics, Analytics Platform System (PDW) O identificador do nó em que essa distribuição está ativada.

Permissões

No SQL Server e na Instância Gerenciada de SQL, requer a permissão VIEW SERVER STATE.

Nos objetivos de serviço do SQL Database Basic, S0 e S1 e para bancos de dados em pools elásticos, a conta de administrador do servidor, a conta de administrador do Microsoft Entra ou a associação à ##MS_ServerStateReader##função de servidor é necessária. Em todos os outros objetivos de serviço do Banco de Dados SQL, a permissão VIEW DATABASE STATE no banco de dados ou a associação à função de servidor ##MS_ServerStateReader## são necessárias.

Permissões do SQL Server 2022 e posteriores

É necessária a permissão VIEW SERVER PERFORMANCE 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 são medidas quando o SQL Server determina alterações em dm_exec_query_optimizer_info, incluindo consultas geradas pelo usuário e pelo sistema. A execução de um plano já armazenado em cache não altera os valores em dm_exec_query_optimizer_info, apenas as otimizações são significativas.

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
pesquisar 0	Somente interno	Somente interno
pesquisar 0 vez	Somente interno	Somente interno
pesquisar 0 tarefa	Somente interno	Somente interno
pesquisar 1	Somente interno	Somente interno
pesquisar 1 vez	Somente interno	Somente interno
pesquisar 1 tarefa	Somente interno	Somente interno
pesquisa 2	Somente interno	Somente interno
pesquisar 2 vezes	Somente interno	Somente interno
pesquisar 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
timeout	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, o MAXDOP efetivo é determinado pelo grau máximo de configuração do servidor de paralelismo 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

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

Quais são as estatísticas de execução do otimizador atual 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;  

Confira também

Exibições e funções de gerenciamento dinâmico (Transact-SQL)
Funções e exibições de gerenciamento dinâmico relacionadas à execução (Transact-SQL)