Condividi tramite

sys.dm_exec_query_optimizer_info (Transact-SQL)

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure azure Synapse Analytics Analytics Platform System (PDW)

Restituisce statistiche dettagliate sul funzionamento di Query Optimizer di SQL Server. È possibile utilizzare questa vista durante l'ottimizzazione di un carico di lavoro per individuare problemi o miglioramenti per l'ottimizzazione delle query. Ad esempio, è possibile utilizzare il numero totale di ottimizzazioni, il valore del tempo trascorso e il valore di costo finale per confrontare le ottimizzazioni della query per il carico di lavoro corrente con eventuali variazioni rilevate durante il processo di ottimizzazione. Alcuni contatori forniscono dati rilevanti solo per l'uso di diagnostica interno di SQL Server. Questi contatori sono contrassegnati come "Solo per uso interno".

Nota

Per chiamare questa operazione da Azure Synapse Analytics o da Platform System (PDW), usare il nome sys.dm_pdw_nodes_exec_query_optimizer_info. Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

Nome Tipo di dati Descrizione
counter nvarchar(4000) Nome dell'evento statistiche di Query Optimizer.
occurrence bigint Numero di occorrenze dell'evento di ottimizzazione per il contatore corrente.
value float Valore medio della proprietà per occorrenza dell'evento.
pdw_node_id int Identificatore del nodo in cui è attiva la distribuzione.

Si applica a: Azure Synapse Analytics, Piattaforma di analisi (PDW)

Autorizzazioni

SQL Server 2019 (15.x) e versioni precedenti e Istanza gestita di SQL di Azure richiedono VIEW SERVER STATE l'autorizzazione.

SQL Server 2022 (16.x) e versioni successive richiede VIEW SERVER PERFORMANCE STATE l'autorizzazione per il server.

In database SQL di Azure obiettivi di servizio Basic, S0 e S1 e per i database nei pool elastici è necessario l'account amministratore del server, l'account amministratore di Microsoft Entra o l'appartenenza al ruolo server ##MS_ServerStateReader##. Per tutti gli altri obiettivi del servizio database SQL, è necessaria l'autorizzazione VIEW DATABASE STATE per il database o l'appartenenza al ruolo del server ##MS_ServerStateReader### .

Osservazioni:

sys.dm_exec_query_optimizer_info contiene le proprietà seguenti (contatori). Tutti i valori di occorrenza sono cumulativi e sono impostati su 0 al riavvio del sistema. Tutti i valori per i campi valore vengono impostati su NULL al riavvio del sistema. Tutti i valori delle colonne valori che specificano una media utilizzano il valore di occorrenza della stessa riga del denominatore nel calcolo della media. Tutte le ottimizzazioni delle query vengono misurate quando SQL Server determina le modifiche apportate a dm_exec_query_optimizer_info, incluse le query generate dall'utente e generate dal sistema. L'esecuzione di un piano già memorizzato nella cache non modifica i valori in dm_exec_query_optimizer_info, solo le ottimizzazioni sono significative.

Contatore Occorrenza Valore
optimizations Numero totale di ottimizzazioni. Non applicabile
elapsed time Numero totale di ottimizzazioni. Tempo medio trascorso per ottimizzazione in una singola istruzione (query), espresso in secondi.
final cost Numero totale di ottimizzazioni. Costo medio stimato per piano ottimizzato espresso nelle unità di costo interne.
trivial plan Solo interno Solo interno
tasks Solo interno Solo interno
no plan Solo interno Solo interno
search 0 Solo interno Solo interno
search 0 time Solo interno Solo interno
search 0 tasks Solo interno Solo interno
search 1 Solo interno Solo interno
search 1 time Solo interno Solo interno
search 1 tasks Solo interno Solo interno
search 2 Solo interno Solo interno
search 2 time Solo interno Solo interno
search 2 tasks Solo interno Solo interno
gain stage 0 to stage 1 Solo interno Solo interno
gain stage 1 to stage 2 Solo interno Solo interno
timeout Solo interno Solo interno
memory limit exceeded Solo interno Solo interno
insert stmt Numero di ottimizzazioni per INSERT le istruzioni. Non applicabile
delete stmt Numero di ottimizzazioni per DELETE le istruzioni. Non applicabile
update stmt Numero di ottimizzazioni per UPDATE le istruzioni. Non applicabile
contains subquery Numero di ottimizzazioni per una query contenente almeno una sottoquery. Non applicabile
unnest failed Solo interno Solo interno
tables Numero totale di ottimizzazioni. Numero medio di tabelle a cui viene fatto riferimento per query ottimizzata.
hints Numero di volte in cui un hint specifico è stato specificato. Gli hint conteggiati includono: JOIN, UNION GROUPe FORCE ORDER hint per la query, FORCE PLAN opzione set e hint di join. Non applicabile
order hint Numero di volte in cui è stato forzato l'ordine di join. Questo contatore non è limitato all'hint FORCE ORDER . Se si specifica un algoritmo di join all'interno di una query, ad esempio , INNER HASH JOINviene chiamato anche l'ordine di join, che incrementa il contatore. Non applicabile
join hint Numero di volte in cui l'algoritmo JOIN è stato applicato a un hint di join. L'hint per la FORCE ORDER query non incrementa questo contatore. Non applicabile
view reference Numero di riferimenti a una vista in una query. Non applicabile
remote query Numero di ottimizzazioni in cui la query ha fatto riferimento ad almeno un'origine dati remota, ad esempio una tabella con un nome in quattro parti o un OPENROWSET risultato. Non applicabile
maximum DOP Numero totale di ottimizzazioni. Valore effettivo MAXDOP medio per un piano ottimizzato. Per impostazione predefinita, l'efficacia MAXDOP è determinata dall'opzione di configurazione del server max degree of parallelism e potrebbe essere sottoposta a override per una query specifica in base al valore dell'hint per la MAXDOP query.
maximum recursion level Numero di ottimizzazioni in cui è stato specificato un MAXRECURSION livello maggiore di 0 quello specificato con l'hint per la query. Livello medio MAXRECURSION nelle ottimizzazioni in cui è stato specificato un livello di ricorsione massimo con l'hint per la query.
indexed views loaded Solo interno Solo interno
indexed views matched Numero di ottimizzazioni in cui vengono confrontate una o più viste indicizzate. Numero medio di viste corrispondenti.
indexed views used Numero di ottimizzazioni in cui una o più viste indicizzate vengono utilizzate nel piano di output dopo aver individuato la corrispondenza. Numero medio di viste utilizzate.
indexed views updated Numero di ottimizzazioni di un'istruzione DML che generano un piano che gestisce una o più viste indicizzate. Numero medio di viste gestite.
dynamic cursor request Numero di ottimizzazioni in cui è stata specificata una richiesta di cursore dinamico. Non applicabile
fast forward cursor request Numero di ottimizzazioni in cui è stata specificata una richiesta di cursore fast forward. Non applicabile
merge stmt Numero di ottimizzazioni per MERGE le istruzioni. Non applicabile

Esempi

R. Visualizzare le statistiche sull'esecuzione di Optimizer

Quali sono le statistiche di esecuzione correnti di Optimizer per questa istanza di SQL Server?

SELECT * FROM sys.dm_exec_query_optimizer_info;

B. Visualizzare il numero totale di ottimizzazioni

Nell'esempio seguente viene visualizzato il numero di ottimizzazioni eseguite.

SELECT occurrence AS Optimizations
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'optimizations';

C. Tempo medio trascorso per ottimizzazione

Nell'esempio seguente viene visualizzato il tempo medio trascorso per ogni ottimizzazione.

SELECT ISNULL(value,0.0) AS ElapsedTimePerOptimization
FROM sys.dm_exec_query_optimizer_info
WHERE counter = 'elapsed time';

D. Frazione di ottimizzazioni che interessano sottoquery

Nell'esempio seguente viene individuata la frazione di query ottimizzate contenente una sottoquery.

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. Visualizzare il numero totale di hint durante l'ottimizzazione

Quanti hint vengono conteggiati quando FORCE ORDER viene incluso come hint per la query?

-- 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'
);

Contenuto correlato