sys.dm_exec_query_optimizer_info (Transact-SQL)

Articolo
06/18/2013

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 del costo finale per confrontare le ottimizzazioni del carico di lavoro corrente e tutte le modifiche osservate durante il processo di ottimizzazione. Alcuni contatori forniscono dati rilevanti solo per utilizzo diagnostico interno in SQL Server. Questi contatori sono contrassegnati come "Solo per uso interno".

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.

Autorizzazioni

È richiesta l'autorizzazione VIEW SERVER STATE nel server.

Osservazioni

sys.dm_exec_query_optimizer_info contiene le proprietà (contatori) riportate nella tabella seguente. 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 di query vengono misurate quando SQL Server determina le modifiche apportate a dm_exec_query_optimizer_info, incluse le query generate dall'utente e quelle generate dal sistema. L'esecuzione di un piano già inserito nella cache non modifica i valori in dm_exec_query_optimizer_info, in quando sono significative solo le ottimizzazioni.

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 per uso interno	Solo per uso interno
tasks	Solo per uso interno	Solo per uso interno
no plan	Solo per uso interno	Solo per uso interno
search 0	Solo per uso interno	Solo per uso interno
search 0 time	Solo per uso interno	Solo per uso interno
search 0 tasks	Solo per uso interno	Solo per uso interno
search 1	Solo per uso interno	Solo per uso interno
search 1 time	Solo per uso interno	Solo per uso interno
search 1 tasks	Solo per uso interno	Solo per uso interno
search 2	Solo per uso interno	Solo per uso interno
search 2 time	Solo per uso interno	Solo per uso interno
search 2 tasks	Solo per uso interno	Solo per uso interno
gain stage 0 to stage 1	Solo per uso interno	Solo per uso interno
gain stage 1 to stage 2	Solo per uso interno	Solo per uso interno
timeout	Solo per uso interno	Solo per uso interno
memory limit exceeded	Solo per uso interno	Solo per uso 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 per uso interno	Solo per uso interno
tabelle	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 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, il valore MAXDOP effettivo è determinato dall'opzione di configurazione del server max degree of parallelism e in una query specifica può essere sostituito dal 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 per uso interno	Solo per uso 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

A.Visualizzazione delle statistiche durante l'esecuzione di Query Optimizer

Nell'esempio seguente vengono visualizzate le statistiche di esecuzione di Query Optimizer per l'istanza corrente 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;

Vedere anche

Riferimento

Funzioni e viste a gestione dinamica (Transact-SQL)

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

Condividi tramite