sys.dm_exec_query_optimizer_info (Transact-SQL)
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 l'autorizzazione VIEW SERVER STATE
.
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'adesione ruolo del server ##MS_ServerStateReader##
.
Autorizzazioni per SQL Server 2022 e versioni successive
È richiesta l'autorizzazione VIEW SERVER PERFORMANCE STATE per il 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 a gestione dinamica e DMV (Transact-SQL)
Funzioni e viste a gestione dinamica relative all'esecuzione (Transact-SQL)
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per