Feedback sulla stima della cardinalità (CE)

Si applica a: SQL Server 2022 (16.x) e versioni successive Database SQL di Microsoft AzureIstanza Gestita SQL di Azure

A partire da SQL Server 2022 (16.x), il feedback sulla stima della cardinalità (CE) fa parte della famiglia di funzionalità elaborazione di query intelligenti e risolve piani di esecuzione di query non ottimali per le query ripetute quando questi problemi derivano da presupposti errati del modello stima della cardinalità. Questo scenario consente di ridurre i rischi di regressione correlati allo stimatore di cardinalità predefinito durante l'aggiornamento da versioni precedenti del Motore di database.

Poiché nessun singolo insieme di modelli e ipotesi della stima di cardinalità può adattarsi all'ampia gamma di carichi di lavoro dei clienti e di distribuzioni dei dati, il feedback della stima di cardinalità fornisce una soluzione adattabile basata sulle caratteristiche di runtime delle query. Il feedback CE identifica e usa un'ipotesi del modello più adatta a una determinata query e alla distribuzione dei dati per migliorare la qualità del piano di esecuzione della query. Attualmente, il feedback CE può identificare gli operatori del piano di esecuzione per i quali il numero stimato di righe e il numero effettivo di righe sono molto diversi. Il feedback viene applicato quando si verificano errori significativi di stima del modello ed è disponibile un modello alternativo valido da provare.

Per altre funzionalità di feedback delle query, vedere Feedback delle concessioni di memoria e Feedback sul grado di parallelismo (DOP).

Comprendere il feedback sulla stima di cardinalità (CE)

La stima della cardinalità (CE) è il modo in cui Query Optimizer può stimare il numero totale di righe elaborate a ogni livello di un piano di query. In SQL Server la stima di cardinalità deriva principalmente da istogrammi creati al momento della creazione di indici o statistiche, in modo manuale o automatico. In alcuni casi, per determinare la cardinalità in SQL Server vengono inoltre utilizzate le informazioni sui vincoli e le riscritture logiche delle query.

Versioni diverse del motore di database usano ipotesi diverse del modello di stima della cardinalità in base al modo in cui i dati sono distribuiti e vengono interrogati. Per ulteriori informazioni, vedere versioni di CE.

Implementazione del feedback per la stima della cardinalità (CE)

Il feedback della stima della cardinalità (CE) apprende quali ipotesi del modello CE risultano ottimali nel tempo e applica quindi l'ipotesi storicamente più accurata:

1. Il feedback CE identifica le ipotesi relative al modello e valuta se sono accurate per le query ripetute.

2. Se un'ipotesi sembra errata, una successiva esecuzione della stessa query viene testata con un piano di esecuzione della query che modifica l'ipotesi significativa del modello di stima della cardinalità (CE) e verifica se ciò è utile. È possibile identificare l'inesattezza esaminando le righe effettive e stimate dagli operatori di piano. Non tutti gli errori possono essere corretti dalle varianti del modello disponibili nel feedback della stima di cardinalità.

3. Se migliora la qualità del piano, il piano di query precedente viene sostituito con un piano di query che usa l'USE HINT appropriato, che regola il modello di stima, implementato attraverso il meccanismo degli Hint del Query Store.

Solo il feedback verificato viene salvato. Il feedback della stima di cardinalità non viene utilizzato per quella query se l'ipotesi del modello modificata comporta una regressione delle prestazioni. In questo contesto una query annullata dall'utente viene percepita anche come regressione.

Scenari di feedback per la stima della cardinalità (CE)

Il feedback sulla stima della cardinalità (CE) risolve i problemi di regressione percepiti derivanti da presupposti errati del modello stima di cardinalità quando si usa la stima di cardinalità predefinita (CE120 o superiore) e può usare in modo selettivo presupposti di modello diversi. Gli scenari includono la correlazione, il contenimento dei join e l'obiettivo di riga dell'ottimizzatore.

Correlazione del feedback sulla stima della cardinalità (CE)

Quando Query Optimizer stima la selettività dei predicati in una determinata tabella o vista o il numero di righe che soddisfano il predicato specificato, usa i presupposti del modello di correlazione. Queste ipotesi possono essere che i predicati siano:

• Completamente indipendente (impostazione predefinita per CE70), dove la cardinalità viene calcolata moltiplicando le selettività di tutti i predicati.

• Parzialmente correlato (impostazione predefinita per CE120 e superiori), in cui la cardinalità viene calcolata mediante una variante del decremento esponenziale, ordinando le selettività dal predicato più selettivo a quello meno selettivo.

• Completamente correlato, dove la cardinalità viene calcolata usando le selettività minime per tutti i predicati.

Nell'esempio seguente viene utilizzata una correlazione parziale quando la compatibilità del database è impostata su 120 o valore superiore:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Quando la compatibilità del database è impostata su 160 e viene usata la correlazione predefinita, il feedback sulla stima di cardinalità tenta di spostare la correlazione nella direzione corretta un passaggio alla volta in base al fatto che la cardinalità stimata sia stata sottovalutata o sovrastimata rispetto al numero effettivo di righe. Usare la correlazione completa se un numero effettivo di righe è maggiore della cardinalità stimata. Utilizzare la piena indipendenza se il numero effettivo di righe è inferiore alla cardinalità stimata.

Per ulteriori informazioni, vedere versioni di CE.

Contenimento dei join nel feedback sulla stima di cardinalità (CE)

Per stimare la selettività dei predicati di join e dei predicati di filtro applicabili, Query Optimizer usa i presupposti del modello di contenimento. Questi presupposti sono:

• Contenimento semplice (impostazione predefinita per CE70) presuppone che i predicati di join siano completamente correlati, dove viene prima calcolata la selettività del filtro e quindi viene fattoriata la selettività di join.

• Contenimento di base (impostazione predefinita per CE120 e versioni successive) non presuppone alcuna correlazione tra predicati join e filtri downstream, dove viene prima calcolata la selettività di join e quindi viene fattoriata la selettività del filtro.

L'esempio seguente usa il contenimento di base quando la compatibilità del database è impostata su 120 o superiore:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Per altre informazioni, vedere le versioni del CE.

Feedback sulla stima di cardinalità (CE) e obiettivo di riga di Query Optimizer

Quando Query Optimizer stima la cardinalità di un piano di esecuzione, in genere presuppone che debbano essare elaborate tutte le righe qualificanti di tutte le tabelle. Tuttavia, alcuni modelli di query portano Query Optimizer a ricercare un piano che restituirà un numero minore di righe per ridurre le operazioni di I/O. Se la query specifica un numero di righe di destinazione (obiettivo di riga) che potrebbe essere previsto in fase di esecuzione usando le parole chiave TOP, IN o EXISTS, l'hint per la query FAST o un'istruzione SET ROWCOUNT, tale obiettivo di riga viene usato come parte del processo di ottimizzazione della query, come nell'esempio seguente:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Quando viene applicato il piano obiettivo di riga, il numero stimato di righe nel piano di query viene ridotto perché Query Optimizer presuppone che sia necessario elaborare un numero minore di righe per raggiungere l'obiettivo della riga.

Anche se l'obiettivo di riga è una strategia di ottimizzazione vantaggiosa per determinati modelli di query, se i dati non vengono distribuiti in modo uniforme, è possibile che vengano analizzate più pagine rispetto a quanto stimato, ovvero l'obiettivo di riga diventa inefficiente. Quando viene rilevata questa inefficienza, il feedback sulla stima di cardinalità può disabilitare la scansione row goal e abilitare un'operazione di seek.

Nel piano di esecuzione non esiste alcun attributo specifico per il feedback sulla stima di cardinalità, ma per l'hint di Query Store è elencato un attributo. Verificare che QueryStoreStatementHintSource sia CE feedback.

Considerazioni per il feedback sulla stima di cardinalità (CE)

• Per abilitare il feedback sulla stima di cardinalità (CE), abilitare il livello di compatibilità del database 160 per il database a cui si è connessi durante l’esecuzione della query. Query Store deve essere abilitato e in modalità READ_WRITE per ogni database in cui viene usato il feedback CE.

• Per disabilitare il feedback sulla stima di cardinalità (CE) a livello di database, usare la CE_FEEDBACKconfigurazione con ambito database. Ad esempio, nel database utente:

  ALTER DATABASE SCOPED CONFIGURATION
  SET CE_FEEDBACK = OFF;
  

• Per disabilitare il feedback CE a livello di query, utilizzare l'hint di query DISABLE_CE_FEEDBACK.

L'attività di feedback della stima di cardinalità è visibile tramite gli XEvent query_feedback_analysis e query_feedback_validation.

È possibile tenere traccia degli hint impostati dal feedback CE tramite la vista del catalogo sys.query_store_query_hints.

È possibile tenere traccia delle informazioni sul feedback usando la visualizzazione del catalogo sys.query_store_plan_feedback.

Se una query ha un piano di esecuzione forzato tramite Query Store, il feedback CE non viene usato per tale query.

Se una query usa hint di query codificati in modo statico o hint di Query Store impostati dall'utente, il feedback CE non viene usato per quella query. Per ulteriori informazioni, vedere Suggerimenti per la query e suggerimenti per il Query Store.

A partire da SQL Server 2022 (16.x), quando Query Store per le repliche secondarie è abilitato, il feedback CE non riconosce le repliche secondarie nei gruppi di disponibilità. Il feedback CE attualmente va a vantaggio solo delle repliche primarie. In caso di failover, il feedback applicato alle repliche primarie o secondarie viene perso. Query Store è disponibile nelle repliche del gruppo di disponibilità secondario a partire da SQL Server 2025 (17.x). Per ulteriori informazioni, vedere Query Store per secondari leggibili.

Persistenza del feedback sulla stima di cardinalità (CE)

Si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure.

Il feedback sulla stima della cardinalità (CE) può rilevare scenari in cui l'ottimizzazione dell'obiettivo di riga deve essere mantenuta e mantenere questa modifica salvandola in modo permanente in Query Store sotto forma di hint di Query Store. La nuova ottimizzazione verrà usata per le esecuzioni future della query. Il feedback CE persiste anche in altri scenari oltre ai modelli di query con ottimizzazione row goal, come illustrato in dettaglio in scenari di feedback. Il feedback sulla stima di cardinalità gestisce attualmente gli scenari di selettività dei predicati utilizzati dal modello di correlazione della stima di cardinalità e gli scenari relativi ai predicati di join, gestiti dal modello di contenimento della stima di cardinalità.

Questa funzionalità è stata introdotta in SQL Server 2022 (16.x); tuttavia, questo miglioramento delle prestazioni è disponibile per le query eseguite con il livello di compatibilità del database pari a 160 o superiore, oppure per quelle con hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n pari a 160 o superiore, e quando Query Store è abilitato per il database e si trova in stato di "lettura/scrittura".

Problemi noti relativi al feedback sulla stima della cardinalità (CE)

Dettagli sui problemi noti

Rallentamento delle prestazioni di SQL Server dopo l'aggiornamento cumulativo 8 per SQL Server 2022 in determinate condizioni

A partire da SQL Server 2022 (16.x) aggiornamento cumulativo 8, SQL Server potrebbe presentare aumenti imprevisti di utilizzo della CPU e della memoria. Inoltre, è possibile osservare un aumento delle attese in RESOURCE_SEMAPHORE_QUERY_COMPILE. Potresti anche notare aumenti costanti del numero di oggetti della cache del piano in uso che si avvicinano ai limiti della cache del piano e che la cancellazione manuale della cache del piano con tecniche come ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE o DBCC FREEPROCCACHE non aiuta. Questo comportamento è stato riscontrato solo da alcuni clienti.

Questo problema non influisce su tutti i carichi di lavoro e dipende dal numero di piani diversi generati, così come dal numero di piani idonei per il coinvolgimento della funzionalità di feedback sulla stima di cardinalità. Mentre il feedback sulla stima di cardinalità analizza gli operatori del piano alla ricerca di errori significativi di stima del modello, esiste uno scenario in cui un piano referenziato può essere dereferenziato durante questa fase di analisi. Questa situazione impedisce al piano di essere rimosso dalla memoria mediante il consueto algoritmo Least Recently Used (LRU). Il meccanismo LRU è uno dei modi in cui SQL Server applica i criteri di espulsione dei piani. SQL Server rimuove anche i piani dalla memoria se nel sistema si verifica un utilizzo elevato di memoria. Quando SQL Server tenta di rimuovere i piani che sono stati dereferenziati in modo improprio, non è in grado di rimuovere tali piani dalla cache dei piani causando la continua crescita della cache. La cache in crescita potrebbe iniziare a causare compilazioni aggiuntive che finirebbero per utilizzare più CPU e memoria. Per ulteriori informazioni, vedere Plan Cache Internals.

Sintomo: il numero di voci in uso nella cache dei piani di esecuzione, contrassegnate come "dirty", sia nei piani SQL sia nei piani oggetto, aumenta nel tempo fino a 50.000 o più. Se si osservano le voci della cache dei piani che iniziano ad avvicinarsi a questo livello insieme a un aumento imprevisto dell'utilizzo della CPU, il sistema potrebbe riscontrare questo problema. Viene fornita una correzione nell'aggiornamento cumulativo 12 di SQL Server 2022 (16.x). Vedere KB5033663.

Per monitorare il numero di voci nella cache dei piani utilizzate dal sistema, è possibile usare gli esempi seguenti come un'istantanea del numero di voci nella cache dei piani presenti in un determinato momento. Ad esempio, monitorare questo fenomeno osservando regolarmente nel corso del tempo il numero di voci della cache del piano contrassegnate come dirty.

SELECT CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
            WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
            ELSE '[All other cache stores]'
       END AS PlanType,
       COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
     LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp
         ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
      AND ecp.bucketid IS NULL
GROUP BY CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
              WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
              ELSE '[All other cache stores]'
         END;

Un altro set di query fornisce anche le stesse informazioni dell'esempio precedente e consente al tempo stesso di osservare metriche di prestazioni aggiuntive. Le percentuali di riscontro della cache dei piani diminuiscono, così come il numero di compilazioni in relazione al numero di richieste batch al secondo. È possibile utilizzare le query seguenti per monitorare il sistema nel corso del tempo. Tenendo d'occhio il rapporto di hit della cache (cali imprevisti), gli oggetti della cache in uso (aumenti del numero fino a livelli prossimi a 50.000 senza diminuire) e un valore di Richieste batch/sec inferiore al previsto rispetto a un aumento di Compilazioni/sec.

--SQL Plan (Adhoc and Prepared plans)
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (SQL Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (Object Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT CASE WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
            WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
       END AS [SQLServer:SQL Statistics],
       FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
      AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec');

Soluzione alternativa

Se il sistema continua a riscontrare i sintomi descritti in precedenza, dopo l'applicazione dell'aggiornamento cumulativo 12 KB5033663, la funzionalità di feedback CE può essere disabilitata a livello di database.

Per recuperare la memoria cache del piano impiegata da questo problema, è necessario riavviare l'istanza di SQL Server. Questa azione di riavvio può essere eseguita dopo la disattivazione della funzionalità di feedback CE. Per disabilitare il feedback sulla stima di cardinalità (CE) a livello di database, usare la CE_FEEDBACKconfigurazione con ambito database. Ad esempio, nel database utente:

ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = OFF;

Feedback e segnalazione problemi

Per feedback o domande, inviare un’e-mail a CEFfeedback@microsoft.com

Contenuto correlato

• Feedback sulla stima di cardinalità in SQL Server 2022
• Elaborazione di query intelligenti nei database SQL
• Funzionalità di elaborazione di query intelligenti in dettaglio
• Stima della cardinalità (SQL Server)
• RECONFIGURE (Transact-SQL)
• Monitoraggio e ottimizzazione delle prestazioni
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)