sys.dm_exec_query_optimizer_info (Transact-SQL)
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 |
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 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;