Feedback sulla stima di cardinalità (CE)

Si applica a: si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure, Istanza gestita di 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 alla stima di cardinalità predefinita durante l'aggiornamento da versioni precedenti del motore di database.

Poiché nessun singolo set di modelli e presupposti stima di cardinalità è in grado di supportare l'ampia gamma di carichi di lavoro e distribuzioni di dati dei clienti, il feedback sulla stima di cardinalità fornisce una soluzione adattabile in base alle caratteristiche di runtime delle query. Il feedback sulla stima di cardinalità identifica e usa un presupposto del modello più adatto a una determinata query e distribuzione dei dati per migliorare la qualità del piano di esecuzione delle query. Attualmente, il feedback sulla stima di cardinalità può identificare gli operatori di piano dove il numero stimato di righe e il numero effettivo di righe differiscono molto. 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).

Informazioni sul 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 presupposti del modello stima di cardinalità diversi in base alla distribuzione di dati ed esecuzione di query. Per altre informazioni, vedere Versioni di stima di cardinalità.

Implementazione del feedback sulla stima della cardinalità (CE)

Il feedback sulla stima della cardinalità (CE) apprende quali presupposti del modello stima della cardinalità restano ottimali nel tempo e quindi applica il presupposto storicamente più corretto:

1. Il feedback stima di cardinalità identifica i presupposti correlati al modello e ne valuta l’accuratezza per le query ripetute.

2. Se un presupposto non è corretto, viene testata un'esecuzione successiva della stessa query con un piano di query che regola il presupposto del modello stima della cardinalità incisivo e verifica se ha migliorato l'efficienza. È 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 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'hint di query USE HINT appropriato che regola il modello di stima, implementato con il meccanismo degli hint di Query Store.

Vengono mantenuti solo i feedback verificati. Il feedback stima di cardinalità non viene usato per la query se il presupposto del modello modificato genera una regressione delle prestazioni. In questo contesto una query annullata dall'utente viene percepita anche come regressione.

Scenari di feedback sulla stima di 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 correlazione, contenimento join e ottimizzazione obiettivo di riga.

Correlazione feedback sulla stima di 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. Questi presupposti possono coincidere con i predicati:

• 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 versioni successive), dove la cardinalità viene calcolata usando una variazione sul backoff esponenziale, ordinando le selettività dai predicato selettivo maggiore al minore.

• 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. Usare l'indipendenza completa se un numero effettivo di righe è inferiore alla cardinalità stimata.

Per altre informazioni, vedere Versioni di stima di cardinalità.

Contenimento join di 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 Versioni di stima di cardinalità.

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 l'analisi degli obiettivi di riga e abilitare una ricerca.

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. È necessario abilitare Query Store in ogni database dove viene usato il feedback sulla stima di cardinalità e impostare lo stato READ_WRITE.

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

  ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
  

• Per disabilitare il feedback sulla stima di cardinalità a livello di query, usare l'hint di query DISABLE_CE_FEEDBACK.

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

È possibile tenere traccia degli hint impostati dal feedback della stima della cardinalità usando 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 dispone di un piano di query forzato tramite Query Store, non è possibile usare il feedback sulla stima di cardinalità per tale query.

Se una query usa hint di query hardcoded o hint di Query Store impostati dall'utente, il feedback sulla stima di cardinalità non viene applicato per tale query. Per altre informazioni, vedere Hint per la query e Hint per il Query Store.

A partire da SQL Server 2022 (16.x), quando è abilitato Query Store per le repliche secondarie, il feedback stima di cardinalità non è compatibile con la replica per le repliche secondarie nei gruppi di disponibilità. Il feedback sulla stima di cardinalità attualmente offre solo vantaggi per le repliche primarie. In caso di failover, il feedback applicato alle repliche primarie o secondarie viene perso. Per altre informazioni, vedere Query Store per repliche secondarie.

Persistenza per feedback sulla stima di cardinalità (CE)

Si applica a: si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure, 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 sulla stima di cardinalità rende persistenti altri scenari oltre i modelli di query di ottimizzazione degli obiettivi di riga, come descritto in dettaglio negli scenari di feedback. Il feedback sulla stima di cardinalità gestisce attualmente gli scenari di selettività dei predicati usati dal modello di correlazione di stima di cardinalità e gli scenari di predicato 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 che operano nel livello di compatibilità del database 160 o superiore oppure l'hint QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n 160 e superiore e quando Query Store è abilitato per il database ed è in uno stato di "lettura/scrittura".

Problemi noti con feedback sulla stima di 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. È anche possibile notare un aumento costante del numero di oggetti Cache dei piani in uso che si avvicinano ai limiti della cache dei piani e cancellare manualmente la cache dei piani con tecniche come ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE o DBCC FREEPROCCACHE non forniscono assistenza. 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à. Anche se il feedback sulla stima di cardinalità analizza gli operatori di piano per una valutazione significativa del modello, esiste uno scenario in cui un piano a cui si fa riferimento può essere dereferenziato durante questa fase di analisi. Questa situazione impedisce che il piano venga rimosso dalla memoria usando l'algoritmo Utilizzati meno di recente (LRU) consueto. Il meccanismo LRU consente a SQL Server di applicare i criteri di rimozione 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 Centralizzazione cache dei piani.

Sintomo: il numero di voci in uso della cache dei piani e contrassegnate come dirty da piani SQL o 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 della cache dei piani usate dal sistema, è possibile usare gli esempi seguenti come punto nel tempo di visualizzazione del numero di voci della cache dei piani esistenti. 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. I rapporti di riscontri nella cache dei piani diminuiscono, così come il numero di compilazioni in relazione al numero di richieste batch/sec. Le query seguenti possono essere usate per monitorare il sistema nel tempo. Tenendo d'occhio il rapporto riscontri cache (dips imprevisti), gli oggetti cache in uso (aumento del conteggio a livelli che si avvicinano a 50.000 senza diminuire) e un rapporto inferiore al previsto richieste batch/sec rispetto a un aumento delle 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 aver disabilitato la funzionalità feedback sulla stima di cardinalità. Per disabilitare il feedback sulla stima di cardinalità (CE) a livello di database, usare la configurazione con ambito database CE_FEEDBACK. 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)