sys.dm_exec_query_optimizer_info (Transact-SQL)

Articolo
08/21/2023

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
Contatore	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	Si applica a: Azure Synapse Analytics, Piattaforma di analisi (PDW) Identificatore del nodo in cui è attiva la distribuzione.

Autorizzazioni

In SQL Server e Istanza gestita di SQL è richiesta VIEW SERVER STATE l'autorizzazione.

In database SQL 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 del ##MS_ServerStateReader##server. Per tutti gli altri obiettivi di servizio database SQL, è necessaria l'autorizzazione VIEW DATABASE STATE per il database o l'appartenenza al ruolo del ##MS_ServerStateReader## server.

Autorizzazioni per SQL Server 2022 e versioni successive

È richiesta l'autorizzazione VIEW edizione Standard RVER PERFORMANCE STATE nel server.

Osservazioni:

sys.dm_exec_query_optimizer_info contiene le proprietà seguenti (contatori). Tutti i valori di occorrenza sono cumulativi e vengono impostati su 0 al riavvio del sistema. Tutti i valori dei campi valori 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 alle dm_exec_query_optimizer_info, incluse le query generate sia dall'utente che dal sistema. L'esecuzione di un piano già memorizzato nella cache non modifica i valori in dm_exec_query_optimizer_info, ma 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
attività	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 istruzioni INSERT.	Non applicabile
delete stmt	Numero di ottimizzazioni per istruzioni DELETE.	Non applicabile
update stmt	Numero di ottimizzazioni per istruzioni UPDATE.	Non applicabile
contains subquery	Numero di ottimizzazioni per una query contenente almeno una sottoquery.	Non applicabile
unnest failed	Solo interno	Solo interno
tabelle	Numero totale di ottimizzazioni.	Numero medio di tabelle a cui viene fatto riferimento per query ottimizzata.
hint	Numero di volte in cui un hint specifico è stato specificato. Gli hint conteggiati includono gli hint per la query JOIN, GROUP, UNION e FORCE ORDER, l'opzione SET FORCE PLAN e gli hint di join.	Non applicabile
order hint	Numero di volte in cui un hint per la query FORCE ORDER è stato specificato.	Non applicabile
join hint	Numero di volte in cui l'algoritmo JOIN è stato applicato a un hint di join.	Non applicabile
view reference	Numero di volte in cui è stato fatto riferimento a una vista in una query.	Non applicabile
remote query	Numero di ottimizzazioni in cui la query ha fatto riferimento ad almeno un'origine dei dati remota, ad esempio una tabella con un nome in quattro parti o un risultato OPENROWSET.	Non applicabile
maximum DOP	Numero totale di ottimizzazioni.	Valore MAXDOP effettivo medio per un piano ottimizzato. Per impostazione predefinita, maxDOP effettivo è determinato dall'opzione di configurazione del server max degree of parallelism e può essere sottoposto a override per una query specifica in base al valore dell'hint per la query MAXDOP.
maximum recursion level	Numero di ottimizzazioni in cui è stato specificato un livello MAXRECURSION maggiore di 0 con l'hint per la query.	Livello MAXRECURSION medio 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 è stata trovata la corrispondenza per 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-only.	Non applicabile
merge stmt	Numero di ottimizzazioni per le istruzioni MERGE.	Non applicabile

Esempi

R. Visualizzazione delle statistiche durante l'esecuzione di Query 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. Visualizzazione del 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;

Vedi anche

Funzioni e viste a gestione dinamica (Transact-SQL)
Funzioni e viste a gestione dinamica correlate all'esecuzione (Transact-SQL)