Partilhar via

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure do Azure Synapse AnalyticsAnalytics Platform System (PDW)

Retorna estatísticas detalhadas sobre a operação do otimizador de consulta do SQL Server. Você pode usar essa exibição ao ajustar uma carga de trabalho para identificar problemas ou melhorias de 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. Essa sintaxe não é suportada pelo pool SQL sem servidor no Azure Synapse Analytics.

Designação Tipo de dados Descrição
counter nvarchar(4000) Nome do evento de estatísticas do otimizador.
occurrence bigint Número de ocorrências de evento de otimização para este contador.
value flutuar Valor médio da propriedade por ocorrência de evento.
pdw_node_id int O identificador do nó em que esta distribuição se encontra.

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 SQL do Azure exigem permissão VIEW SERVER STATE.

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

No Banco de Dados SQL do Azure Basic, S0e S1 objetivos de serviço e para bancos de dados em pools elásticos , a conta de administrador do servidor , a conta de de administrador do Microsoft Entra ou a associação ao de 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 de no banco de dados ou a associação à 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 0 na reinicialização do sistema. Todos os valores para campos de valor são definidos como NULL na reinicialização do sistema. Todos os valores de coluna de valor que especificam uma média usam o valor de ocorrência da mesma linha que 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 valores em dm_exec_query_optimizer_info, apenas 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 médio estimado para um plano otimizado em unidades de custo internas.
trivial plan Apenas interno Apenas interno
tasks Apenas interno Apenas interno
no plan Apenas interno Apenas interno
search 0 Apenas interno Apenas interno
search 0 time Apenas interno Apenas interno
search 0 tasks Apenas interno Apenas interno
search 1 Apenas interno Apenas interno
search 1 time Apenas interno Apenas interno
search 1 tasks Apenas interno Apenas interno
search 2 Apenas interno Apenas interno
search 2 time Apenas interno Apenas interno
search 2 tasks Apenas interno Apenas interno
gain stage 0 to stage 1 Apenas interno Apenas interno
gain stage 1 to stage 2 Apenas interno Apenas interno
timeout Apenas interno Apenas interno
memory limit exceeded Apenas interno Apenas 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
merge stmt Número de otimizações que são para MERGE instruções. Não aplicável
contains subquery Número de otimizações para uma consulta que contém pelo menos uma subconsulta. Não aplicável
unnest failed Apenas interno Apenas interno
tables Número total de otimizações. 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 de conjunto 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. Este contador não se restringe à dica FORCE ORDER. Especificar um algoritmo de junção dentro de 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 junção. A dica de consulta FORCE ORDER não incrementa esse contador. Não aplicável
view reference Número de vezes que um modo de exibição é referenciado em uma consulta. Não aplicável
remote query Número de otimizações em que a consulta fez referência a pelo menos uma fonte de dados remota, como uma tabela com um nome de quatro partes ou um resultado OPENROWSET. Não aplicável
maximum DOP Número total de otimizações. Valor MAXDOP efetivo médio para um plano otimizado. Por padrão, a MAXDOP efetiva é determinada pelo grau máximo de paralelismo opção de configuração do servidor e pode ser substituída para uma consulta específica pelo valor da dica de consulta MAXDOP.
maximum recursion level Número de otimizações em que um nível de MAXRECURSION maior que 0 foi especificado com a dica de consulta. Nível médio de MAXRECURSION em otimizações em que um nível máximo de recursão foi especificado com a dica de consulta.
indexed views loaded Apenas interno Apenas 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 visualizaçõ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 serem correspondidas. Número médio de visualizações utilizadas.
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 visualizações mantidas.
dynamic cursor request Número de otimizações nas quais uma solicitação de cursor dinâmico foi especificada. Não aplicável
fast forward cursor request Número de otimizações nas quais uma solicitação de cursor de avanço rápido foi especificada. Não aplicável

Exemplos

Um. Ver estatísticas sobre a execução do otimizador

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

SELECT * FROM sys.dm_exec_query_optimizer_info;

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

Quantas otimizações são realizadas?

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. Fração 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. Veja o número total de dicas durante a otimização

Quantas dicas são contadas quando FORCE ORDER é incluída como 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