sys.dm_exec_query_optimizer_info (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

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”.

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.

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

• 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)