Odhad kardinality (CE) - zpětná vazba

Platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database, Azure SQL Managed Instance.

Od verze SQL Server 2022 (16.x) je zpětná vazba odhadu kardinality (CE) součástí inteligentní řady funkcí pro zpracování dotazů a řeší neoptimální plány provádění dotazů pro opakování dotazů, pokud tyto problémy vyplývají z nesprávných předpokladů modelu CE. Tento scénář pomáhá snížit rizika regrese související s výchozím CE při upgradu ze starších verzí databázového stroje.

Vzhledem k tomu, že žádná sada modelů a předpokladů CE nemůže pojmout širokou škálu zákaznických úloh a distribucí dat, zpětná vazba CE poskytuje přizpůsobitelné řešení na základě charakteristik modulu runtime dotazů. Zpětná vazba CE identifikuje a používá modelový předpoklad, který lépe vyhovuje danému dotazu a distribuci dat, aby se zlepšila kvalita plánu provádění dotazů. V současné době může zpětná vazba CE identifikovat operátory plánu, jejichž odhadovaný počet řádků a skutečný počet řádků jsou velmi odlišné. Zpětná vazba se použije, když dojde k významným chybám odhadu modelu a existuje funkční alternativní model, který se má vyzkoušet.

Další funkce zpětné vazby k dotazům najdete v zpětná vazba na udělení paměti a zpětná vazba na stupeň paralelismu (DOP).

Pochopit zpětnou vazbu k odhadu kardinality (CE)

Odhad kardinality (CE) je způsob, jakým optimalizátor dotazů dokáže odhadnout celkový počet řádků zpracovaných na každé úrovni plánu dotazu. Odhad kardinality v SQL Serveru je odvozen především z histogramů vytvořených při vytváření indexů nebo statistik, a to buď ručně, nebo automaticky. Sql Server někdy také používá informace o omezení a logické přepisy dotazů k určení kardinality.

Různé verze databázového stroje používají různé předpoklady modelu CE na základě způsobu distribuce a dotazování dat. Další informace naleznete ve verzích CE.

Implementace zpětné vazby pro odhad kardinality (Cardinality Estimation)

Zpětná vazba odhadu kardinality (CE) zjišťuje, které předpoklady modelu CE jsou v průběhu času optimální, a pak použije historicky nejpravděvější předpoklad:

1. Zpětná vazba CE identifikuje předpoklady související s modelem a vyhodnotí, jestli jsou přesné pro opakující se dotazy.

2. Pokud se předpoklad jeví jako nesprávný, následné spuštění stejného dotazu se testuje s plánem dotazu, který upraví předpoklad modelu CE, který má vliv, a ověří, jestli to pomůže. Nesprávnost identifikujeme tak, že se podíváme na skutečné a odhadované řádky od operátorů plánu. Ne všechny chyby mohou být opraveny variantami modelu, které jsou k dispozici ve zpětné vazbě CE.

3. Pokud zlepšuje kvalitu plánu, starý plán dotazu se nahradí plánem dotazu, který používá odpovídající nápovědu dotazu USE HINT , která upravuje model odhadu implementovaný prostřednictvím mechanismu nápovědy úložiště dotazů .

Pouze ověřená zpětná vazba je uchovávána. Zpětná vazba CE se pro tento dotaz nepoužívá, pokud upravený předpoklad modelu vede k regresi výkonu. V tomto kontextu se zrušený dotaz uživatele také považuje za regresi.

Scénáře zpětné vazby k odhadu kardinality (CE)

Zpětná vazba odhadu kardinality (CE) řeší vnímané regresní problémy vyplývající z nesprávných předpokladů modelu CE při použití výchozího modelu CE (CE120 nebo vyšší) a může selektivně používat různé předpoklady modelu. Mezi scénáře patří korelace, obsah spojení a cílový počet řádků optimalizátoru.

Korelace zpětné vazby pro odhad kardinality (CE)

Když Optimalizátor dotazů odhadne selektivitu predikátů v dané tabulce nebo zobrazení nebo počet řádků splňujících uvedený predikát, použije předpoklady korelačního modelu. Tyto předpoklady mohou být takové, že predikáty jsou:

• Plně nezávislé (výchozí hodnota pro CE70), kde kardinalita se počítá vynásobením selektivity všech predikátů.

• Částečně korelované (výchozí hodnota pro CE120 a vyšší), kde se kardinalita vypočítává pomocí úpravy algoritmu exponenciálního zpoždění, kdy je pořadí selektivit určeno od nejvíce po nejméně selektivní predikát.

• Plně korelovaná, kde se kardinalita vypočítá pomocí minimální selektivity pro všechny predikáty.

Následující příklad používá částečnou korelaci, pokud je kompatibilita databáze nastavená na 120 nebo vyšší:

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

Pokud je kompatibilita databáze nastavená na 160 a použije se výchozí korelace, pokusí se zpětná vazba CE přesunout korelaci ke správnému směru o krok najednou na základě toho, jestli byla odhadovaná kardinalita podceněna nebo přehodnocena v porovnání se skutečným počtem řádků. Úplnou korelaci použijte, pokud je skutečný počet řádků větší než odhadovaná kardinalita. Použijte úplnou nezávislost, pokud je skutečný počet řádků menší než odhadovaná kardinalita.

Další informace naleznete ve verzích CE.

Zahrnutí zpětnovazebního spojení odhadu kardinality (CE)

Když Optimalizátor dotazů odhadne selektivitu predikátů spojení a použitelných predikátů filtru, použije předpoklady modelu zahrnutí. Mezi tyto předpoklady patří:

• Jednoduché zahrnutí (výchozí hodnota pro CE70) předpokládá, že predikáty spojení jsou plně korelované, kde se nejprve vypočítá selektivita filtru a pak se selektivita spojení započítá.

• Základní zahrnutí (výchozí hodnota pro CE120 a vyšší) nepředpokládá žádnou korelaci mezi predikáty spojení a podřízenými filtry, kde se nejprve vypočítá selektivita spojení, a pak je selektivita filtru zahrnuta.

Následující příklad používá základní omezení, pokud je kompatibilita databáze nastavena na 120 nebo vyšší:

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

Další informace naleznete ve verzích CE.

Zpětná vazba k odhadu kardinality (CE) a požadavek na počet řádků v optimalizátoru dotazů

Když Optimalizátor dotazů odhaduje kardinalitu plánu provádění, obvykle předpokládá, že musí být zpracovány všechny opravňující řádky ze všech tabulek. Některé vzory dotazů ale způsobí, že optimalizátor dotazů vyhledá plán, který vrátí menší počet řádků, aby se snížil počet vstupně-výstupních operací. Pokud dotaz určuje cílový počet řádků (cíl řádku), které by mohly být očekávány za běhu pomocí klíčových slov TOPINEXISTS, FAST nápovědy dotazu nebo příkazu SET ROWCOUNT, tento cíl řádku se použije jako součást procesu optimalizace dotazu, například v následujícím příkladu:

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

Při použití plánu cíle řádku se odhadovaný počet řádků v plánu dotazu zmenší, protože Optimalizátor dotazů předpokládá, že aby bylo možné dosáhnout cíle řádku, bude potřeba zpracovat menší počet řádků.

I když je řídký cíl výhodnou strategií optimalizace pro určité vzory dotazů, pokud data nejsou rovnoměrně distribuovaná, může být procházeno více stránek, než je odhadnuto, což znamená, že řídký cíl je neefektivní. Zpětná vazba CE může zakázat skenování cíle řádku a umožnit vyhledávání, když je tato neefektivita zjištěna.

V plánu provádění neexistuje žádný atribut specifický pro zpětnou vazbu CE, ale pro nápovědu k úložišti dotazů je uvedený atribut. Hledejte, aby QueryStoreStatementHintSource to bylo CE feedback.

Úvahy ke zpětné vazbě k odhadu kardinality (CE)

• Pokud chcete povolit zpětnou vazbu odhadu kardinality (CE), povolte úroveň kompatibility databáze 160 pro databázi, ke které jste připojení při provádění dotazu. Úložiště dotazů musí být povolené a v režimu READ_WRITE pro každou databázi, ve které se používá zpětná vazba CE.

• Pokud chcete zakázat zpětnou vazbu CE na úrovni databáze, použijte CE_FEEDBACK databáze. Například v uživatelské databázi:

  ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;
  

• Pokud chcete zakázat zpětnou vazbu CE na úrovni dotazu, použijte nápovědu DISABLE_CE_FEEDBACK k dotazu.

Aktivita zpětné vazby systému CE je viditelná prostřednictvím query_feedback_analysis a query_feedback_validation XEvents.

Pomocí zobrazení katalogu sys.query_store_query_hints lze sledovat nápovědy nastavené zpětnou vazbou kompilátoru CE.

Informace o zpětné vazbě je možné sledovat pomocí zobrazení katalogu sys.query_store_plan_feedback .

Pokud má dotaz plán dotazu vynucený prostřednictvím úložiště dotazů, zpětná vazba CE se pro tento dotaz nepoužívá.

Pokud dotaz používá pevně zakódované rady dotazů nebo používá rady úložiště dotazů nastavené uživatelem, nebude pro tento dotaz použitá zpětná vazba CE. Další informace najdete v nápovědě k dotazům a nápovědě k úložišti dotazů.

Počínaje SQL Serverem 2022 (16.x) platí, že pokud je pro sekundární repliky povolen Query Store, zpětná vazba CE není citlivá na charakteristiky sekundárních replik ve skupinách dostupnosti. Zpětná vazba CE v současné době přináší výhody pouze primárním replikám. Při selhání dojde ke ztrátě zpětné vazby aplikované na primární nebo sekundární repliky. Úložiště dotazů je k dispozici u replik sekundární skupiny dostupnosti počínaje systémem SQL Server 2025 (17.x). Další informace najdete v tématu Úložiště dotazů pro sekundární repliky.

Přetrvávání pro zpětnou vazbu k odhadu kardinality (CE)

Platí pro: SQL Server 2022 (16.x) a novější verze, Azure SQL Database, Azure SQL Managed Instance.

Zpětná vazba k odhadu kardinality (CE) může detekovat scénáře, kdy by měla být zachována optimalizace cíle řádku, a uchovat tuto změnu jejím uložením v úložišti dotazů ve formě hintu Query Store. Nová optimalizace se používá pro budoucí spuštění dotazu. Zpětná vazba CE se vyskytuje i v jiných scénářích mimo dotazy s optimalizací cíle řádků, jak je podrobně popsáno ve scénářích zpětné vazby. Zpětná vazba CE v současné době zpracovává scénáře selektivního predikátu, které používá korelační model CE, a scénáře spojovacího predikátu, které zpracovává model zahrnutí CE.

Funkce byla představena v SQL Serveru 2022 (16.x), ale tato vylepšení výkonu jsou k dispozici pro dotazy, které pracují s úrovní kompatibility databáze 160 nebo vyšší, nebo s pobídkou QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ve verzi 160 a vyšší, a když je úložiště dotazů pro databázi povoleno a je ve stavu zápis a čtení.

Známé problémy se zpětnou vazbou ohledně odhadu kardinality (CE)

Problém	Datum zjištění	Stav	Datum vyřešení
Nízký výkon SQL Serveru po instalaci kumulativní aktualizace 8 pro SQL Server 2022 (16.x) za určitých podmínek. Pokud je povolená zpětná vazba CE, může dojít k dramatickému využití mezipaměti plánu spolu s nepředvídaným zvýšením využití CPU.	Prosinec 2023	Vyřešeno	22. dubna 2024 (CU 12)

Známé podrobnosti o problémech

Nízký výkon SQL Serveru po instalaci kumulativní aktualizace 8 pro SQL Server 2022 za určitých podmínek

Počínaje kumulativní aktualizací SQL Serveru 2022 (16.x) 8 může SQL Server vykazovat neočekávané zvýšení využití procesoru a paměti. Navíc může být také pozorováno zvýšení čekání na RESOURCE_SEMAPHORE_QUERY_COMPILE. Můžete si také všimnout stabilního nárůstu počtu objektů v mezipaměti plánu, které se používají a přibližují se k limitům mezipaměti plánu, a že ručně vymazat mezipaměť plánu pomocí technik jako ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE nebo DBCC FREEPROCCACHE neposkytuje žádnou pomoc. Toto chování bylo pozorováno pouze několika zákazníky.

Tento problém nemá vliv na všechny úlohy a závisí na počtu různých plánů, které byly generovány, a také na počtu plánů, které měly nárok na zapojení funkce zpětné vazby CE. I když zpětná vazba CE analyzuje operátory plánů pro významné chybné odhady modelu, existuje scénář, kdy může být plán, na který se odkazuje, v této fázi analýzy dereferencován. Tato situace zabrání odebrání plánu z paměti pomocí obvyklého algoritmu LRU (Least Recently Used). Mechanismus LRU je jedním ze způsobů, jakým SQL Server vynucuje zásady vyřazení plánu. SQL Server také odebere plány z paměti, pokud je systém pod zatížením paměti. Když se SQL Server pokusí odebrat plány, které byly nesprávně odvozeny, nemůže tyto plány odebrat z mezipaměti plánů, což způsobí, že mezipaměť bude dál růst. Rostoucí mezipaměť může začít způsobit další kompilace, které nakonec využívají více procesoru a paměti. Další informace najdete v tématu Vnitřní prvky mezipaměti plánu.

Příznak: Počet položek mezipaměti plánu , které se používají a jsou označeny jako špinavé z plánů SQL nebo plánů objektů, se v průběhu času zvyšuje na 50 000 nebo více. Pokud zjistíte položky mezipaměti plánu, které začínají dosahovat této úrovně spolu s neočekávaným nárůstem využití procesoru, váš systém může čelit tomuto problému. Oprava je k dispozici s kumulativní aktualizací SQL Serveru 2022 (16.x) 12. Viz KB5033663.

Pokud chcete monitorovat počet položek mezipaměti plánu, které váš systém používá, můžete následující příklady použít jako zobrazení počtu položek mezipaměti plánu, které existují. Například sledování počtu položek mezipaměti plánu, které jsou označeny jako špinavé, pravidelně v průběhu času je jedním ze způsobů, jak monitorovat tento jev.

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;

Další sada dotazů, které také poskytují stejné informace jako předchozí příklad, a zároveň umožňuje sledovat další metriky výkonu. Poměry zasažení vyrovnávací paměti plánu klesají, stejně jako počet kompilací v poměru k počtu dávkových požadavků za sekundu. Následující dotazy se dají použít ke sledování systému v průběhu času. Pozor na poměr přístupů do mezipaměti (neočekávané poklesy), používané objekty mezipaměti (zvýšení počtu na úrovně blížící se 50 000 bez snížení) a nižší než očekávaný poměr dávkových požadavků za sekundu oproti nárůstu počtu kompilací/s.

--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'
);

Řešení problému

Pokud váš systém nadále dochází k příznakům, které byly popsány dříve, po instalaci kumulativní aktualizace 12 KB5033663, může být funkce zpětné vazby CE zakázána na úrovni databáze.

K uvolnění paměti mezipaměti plánu, kterou tento problém převzal, je vyžadováno restartování instance SQL Serveru. Tuto akci restartování je možné provést po zakázání funkce zpětné vazby CE. Pokud chcete zakázat zpětnou vazbu CE na úrovni databáze, použijte CE_FEEDBACK databáze. Například v uživatelské databázi:

ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

Zpětná vazba a hlášení problémů

Pokud máte zpětnou vazbu nebo dotazy, pošlete e-mail CEFfeedback@microsoft.com

Související obsah

• Zpětná vazba odhadu kardinality v SQL Serveru 2022
• inteligentní zpracování dotazů v databázích SQL
• Funkce inteligentního zpracování dotazů podrobně
• Odhad kardinality (SQL Server)
• PŘEKONFIGUROVAT (Transact-SQL)
• Monitorování a ladění výkonu
• ÚPRAVA KONFIGURACE S ROZSAHEM DATABÁZE (Transact-SQL)