Compartilhar via

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure PDW (Sistema de Plataforma de Análise) do Azure Synapse Analytics

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 do tempo decorrido e o valor do 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 PDW (Analytics Platform System), 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
counter 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 O identificador do nó em que essa distribuição está ativada.

Aplica-se a: Azure Synapse Analytics, Analytics Platform System (PDW)

Permissões

O SQL Server 2019 (15.x) e versões anteriores e a Instância Gerenciada de SQL do Azure exigem VIEW SERVER STATE permissão.

SQL Server 2022 (16.x) e versões posteriores, requer VIEW SERVER PERFORMANCE STATE permissão no servidor.

Nos objetivos de serviço Básico, S0 e S1 do Banco de Dados SQL do Azure 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 na função de servidor ##MS_ServerStateReader## é necessária. Em todos os outros objetivos de serviço do Banco de dados SQL, é necessária a permissão no banco de dados ou a VIEW DATABASE STATE associação na função de servidor ##MS_ServerStateReader## .

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 como na reinicialização do 0 sistema. Todos os valores dos campos de valor são definidos como na reinicialização do NULL 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 no 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, somente as otimizações são significativas.

Contador Ocorrência Valor
optimizations Número total de otimizações. Não aplicável
elapsed time Número total de otimizações. Tempo médio decorrido por otimização de uma instrução individual (consulta), em segundos.
final cost Número total de otimizações. Custo estimado médio para um plano otimizado em unidades de custo interno.
trivial plan Somente interno Somente interno
tasks Somente interno Somente interno
no plan Somente interno Somente interno
search 0 Somente interno Somente interno
search 0 time Somente interno Somente interno
search 0 tasks Somente interno Somente interno
search 1 Somente interno Somente interno
search 1 time Somente interno Somente interno
search 1 tasks Somente interno Somente interno
search 2 Somente interno Somente interno
search 2 time Somente interno Somente interno
search 2 tasks Somente interno Somente interno
gain stage 0 to stage 1 Somente interno Somente interno
gain stage 1 to stage 2 Somente interno Somente interno
timeout Somente interno Somente interno
memory limit exceeded Somente interno Somente interno
insert stmt Número de otimizações que são para INSERT instruções. Não aplicável
delete stmt Número de otimizações que são para DELETE instruções. Não aplicável
update stmt Número de otimizações que são para UPDATE instruções. Não aplicável
contains subquery Número de otimizações para uma consulta que contém ao menos uma subconsulta. Não aplicável
unnest failed Somente interno Somente interno
tables Número total de otimizações. Calcule o número médio de tabelas referenciadas por consulta otimizada.
hints Número de vezes que alguma dica foi especificada. As dicas contadas incluem: JOIN, GROUP, UNION e FORCE ORDER dicas de consulta, FORCE PLAN opção set e dicas de junção. Não aplicável
order hint Número de vezes em que a ordem de junção foi forçada. Esse contador não se restringe à FORCE ORDER dica. Especificar um algoritmo de junção em uma consulta, como um INNER HASH JOIN, também força a ordem de junção, que incrementa o contador. Não aplicável
join hint Número de vezes que o algoritmo de junção foi forçado por uma dica de associação. A FORCE ORDER dica de consulta não incrementa esse contador. Não aplicável
view reference Número de vezes que uma exibição é referenciada em uma consulta. Não aplicável
remote query Número de otimizações em que a consulta fazia referência a pelo menos uma fonte de dados remota, como uma tabela com um nome de quatro partes ou um OPENROWSET resultado. Não aplicável
maximum DOP Número total de otimizações. Valor efetivo MAXDOP médio para um plano otimizado. Por padrão, effective MAXDOP é determinado pela opção de configuração do servidor de grau máximo de paralelismo e pode ser substituído para uma consulta específica pelo valor da MAXDOP dica de consulta.
maximum recursion level Número de otimizações em que um MAXRECURSION nível maior que 0 foi especificado com a dica de consulta. Nível médio MAXRECURSION em otimizações em que um nível máximo de recursão foi especificado com a dica de consulta.
indexed views loaded Somente interno Somente interno
indexed views matched Número de otimizações em que uma ou mais exibições indexadas são correspondidas. Número médio de exibições correspondentes.
indexed views used 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.
indexed views updated 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.
dynamic cursor request Número de otimizações em que uma solicitação de cursor dinâmico foi especificada. Não aplicável
fast forward cursor request 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 que são para MERGE instruções. Não aplicável

Exemplos

R. Exibir estatísticas sobre a execução do otimizador

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

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Exibir 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;

E. Exibir o número total de dicas durante a otimização

Quantas dicas são contadas quando FORCE ORDER são incluídas como uma dica de consulta?

-- Check hint count before query execution
SELECT ISNULL('', 0) AS [Before],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

SELECT poh.PurchaseOrderID,
    poh.OrderDate,
    pod.ProductID,
    pod.DueDate,
    poh.VendorID
FROM Purchasing.PurchaseOrderHeader AS poh
INNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod
    ON poh.PurchaseOrderID = pod.PurchaseOrderID
OPTION (
    FORCE ORDER,
    RECOMPILE
);

-- check hint count after query execution
SELECT ISNULL('', 0) AS [After],
    [counter],
    occurrence
FROM sys.dm_exec_query_optimizer_info
WHERE [counter] IN (
    'hints',
    'order hint',
    'join hint'
);

Conteúdo relacionado