Odhad kardinality (SQL Server)

Platí pro:SQL ServerAzure SQL DatabaseSpravovaná instance Azure SQLDatabáze SQL v Microsoft Fabric

Optimalizátor dotazů SQL Serveru je optimalizátor dotazů založený na nákladech. To znamená, že vybírá plány dotazů s nejnižšími odhadovanými náklady na zpracování. Optimalizátor dotazů určuje náklady na provedení plánu dotazů na základě dvou hlavních faktorů:

• Celkový počet řádků zpracovaných na každé úrovni plánu dotazu, který se označuje jako kardinalita plánu.
• Nákladový model algoritmu diktovaného operátory použitými v dotazu.

První faktor, kardinalita, se používá jako vstupní parametr druhého faktoru, modelu nákladů. Vylepšená kardinalita proto vede k lepším odhadovaným nákladům a rychlejším plánům provádění.

Odhad kardinality (CE) na 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.

V následujících případech sql Server nedokáže přesně vypočítat kardinality. To způsobí nepřesné výpočty nákladů, které můžou způsobit neoptimální plány dotazů. Zabránění těmto konstruktorům v dotazech může zlepšit výkon dotazů. Někdy jsou možné alternativní formulace dotazů nebo jiné míry a jsou uvedené:

• Dotazy s predikáty, které používají relační operátory mezi různými sloupci stejné tabulky.
• Dotazy s predikáty, které používají operátory, a platí některá z následujících podmínek:
  • Na obou stranách operátorů nejsou žádné statistiky.
  • Rozdělení hodnot ve statistikách není jednotné, ale dotaz hledá vysoce selektivní sadu hodnot. Tato situace může být obzvláště platná, pokud je operátor něčím jiným než operátorem rovnosti (=).
  • Predikát používá operátor porovnání nerovnající se (!=) nebo NOT logický operátor.
• Dotazy, které používají některou z integrovaných funkcí SQL Serveru nebo skalární funkci definovanou uživatelem, jejíž argument není konstantní hodnotou.
• Dotazy, které zahrnují spojování sloupců prostřednictvím aritmetických operátorů nebo operátorů pro spojování řetězců.
• Dotazy, které porovnávají proměnné, jejichž hodnoty nejsou známé při kompilaci a optimalizaci dotazu.

Tento článek ukazuje, jak můžete posoudit a zvolit nejlepší konfiguraci CE pro váš systém. Většina systémů přináší nejnovější verzi CE, protože je nejpřesnější. CE předpovídá, kolik řádků dotaz pravděpodobně vrátí. Predikce kardinality používá optimalizátor dotazů k vygenerování optimálního plánu dotazu. S přesnějšími odhady může optimalizátor dotazů obvykle provádět lepší úlohu vytváření optimálního plánu dotazů.

Váš aplikační systém může mít pravděpodobně důležitý dotaz, jehož plán se změní na pomalejší plán kvůli změnám v CE v různých verzích. Máte techniky a nástroje pro identifikaci dotazu, který kvůli problémům s CE provádí pomaleji. A máte možnosti, jak řešit problémy s výkonem.

Verze CE

V roce 1998 byla hlavní aktualizace CE součástí SQL Serveru 7.0, pro kterou byla úroveň kompatibility 70. Tato verze modelu CE je nastavená na čtyři základní předpoklady:

• Nezávislost: Distribuce dat v různých sloupcích se předpokládají nezávisle na sobě, pokud nejsou k dispozici a použitelné informace o korelaci.

• Uniformita: Jedinečné hodnoty jsou rovnoměrně rozloženy a že všechny mají stejnou frekvenci. Přesněji řečeno, v každém kroku histogramu jsou jedinečné hodnoty rovnoměrně rozložené a každá hodnota má stejnou frekvenci.

• Uzavření (jednoduché): Uživatelé dotazují data, která existují. U spojení rovnosti mezi dvěma tabulkami započítejte selektivitu¹ v každém vstupním histogramu před spojením histogramů, aby se odhadla selektivita spojení.

• Inkluze: Pro predikáty filtru, kde Column = Constantse předpokládá, že konstanta skutečně existuje pro přidružený sloupec. Pokud je odpovídající krok histogramu neprázdný, předpokládá se, že jedna z jedinečných hodnot kroku odpovídá hodnotě z predikátu.

  ¹ počet řádků, který splňuje predikát.

Další aktualizace začaly s SQL Serverem 2014 (12.x), což znamená úrovně kompatibility 120 a vyšší. Aktualizace CE pro úrovně 120 a vyšší zahrnují aktualizované předpoklady a algoritmy, které dobře fungují na moderních datových skladech a v úlohách OLTP. Z předpokladů CE 70 se od CE 120 změnily následující předpoklady modelu:

• Nezávislost se stává korelací: Kombinace různých hodnot sloupců nemusí nutně být nezávislá. Může se to podobat dotazování na data z reálného života.
• Jednoduchá omezení se stane základním omezením: Uživatelé můžou zadávat dotazy na data, která neexistují. Například pro spojení podle rovnosti mezi dvěma tabulkami používáme histogramy výchozích tabulek k odhadu selektivity spojení a zohledňujeme selektivitu predikátů.

K posouzení verze CE použijte úložiště dotazů

Počínaje SQL Serverem 2016 (13.x) je úložiště dotazů užitečným nástrojem pro zkoumání výkonu dotazů. Jakmile je úložiště dotazů povolené, začne sledovat výkon dotazů v průběhu času, i když se plány provádění změní. Sledujte Query Store kvůli dotazům s vysokými náklady nebo zhoršujícím se výkonem. Další informace najdete v tématu Monitorování výkonu pomocí úložiště dotazů.

Pokud se připravujete na upgrade na SQL Server nebo zvýšení úrovně kompatibility databáze v jakékoli platformě SQL Serveru, zvažte upgrade databází pomocí Pomocníka pro ladění dotazů, který může pomoct porovnat výkon dotazů ve dvou různých úrovních kompatibility.

Important

Ujistěte se, že je úložiště dotazů správně nakonfigurované pro vaši databázi a úlohu. Další informace najdete v tématu Osvědčené postupy pro monitorování úloh pomocí úložiště dotazů.

Použijte rozšířené události k posouzení verze CE

Další možností sledování procesu odhadu kardinality je použití rozšířené události s názvem query_optimizer_estimate_cardinality. Následující ukázka kódu Transact-SQL běží na SQL Serveru. Zapíše soubor .xel do C:\Temp\ (i když můžete změnit cestu). Když otevřete soubor .xel v Management Studiu, jeho podrobné informace se zobrazí uživatelsky přívětivým způsobem.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;
go

CREATE EVENT SESSION Test_the_CE_qoec_1
ON SERVER
ADD EVENT sqlserver.query_optimizer_estimate_cardinality
 (
 ACTION (sqlserver.sql_text)
  WHERE (
  sql_text LIKE '%yourTable%'
  and sql_text LIKE '%SUM(%'
  )
 )
ADD TARGET package0.asynchronous_file_target
 (SET
  filename = 'c:\temp\xe_qoec_1.xel',
  metadatafile = 'c:\temp\xe_qoec_1.xem'
 );
GO

ALTER EVENT SESSION Test_the_CE_qoec_1
ON SERVER
STATE = START;  --STOP;
GO

Note

Událost sqlserver.query_optimizer_estimate_cardinality není dostupná pro Azure SQL Database.

Informace o rozšířených událostech přizpůsobené službě SQL Database naleznete v tématu Rozšířené události ve službě SQL Database.

Postup posouzení verze CE

V dalším kroku můžete vyhodnotit, jestli některé z nejdůležitějších dotazů v nejnovější verzi CE fungují hůře. Některé kroky se provádějí spuštěním ukázky kódu uvedené v předchozí části.

1. Otevřete SQL Server Management Studio (SSMS). Ujistěte se, že je databáze SQL Serveru nastavená na nejvyšší dostupnou úroveň kompatibility.

2. Proveďte následující předběžné kroky:

   1. Otevřete SQL Server Management Studio (SSMS).

   2. Spusťte Transact-SQL a ujistěte se, že je databáze SQL Serveru nastavená na nejvyšší dostupnou úroveň kompatibility.

   3. Ujistěte se, že je LEGACY_CARDINALITY_ESTIMATION vaše databáze nakonfigurovaná OFF.

   4. Vymažte úložiště dotazů. V databázi se ujistěte, že je úložiště dotazů zapnuté.

   5. Spusťte příkaz: SET NOCOUNT OFF;

3. Spusťte příkaz: SET STATISTICS XML ON;

4. Spusťte důležitý dotaz.

5. V podokně výsledků si na kartě Zprávy všimněte skutečného počtu ovlivněných řádků.

6. V podokně výsledků na kartě Výsledky poklikejte na buňku obsahující statistiku ve formátu XML. Zobrazí se grafický plán dotazu.

7. Pravým tlačítkem myši klikněte na první pole v plánu grafického dotazu a pak vyberte Vlastnosti.

8. Pro pozdější porovnání s jinou konfigurací si poznamenejte hodnoty následujících vlastností:

   • KardinalitaEstimationModelVersion.

   • Odhadovaný počet řádků

   • Odhadované vstupně-výstupní náklady a několik podobných odhadovaných vlastností, které zahrnují skutečný výkon, a ne předpovědi počtu řádků.

   • Logická operace a fyzická operace. Paralelismus je dobrá hodnota.

   • Skutečný režim spuštění. Batch je dobrá hodnota, lepší než řádek.

9. Porovnejte odhadovaný počet řádků se skutečným počtem řádků. Je CE nepřesná o 1% (vysoká nebo nízká), nebo o 10%?

10. Spusťte SET STATISTICS XML OFF;.

11. Spuštěním Transact-SQL snižte úroveň kompatibility databáze o jednu úroveň (například z 130 až 120).

12. Znovu spusťte všechny kroky, které nejsou předběžné.

13. Porovnejte hodnoty vlastností CE ze dvou spuštění.

    • Je procento nepřesnosti pod nejnovějším CE menší než pod starším CE?

14. Nakonec porovnejte různé hodnoty vlastností výkonu z obou běhů.

    • Použil váš dotaz jiný plán v rámci dvou rozdílných odhadů CE?

    • Běžel dotaz v nejnovější verzi CE pomaleji?

    • Pokud váš dotaz nepoběží lépe a s jiným plánem ve starší verzi CE, téměř jistě budete chtít nejnovější CE.

    • Pokud ale váš dotaz běží s rychlejším plánem ve starší verzi CE, zvažte vynucení systému, aby používal rychlejší plán a ignoroval CE. Tímto způsobem můžete mít nejnovější verzi CE pro všechno, zatímco v jednom ojedinělém případě zachováte rychlejší plán.

Postup aktivace nejlepšího plánu dotazů

Předpokládejme, že u verze CE 120 nebo vyšší se pro váš dotaz vygeneruje méně efektivní plán dotazů. Tady je několik možností, jak aktivovat lepší plán seřazený od největšího rozsahu po nejmenší:

• Úroveň kompatibility databáze můžete nastavit na hodnotu nižší než nejnovější dostupnou pro celou databázi.

  • Například nastavení úrovně kompatibility 110 nebo nižší aktivuje CE 70, ale všechny dotazy podléhají předchozímu modelu CE.

  • Kromě toho nastavení nižší úrovně kompatibility také vynechá řadu vylepšení optimalizátoru dotazů pro nejnovější verze a ovlivňuje všechny dotazy na databázi.

• Můžete použít LEGACY_CARDINALITY_ESTIMATION možnost konfigurace s vymezeným oborem databáze, pokud chcete, aby celá databáze používala starší CE a zachovala další vylepšení optimalizátoru dotazů.

• Můžete použít LEGACY_CARDINALITY_ESTIMATION jako nápovědu k dotazu, aby jeden dotaz použil starší CE, zatímco si zachová další vylepšení optimalizátoru dotazů.

• Pomocí funkce rady LEGACY_CARDINALITY_ESTIMATION úložiště dotazů můžete vynutit použití starší verze CE pro tento jediný dotaz, aniž by došlo ke změně dotazu.

• Vynutit jiný plán pomocí Query Store.

Úroveň kompatibility databáze

Databázi můžete zajistit na určité úrovni pomocí následujícího Transact-SQL kódu pro úroveň kompatibility ALTER DATABASE (Transact-SQL).

Important

Čísla verzí databázového stroje pro SQL Server a Azure SQL Database nejsou vzájemně srovnatelná a jsou spíše interními čísly buildů pro tyto samostatné produkty. Databázový stroj pro Azure SQL Server je založený na stejném základu kódu jako databázový stroj SQL Serveru. Nejdůležitější je, že databázový stroj ve službě Azure SQL Database má vždy nejnovější bity databázového stroje SQL. Verze 12 služby Azure SQL Database je novější než verze 15 SQL Serveru. Od listopadu 2019 je ve službě Azure SQL Database výchozí úroveň kompatibility 150 pro nově vytvořené databáze. Microsoft neaktualizuje úroveň kompatibility databáze pro existující databáze. Je na zákaznících, aby to udělali podle vlastního uvážení.

SELECT ServerProperty('ProductVersion');
GO

SELECT d.name, d.compatibility_level
FROM sys.databases AS d
WHERE d.name = 'yourDatabase';
GO

U existujících databází běžících na nižších úrovních kompatibility platí, že pokud aplikace nepotřebuje používat vylepšení, která jsou k dispozici pouze na vyšší úrovni kompatibility databáze, je platným přístupem k zachování předchozí úrovně kompatibility databáze. Pro novou vývojovou práci nebo v případě, že existující aplikace vyžaduje použití nových funkcí, jako je inteligentní zpracování dotazů v databázích SQL, a také některé nové transact-SQL, naplánujte upgrade úrovně kompatibility databáze na nejnovější dostupnou úroveň. Další informace najdete v tématu Úrovně kompatibility a upgrady databázového stroje.

Caution

Před změnou úrovně kompatibility databáze zkontrolujte úroveň kompatibility ALTER DATABASE (Transact-SQL).

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

Pro databázi SQL Serveru nastavenou na úrovni kompatibility 120 nebo vyšší, aktivace příznaku trasování 9481 vynutí systém používat CE verze 70.

Nástroj pro posouzení kardinality starší verze

Pro databázi SQL Serveru nastavenou na úrovni kompatibility 120 a vyšší je možné pomocí konfigurace ALTER DATABASE SCOPED aktivovat starší estimátor kardinality (CE verze 70).

ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = ON;
GO

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
GO

Upravit dotaz, aby používal hint

Počínaje SQL Serverem 2016 (13.x) SP1 upravte dotaz tak, aby používal Query HintUSE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

Nastavení nápovědy k úložišti dotazů

Dotazy je možné vynutit, aby používaly starší estimátor kardinality bez úpravy dotazu pomocí nápovědy k úložišti dotazů.

1. Identifikujte dotaz v zobrazeních katalogu úložiště dotazů sys.query_store_query_text a sys.query_store_query . Například vyhledejte spuštěný dotaz podle fragmentu textu:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt
   INNER JOIN sys.query_store_query q ON
   qt.query_text_id = q.query_text_id
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'
   AND query_sql_text not like N'%query_store%';
   

2. Následující příklad použije příznak Query Store, který vynutí starší odhadce kardinality na query_id 39 beze změny dotazu.

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Note

Další informace najdete v tématu Nápovědy k úložišti dotazů (Preview). V současné době je tato funkce dostupná jenom ve službě Azure SQL Database.

Jak vynutit konkrétní plán dotazu

Pro nejlepší kontrolu byste mohli vynutit , aby systém používal plán vygenerovaný s CE 70 během testování. Po připnutí upřednostňovaného plánu můžete nastavit celou databázi tak, aby používala nejnovější úroveň kompatibility a úroveň CE. Tato možnost je detailně rozpracována dále.

Úložiště dotazů nabízí různé způsoby, jak můžete vynutit, aby systém používal konkrétní plán dotazu:

• Spusťte sys.sp_query_store_force_plan.

• V aplikaci SQL Server Management Studio (SSMS) rozbalte uzel úložiště dotazů , klikněte pravým tlačítkem myši na uzly s nejvyšším využitím prostředků a pak vyberte Zobrazit uzly s nejvyšším využitím prostředků. Na displeji se zobrazují tlačítka s popiskem Vynutit plán (Force Plan) a Zrušit vynucení plánu (Unforce Plan).

Další informace o úložišti dotazů najdete v tématu Monitorování výkonu pomocí úložiště dotazů.

Konstantní skládání a vyhodnocování výrazů během odhadu kardinality

Databázový stroj vyhodnocuje některé konstantní výrazy na začátku, aby se zlepšil výkon dotazů. To se označuje jako konstantní skládání. Konstanta je Transact-SQL literál, například 3, 'ABC', '2005-12-31', 1.0e3nebo 0x12345678. Další informace naleznete v tématu Konstantní posouvání.

Kromě toho se některé výrazy, které nejsou optimalizovány metodou skládání konstant, ale jejichž argumenty jsou známé v době kompilace, ať už jsou to parametry nebo konstanty, vyhodnocují pomocí odhadu velikosti sady výsledků (kardinality), který je součástí optimalizátoru dotazů během optimalizace. Další informace naleznete v tématu Vyhodnocení výrazu.

Osvědčené postupy: Použití konstantního posouvání a vyhodnocování výrazů v době kompilace pro generování optimálních plánů dotazů

Abyste měli jistotu, že vygenerujete optimální plány dotazů, doporučujeme navrhovat dotazy, uložené procedury a dávky, aby optimalizátor dotazů mohl přesně odhadnout selektivitu podmínek v dotazu na základě statistik o distribuci dat. V opačném případě musí optimalizátor dotazů použít výchozí odhad při odhadu selektivity.

Abyste měli jistotu, že nástroj pro posouzení kardinality optimalizátoru dotazů poskytuje dobré odhady, měli byste nejprve zajistit, aby AUTO_CREATE_STATISTICS byly AUTO_UPDATE_STATISTICS možnosti a SET databáze ON (výchozí nastavení) nebo že jste ručně vytvořili statistiky pro všechny sloupce odkazované v podmínce dotazu. Když pak navrhujete podmínky v dotazech, proveďte následující kroky, pokud je to možné:

• Vyhněte se použití místních proměnných v dotazech. Místo toho v dotazu použijte parametry, literály nebo výrazy.

• Omezte použití operátorů a funkcí vložených do dotazu, který obsahuje parametr, na ty, které jsou uvedeny podle vyhodnocení výrazů v době kompilace pro odhad kardinality.

• Ujistěte se, že výrazy, které obsahují pouze konstanty, v podmínce dotazu jsou buď konstantně složitelné, nebo je možné je vyhodnotit v době kompilace.

• Pokud k vyhodnocení výrazu, který se má použít v dotazu, musíte použít místní proměnnou, zvažte jeho vyhodnocení v jiném oboru než v dotazu. Může být například užitečné provést jednu z následujících možností:

  • Předejte hodnotu proměnné uložené proceduře, která obsahuje dotaz, který chcete vyhodnotit, a použijte parametr procedury místo místní proměnné.

  • Vytvořte řetězec, který obsahuje dotaz založený částečně na hodnotě místní proměnné, a pak řetězec spusťte pomocí dynamického SQL (EXEC nebo nejlépe sp_executesql).

  • Parametrizujte dotaz a spusťte ho pomocí sp_executesqla předejte hodnotu proměnné jako parametr dotazu.

Příklady vylepšení CE

Tato část popisuje ukázkové dotazy, které využívají vylepšení implementovaná v CE v nedávných verzích. Jedná se o základní informace, které nevyžadují konkrétní akci na vaší straně.

Příklad A. CE rozumí, že maximální hodnota může být vyšší než tehdy, kdy byly statistiky naposledy shromážděny.

Předpokládejme, že naposledy byly statistiky shromážděny pro OrderTable dne 2016-04-30, kdy maximum OrderAddedDate bylo 2016-04-30. Verze CE 120 (a vyšší) rozumí tomu, že sloupce ve OrderTablesloupcích, které mají vzestupná data, můžou mít hodnoty větší než maximum zaznamenané statistikou. Toto porozumění zlepšuje plán dotazů pro Transact-SQL SELECT příkazy, jako je následující.

SELECT CustomerId, OrderAddedDate
FROM OrderTable
WHERE OrderAddedDate >= '2016-05-01';

Příklad B. CE rozumí tomu, že filtrované predikáty ve stejné tabulce jsou často korelovány.

V následujícím příkladu SELECT vidíme filtrované predikáty na Model a ModelVariant. Intuitivně chápeme, že když Model je Xbox, existuje šance ModelVariant , že je one, protože Xbox má variantu s názvem One.

Počínaje verzí CE 120 SQL Server rozumí korelaci mezi dvěma sloupci ve stejné tabulce Model a ModelVariant. CE zpřesní odhad počtu řádků vrácených dotazem a optimalizátor dotazů vygeneruje optimaličtější plán.

SELECT Model, Purchase_Price
FROM dbo.Hardware
WHERE Model = 'Xbox' AND
ModelVariant = 'Series X';

Příklad C. CE už nepředpokládá žádnou korelaci mezi filtrovanými predikáty z různých tabulek.

Rozsáhlý nový výzkum moderních úloh a skutečných obchodních dat ukazuje, že predikátové filtry z různých tabulek obvykle vzájemně nekorelují. V následujícím dotazu CE předpokládá, že neexistuje žádná korelace mezi s.type a r.date. Proto CE sníží odhad počtu vrácených řádků.

SELECT s.ticket, s.customer, r.store
FROM dbo.Sales AS s
CROSS JOIN dbo.Returns AS r
WHERE s.ticket = r.ticket AND
s.type = 'toy' AND
r.date = '2016-05-11';

Související obsah

• odhad kardinality (CE) zpětné vazby
• Monitorování a ladění výkonu
• optimalizace plánů dotazů pomocí nástroje pro posouzení kardinality SQL Serveru 2014
• Rady dotazů (Transact-SQL)
• USE HINT pro dotazy
• Upgrade databází pomocí Pomocníka pro ladění dotazů
• Monitorování výkonu s využitím úložiště dotazů
• Průvodce architekturou zpracování dotazů
• Pokyny pro úložiště dotazů
• inteligentní zpracování dotazů v databázích SQL
• Nastavení příznaků trasování pomocí DBCC TRACEON (Transact-SQL)
• sys.query_store_query_hints