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".

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.

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

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