CREATE STATISTICS (Transact-SQL)

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed Instancekoncový bod azure Synapse AnalyticsSQL Analytics v Microsoft FabricWarehouse v Microsoft Fabric

Vytvoří statistiku optimalizace dotazů pro jeden nebo více sloupců tabulky, indexovaného zobrazení nebo externí tabulky. Pro většinu dotazů už optimalizátor dotazů generuje nezbytné statistiky pro vysoce kvalitní plán dotazů; V několika případech je potřeba vytvořit další statistiky s návrhem CREATE STATISTICS dotazu nebo ho upravit, aby se zlepšil výkon dotazů.

Další informace najdete v tématu Statistika.

Poznámka:

Další informace o statistikách v Microsoft Fabric najdete v tématu Statistiky v datových skladech Fabric.

Transact-SQL konvence syntaxe

Syntaxe

Syntaxe pro SQL Server, Azure SQL Database a Spravovanou instanci Azure SQL

-- Create statistics on an external table
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WITH FULLSCAN ] ;

-- Create statistics on a regular table or indexed view
CREATE STATISTICS statistics_name
ON { table_or_indexed_view_name } ( column [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH
        [ FULLSCAN
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | SAMPLE number { PERCENT | ROWS }
            [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]
          | <update_stats_stream_option> [ , ...n ]
        [ [ , ] NORECOMPUTE ]
        [ [ , ] INCREMENTAL = { ON | OFF } ]
        [ [ , ] MAXDOP = max_degree_of_parallelism ]
        [ [ , ] AUTO_DROP = { ON | OFF } ]
        ]
    ];

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric_constant ]

Syntaxe pro Azure Synapse Analytics a Platform Platform System (PDW).

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name  [ , ...n ] )
    [ WHERE <filter_predicate> ]
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

<filter_predicate> ::=
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant , ...)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<

Syntaxe pro Microsoft Fabric.

CREATE STATISTICS statistics_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( column_name )
    [ WITH {
           FULLSCAN
           | SAMPLE number PERCENT
      }
    ]
[ ; ]

Argumenty

statistics_name

Název statistiky, která se má vytvořit.

table_or_indexed_view_name

Název tabulky, indexovaného zobrazení nebo externí tabulky, pro kterou chcete vytvořit statistiku. Pokud chcete vytvořit statistiku pro jinou databázi, zadejte kvalifikovaný název tabulky.

column [ ,... n ]

Jeden nebo více sloupců, které se mají zahrnout do statistiky. Sloupce by měly být v pořadí priority zleva doprava. K vytvoření histogramu se používá pouze první sloupec. Všechny sloupce se používají pro statistiky korelace mezi sloupci označované jako hustoty.

Můžete zadat libovolný sloupec, který lze zadat jako sloupec s klíčem indexu, s následujícími výjimkami:

• Nelze zadat sloupce XML, fulltext a FILESTREAM.

• Počítané sloupce lze zadat pouze v případě, že ARITHABORT jsou ONnastavení databáze QUOTED_IDENTIFIER .

• Sloupce uživatelem definovaného typu CLR je možné zadat, pokud typ podporuje binární řazení. Počítané sloupce definované jako volání metody sloupce typu definované uživatelem je možné zadat, pokud jsou metody označeny deterministicky.

WHERE <filter_predicate>

Určuje výraz pro výběr podmnožina řádků, které se mají zahrnout při vytváření objektu statistiky. Statistiky vytvořené pomocí predikátu filtru se nazývají filtrované statistiky. Predikát filtru používá jednoduchou porovnávací logiku a nemůže odkazovat na počítaný sloupec, sloupec UDT, sloupec prostorového datového typu nebo sloupec datového typu HierarchyID . Porovnání používající NULL literály nejsou u relačních operátorů povolená. Místo toho použijte operátory IS NULL a IS NOT NULL.

Tady je několik příkladů predikátů filtru pro tabulku Production.BillOfMaterials:

• WHERE StartDate > '20000101' AND EndDate <= '20000630'
• WHERE ComponentID IN (533, 324, 753)
• WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Další informace o predikátech filtru naleznete v tématu Vytvoření filtrovaných indexů.

FULLSCAN

Platí pro: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 a novější verze

Výpočet statistiky prohledáváním všech řádků FULLSCAN a SAMPLE 100 PERCENT mají stejné výsledky. FULLSCAN nelze s možností SAMPLE použít.

Pokud tento parametr vynecháte, SQL Server použije k vytvoření statistiky vzorkování a určí velikost vzorku, která se vyžaduje k vytvoření vysoce kvalitního plánu dotazů.

Ve službě Warehouse v Microsoft Fabric se podporují pouze jednosloupce FULLSCAN a jednosloupce SAMPLE. Pokud není zahrnuta žádná možnost, SAMPLE vytvoří se statistika.

VZOROVÉ číslo { PERCENT | ŘÁDKY }

Určuje přibližné procento nebo počet řádků v tabulce nebo indexované zobrazení pro optimalizátor dotazů, které se mají použít při vytváření statistik. Pro PERCENT, číslo může být od 0 do 100 a pro ROWS, číslo může být od 0 do celkového počtu řádků. Skutečné procento nebo počet řádků, které vzorky optimalizátoru dotazů nemusí odpovídat zadanému procentu nebo číslu. Optimalizátor dotazů například prohledá všechny řádky na datové stránce.

SAMPLE je užitečná ve zvláštních případech, kdy plán dotazu na základě výchozího vzorkování není optimální. Ve většině situací není nutné zadávat SAMPLE , protože optimalizátor dotazů už používá vzorkování a ve výchozím nastavení určuje statisticky významnou velikost vzorku, jak je potřeba k vytvoření vysoce kvalitních plánů dotazů.

SAMPLE nelze použít s možností FULLSCAN. Pokud SAMPLE nebo FULLSCAN nejsou zadané, optimalizátor dotazů použije vzorkovaná data a ve výchozím nastavení vypočítá velikost vzorku.

Doporučujeme zadat 0 PERCENT nebo 0 ROWS. Pokud 0 PERCENT je objekt statistiky vytvořen nebo 0 ROWS je zadán, ale neobsahuje data statistiky.

Ve službě Warehouse v Microsoft Fabric se podporují pouze jednosloupce FULLSCAN a jednosloupce SAMPLE. Pokud není zahrnuta žádná možnost, FULLSCAN vytvoří se statistika.

PERSIST_SAMPLE_PERCENT = { ON | VYPNUTO }

Pokud ONstatistika zachová procento vzorkování pro následné aktualizace, které explicitně nezadávají procento vzorkování. Pokud OFFse procento vzorkování statistik resetuje na výchozí vzorkování v následných aktualizacích, které explicitně nezadávají procento vzorkování. Výchozí hodnota je OFF.

Poznámka:

Pokud je tabulka zkrácená, všechny statistiky založené na zkrácené haldě nebo B-tree (HoBT) se vrátí k použití výchozího procenta vzorkování. Podobně platí, že pokud jsou statistiky u objektu aktualizovány bez řádků, vrátí se k použití výchozího procenta vzorkování, i když PERSIST_SAMPLE_PERCENT byla dříve nakonfigurována.

STATS_STREAM = stats_stream

Určeno pouze pro informační účely. Nepodporováno Budoucí kompatibilita není zaručena.

NORECOMPUTE

Zakažte možnost AUTO_STATISTICS_UPDATEautomatické aktualizace statistiky pro statistics_name. Pokud je tato možnost zadaná, optimalizátor dotazů dokončí všechny probíhající aktualizace statistik pro statistics_name a zakáže budoucí aktualizace.

Pokud chcete aktualizace statistik znovu povolit, odeberte statistiku pomocí funkce DROP STATISTICS a spusťte CREATE STATISTICS ji bez NORECOMPUTE možnosti.

Výstraha

Pokud zakážete automatickou aktualizaci statistik, může zabránit optimalizaci dotazů v výběru optimálních plánů provádění pro dotazy, které zahrnují tabulku. Tuto možnost byste měli používat střídmě a pouze kvalifikovaným správcem databáze.

Další informace o této AUTO_STATISTICS_UPDATE možnosti naleznete v tématu ALTER DATABASE SET možnosti. Další informace o zakázání a opětovném povolení aktualizací statistik najdete v tématu Statistika.

INCREMENTAL = { ON | VYPNUTO }

platí pro: SQL Server 2014 (12.x) a novější verze

Když ONse vytvoří statistika pro jednotlivé oddíly. Když OFFse statistiky zkombinují pro všechny oddíly. Výchozí hodnota je OFF.

Pokud statistiky jednotlivých oddílů nejsou podporované, vygeneruje se chyba. Přírůstkové statistiky nejsou podporované pro následující typy statistik:

• Statistiky vytvořené s indexy, které nejsou v souladu se základní tabulkou
• Statistiky vytvořené v sekundárních databázích s možností čtení AlwaysOn
• Statistiky vytvořené pro databáze jen pro čtení
• Statistiky vytvořené pro filtrované indexy
• Statistiky vytvořené v zobrazeních
• Statistiky vytvořené v interních tabulkách
• Statistiky vytvořené pomocí prostorových indexů nebo indexů XML

MAXDOP = max_degree_of_parallelism

Platí pro: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 a novější verze

Přepíše maximální stupeň konfigurace paralelismu během statistické operace. Další informace najdete v tématu Konfigurace maximálního stupně paralelismu (možnost konfigurace serveru). Slouží MAXDOP k omezení počtu procesorů používaných při paralelním provádění plánu. Maximum je 64 procesorů.

max_degree_of_parallelism může být:

• 1: Potlačí generování paralelního plánu.
• >1: Omezuje maximální počet procesorů použitých v paralelní operaci indexu na zadané číslo.
• 0 (výchozí): Používá skutečný počet procesorů nebo méně procesorů na základě aktuální systémové úlohy.

update_stats_stream_option

Určeno pouze pro informační účely. Nepodporováno Budoucí kompatibilita není zaručena.

AUTO_DROP = { ON | VYPNUTO }

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

Před SQL Serverem 2022 (16.x), pokud jsou statistiky ručně vytvořeny nástrojem uživatele nebo třetí strany v uživatelské databázi, mohou tyto statistické objekty blokovat nebo narušit změny schématu, které zákazník může chtít.

Počínaje SQL Serverem 2022 (16.x) AUTO_DROP je tato možnost ve výchozím nastavení povolená pro všechny nové a migrované databáze. Tato AUTO_DROP vlastnost umožňuje vytváření statistických objektů v režimu tak, že následná změna schématu není blokována statistickým objektem, ale statistiky se podle potřeby zahodí. Tímto způsobem se ručně vytvořené statistiky s povoleným chováním AUTO_DROP chovají jako automaticky vytvořené statistiky.

Poznámka:

Při pokusu o nastavení nebo zrušení nastavení vlastnosti Auto_Drop u automaticky vytvořených statistik může dojít k chybám. Automaticky vytvořené statistiky vždy používají automatické odstraňování. Některé zálohy při obnovení můžou mít tuto vlastnost nastavenou nesprávně, dokud se příště neaktualizuje objekt statistiky (ručně nebo automaticky). Automaticky vytvořené statistiky se ale vždy chovají jako automatické poklesy statistiky. Při obnovování databáze na SQL Server 2022 (16.x) z předchozí verze se doporučuje provést sp_updatestats v databázi a nastavit správná metadata pro funkci statistiky AUTO_DROP .

Další informace najdete v tématu AUTO_DROP možnost.

Dovolení

Vyžaduje jedno z těchto oprávnění:

• ALTER TABLE
• Uživatel je vlastníkem tabulky.
• Členství v db_ddladmin pevné databázové roli

Poznámky

SQL Server může před sestavením statistiky seřadit tempdb vzorkované řádky.

Statistiky pro externí tabulky

Při vytváření statistik externí tabulky SQL Server naimportuje externí tabulku do dočasné tabulky SQL Serveru a pak vytvoří statistiku. Pro statistiky vzorků se importují pouze řádky se vzorky. Pokud máte velkou externí tabulku, je rychlejší použít výchozí vzorkování místo možnosti úplné kontroly.

Pokud externí tabulka používá DELIMITEDTEXT, CSV, PARQUETnebo DELTA jako datové typy, externí tabulky podporují statistiky pouze pro jeden sloupec na CREATE STATISTICS příkaz.

Statistiky s filtrovanou podmínkou

Filtrované statistiky můžou zlepšit výkon dotazů pro dotazy, které vybírají z dobře definovaných podmnožina dat. Filtrované statistiky používají predikát filtru v klauzuli WHERE k výběru podmnožinu dat, která jsou součástí statistiky.

Kdy použít FUNKCI CREATE STATISTICS

Další informace o tom, kdy použít CREATE STATISTICS, naleznete v tématu Statistika.

Referenční závislosti pro filtrované statistiky

Zobrazení katalogu sys.sql_expression_dependencies sleduje každý sloupec v predikátu filtrovaných statistik jako odkazující závislost. Než vytvoříte filtrované statistiky, zvažte operace, které provádíte se sloupci tabulky. Definici sloupce tabulky definovaného v predikáte filtrované statistiky nelze odstranit, přejmenovat ani změnit.

Omezení

• Aktualizace statistik není u externích tabulek podporovaná. Pokud chcete aktualizovat statistiky v externí tabulce, odstraňte a znovu vytvořte statistiku.
• Pro každý objekt statistiky můžete zobrazit až 64 sloupců.
• Tato MAXDOP možnost není kompatibilní s možnostmi a PAGECOUNT možnostmiSTATS_STREAMROWCOUNT.
• Možnost MAXDOP je omezena skupinou úloh Správce prostředků MAX_DOP nastavení, pokud se používá.
• CREATE a DROP STATISTICS u externích tabulek se ve službě Azure SQL Database nepodporují.

Příklady

Ukázky kódu v tomto článku používají ukázkovou databázi AdventureWorks2022 nebo AdventureWorksDW2022, kterou si můžete stáhnout z domovské stránky Microsoft SQL Serveru pro ukázky a komunitní projekty .

A. Použití FUNKCE CREATE STATISTICS s číslem VZORKU PERCENT

Následující příklad vytvoří statistiku ContactMail1 pomocí náhodného vzorku 5 procent BusinessEntityID a EmailPromotion sloupců Person tabulky Databáze AdventureWorks2022.

CREATE STATISTICS ContactMail1
    ON Person.Person (BusinessEntityID, EmailPromotion)
    WITH SAMPLE 5 PERCENT;

B. Použití FUNKCE CREATE STATISTICS s funkcí FULLSCAN a NORECOMPUTE

Následující příklad vytvoří statistiku NamePurchase pro všechny řádky v BusinessEntityID tabulce a EmailPromotion sloupce Person tabulky a zakáže automatické přepočítání statistik.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, NORECOMPUTE;

C. Vytvoření filtrovaných statistik pomocí funkce CREATE STATISTICS

Následující příklad vytvoří filtrované statistiky ContactPromotion1. Databázový stroj vzorkuje 50 procent dat a pak vybere řádky se EmailPromotion rovna 2.

CREATE STATISTICS ContactPromotion1
    ON Person.Person (BusinessEntityID, LastName, EmailPromotion)
WHERE EmailPromotion = 2
WITH SAMPLE 50 PERCENT;
GO

D. Vytvoření statistiky pro externí tabulku

Jediným rozhodnutím, které je potřeba provést při vytváření statistiky pro externí tabulku kromě poskytnutí seznamu sloupců, je to, jestli chcete vytvořit statistiku vzorkováním řádků nebo prohledáváním všech řádků. CREATE a DROP STATISTICS u externích tabulek se ve službě Azure SQL Database nepodporují.

Vzhledem k tomu, že SQL Server importuje data z externí tabulky do dočasné tabulky, aby se vytvořily statistiky, trvá možnost úplné kontroly mnohem déle. U velké tabulky je výchozí metoda vzorkování obvykle dostatečná.

--Create statistics on an external table and use default sampling.
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

--Create statistics on an external table and scan all the rows
CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

E. Použití funkce CREATE STATISTICS s funkcí FULLSCAN a PERSIST_SAMPLE_PERCENT

Následující příklad vytvoří statistiku NamePurchase pro všechny řádky v BusinessEntityID tabulce a EmailPromotion sloupce Person tabulky a nastaví procento vzorkování 100 procent pro všechny následné aktualizace, které explicitně nezadávají procento vzorkování.

CREATE STATISTICS NamePurchase
    ON AdventureWorks2022.Person.Person (BusinessEntityID, EmailPromotion)
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

Příklady použití databáze AdventureWorksDW

F. Vytvoření statistiky pro dva sloupce

Následující příklad vytvoří statistiku CustomerStats1 na CustomerKey základě sloupců EmailAddressDimCustomer tabulky. Statistiky se vytvářejí na základě statisticky významného vzorkování řádků v Customer tabulce.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress);

G. Vytvoření statistik pomocí úplného prohledávání

Následující příklad vytvoří statistiku CustomerStatsFullScan na základě skenování všech řádků v DimCustomer tabulce.

CREATE STATISTICS CustomerStatsFullScan
ON DimCustomer (CustomerKey, EmailAddress) WITH FULLSCAN;

H. Vytvoření statistiky zadáním procenta vzorku

Následující příklad vytvoří statistiku CustomerStatsSampleScan na základě skenování 50 procent řádků v DimCustomer tabulce.

CREATE STATISTICS CustomerStatsSampleScan
ON DimCustomer (CustomerKey, EmailAddress) WITH SAMPLE 50 PERCENT;

I. Použití funkce CREATE STATISTICS s AUTO_DROP

Pokud chcete použít statistiku automatického odstraňování, stačí do klauzule WITH statistiky vytvořit nebo aktualizovat následující.

CREATE STATISTICS CustomerStats1 ON DimCustomer (CustomerKey, EmailAddress) WITH AUTO_DROP = ON

Chcete-li vyhodnotit nastavení automatického odstraňování existujících statistik, použijte auto_drop sloupec v sys.stats:

SELECT object_id, [name], auto_drop
FROM sys.stats;

Související obsah

• Statistika
• Statistiky v datových skladech v prostředcích infrastruktury
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DB SHOW_STATISTICS CC (Transact-SQL)
• DROP STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)