STATISZTIKA LÉTREHOZÁSA (Transact-SQL)

A következőkre vonatkozik:SQL ServerAzure SQL DatabaseFelügyelt Azure SQL-példányAzure Synapse AnalyticsSQL Analytics-végpont a Microsoft FabricbenRaktár a Microsoft FabricbenSQL-adatbázis a Microsoft Fabricben

Lekérdezésoptimalizálási statisztikákat hoz létre egy tábla egy vagy több oszlopán, indexelt nézeten vagy külső táblán. A legtöbb lekérdezés esetében a lekérdezésoptimalizáló már létrehozza a megfelelő statisztikákat egy kiváló minőségű lekérdezési tervhez; néhány esetben további statisztikákat kell létrehoznia a lekérdezési tervvel, CREATE STATISTICS vagy módosítania kell a lekérdezési teljesítményt.

További információ: Statisztika.

Megjegyzés

A Microsoft Fabric statisztikáiról további információt a Fabric Data Warehouse statisztikái című témakörben talál.

Transact-SQL szintaxis konvenciók

Szemantika

Szintaxis az SQL Serverhez, az Azure SQL Database-hez és a felügyelt Azure SQL-példányhoz.

-- 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 ]

Az Azure Synapse Analytics and Analytics Platform System (PDW) szintaxisa.

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 | = | <> | != | > | >= | !> | < | <= | !<

A Microsoft Fabric szintaxisa.

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

Érvek

statistics_name

A létrehozandó statisztikák neve.

table_or_indexed_view_name

Annak a táblának, indexelt nézetnek vagy külső táblának a neve, amelyen létre szeretné hozni a statisztikákat. Egy másik adatbázis statisztikáinak létrehozásához adjon meg egy minősített táblanevet.

oszlop [ ,... n ]

Egy vagy több oszlop, amelyet fel kell venni a statisztikákba. Az oszlopoknak prioritási sorrendben kell lenniük balról jobbra. A hisztogram létrehozásához csak az első oszlopot használja a rendszer. Minden oszlop oszlopközi korrelációs statisztikákhoz, úgynevezett sűrűségekhez használatos.

Az indexkulcs oszlopaként megadható oszlopokat az alábbi kivételekkel adhatja meg:

• xml-, teljes szöveges és FILESTREAM-oszlopok nem adhatók meg.

• A számított oszlopok csak akkor adhatók meg, ha az adatbázis és ARITHABORT az QUOTED_IDENTIFIER adatbázis beállításai.ON

• A CLR felhasználó által definiált típusoszlopai akkor adhatók meg, ha a típus támogatja a bináris rendezést. A felhasználó által definiált típusú oszlopok metódushívásaként definiált számított oszlopok akkor adhatók meg, ha a metódusok determinisztikusként vannak megjelölve.

HOL <filter_predicate>

A statisztikai objektum létrehozásakor belefoglalandó sorok egy részhalmazának kiválasztására szolgáló kifejezést ad meg. A szűrő predikátummal létrehozott statisztikákat szűrt statisztikáknak nevezzük. A szűrő predikátum egyszerű összehasonlító logikát használ, és nem hivatkozhat számított oszlopra, UDT-oszlopra, térbeli adattípus-oszlopra vagy hierarchiaazonosító adattípus-oszlopra. A konstansokat használó NULL összehasonlítások nem engedélyezettek az összehasonlító operátorokkal. Használja inkább a IS NULL és IS NOT NULL operátorokat.

Íme néhány példa a Production.BillOfMaterials táblára vonatkozó szűrő predikátumokra:

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

A szűrési predikátumokról további információt a Szűrt indexek létrehozása című témakörben talál.

FULLSCAN

A következőkre vonatkozik: SQL Server 2016 (13.x) SP 1 CU 4, SQL Server 2017 (14.x) CU 1 és újabb verziók

Az összes sor vizsgálatával kiszámítja a statisztikákat. FULLSCAN és SAMPLE 100 PERCENT ugyanazokkal az eredményekkel rendelkezik. FULLSCAN nem használható a SAMPLE beállítással.

Ha nincs megadva, az SQL Server mintavételezéssel hozza létre a statisztikákat, és meghatározza a kiváló minőségű lekérdezési terv létrehozásához szükséges mintaméretet.

A Microsoft Fabric Warehouse-ban csak egyoszlopos FULLSCAN és egyoszlopos SAMPLEstatisztikák támogatottak. Ha nincs lehetőség, SAMPLE a rendszer statisztikákat hoz létre.

MINTAszám { PERCENT | SOROK }

A lekérdezésoptimalizáló statisztikai adatok létrehozásakor használni kívánt tábla- vagy indexelt nézetben adja meg a sorok hozzávetőleges százalékát vagy számát. A PERCENTszám értéke 0 és 100 között lehet, a ROWSszám pedig 0 és a sorok teljes száma között lehet. Előfordulhat, hogy a lekérdezésoptimalizáló-minták tényleges százaléka vagy száma nem felel meg a megadott százalékos értéknek vagy számnak. A lekérdezésoptimalizáló például egy adatoldal összes sorát megvizsgálja.

SAMPLE olyan speciális esetekben hasznos, amikor a lekérdezési terv az alapértelmezett mintavételezés alapján nem optimális. A legtöbb esetben nem szükséges megadni SAMPLE , mert a lekérdezésoptimalizáló már mintavételezést használ, és alapértelmezés szerint meghatározza a statisztikailag jelentős mintaméretet a kiváló minőségű lekérdezési tervek létrehozásához.

SAMPLE nem használható a FULLSCAN beállítással. Ha SAMPLE nincs megadva, FULLSCAN a lekérdezésoptimalizáló mintavételezett adatokat használ, és alapértelmezés szerint kiszámítja a minta méretét.

Javasoljuk, hogy ne adjon meg vagy 0 PERCENT0 ROWS. Ha 0 PERCENT meg van adva vagy 0 ROWS meg van adva, a statisztikai objektum létrejön, de nem tartalmaz statisztikai adatokat.

A Microsoft Fabric Warehouse-ban csak egyoszlopos FULLSCAN és egyoszlopos SAMPLEstatisztikák támogatottak. Ha nincs lehetőség, FULLSCAN a rendszer statisztikákat hoz létre.

PERSIST_SAMPLE_PERCENT = { ON | KI }

Amikor ONa statisztikák megtartják a létrehozási mintavételezési százalékot az olyan későbbi frissítéseknél, amelyek nem határoznak meg explicit módon mintavételezési százalékot. Amikor OFFa statisztikai mintavételezési százalék visszaállítja az alapértelmezett mintavételezést a későbbi frissítésekben, amelyek nem adják meg explicit módon a mintavételezési százalékot. Az alapértelmezett érték a OFF.

Megjegyzés

Ha a táblázat csonkolva van, a csonkolt halomra vagy a B-fára (HoBT) épülő összes statisztika visszaáll az alapértelmezett mintavételezési százalékra. Hasonlóképpen, ha a statisztikák sorok nélkül frissülnek egy objektumon, akkor az visszaállítja az alapértelmezett mintavételezési százalékot, még akkor is, ha PERSIST_SAMPLE_PERCENT korábban konfigurálva volt.

STATS_STREAM = stats_stream

Csak tájékoztatási célokra van meghatározva. Nem támogatott. A jövőbeli kompatibilitás nem garantált.

NORECOMPUTE

Tiltsa le az automatikus statisztikafrissítési beállítást AUTO_STATISTICS_UPDATE esetén. Ha ez a beállítás meg van adva, a lekérdezésoptimalizáló elvégzi a statistics_name folyamatban lévő statisztikai frissítéseit, és letiltja a jövőbeli frissítéseket.

A statisztikai frissítések újbóli engedélyezéséhez távolítsa el a statisztikákat a DROP STATISTICS használatával, majd futtassa CREATE STATISTICS a NORECOMPUTE beállítás nélkül.

Figyelmeztetés

Ha letiltja a statisztikák automatikus frissítését, az megakadályozhatja, hogy a Lekérdezésoptimalizáló optimális végrehajtási terveket válasszon a táblát tartalmazó lekérdezésekhez. Ezt a lehetőséget takarékosan, csak egy minősített adatbázis-rendszergazda használhatja.

A beállítással kapcsolatos további információkért lásd az AUTO_STATISTICS_UPDATEALTER DATABASE SET beállításait. A statisztikai frissítések letiltásáról és újbóli engedélyezéséről további információt a Statisztika című témakörben talál.

NÖVEKMÉNYES = { BE | KI }

: SQL Server 2014 (12.x) és újabb verziók

Amikor ONa létrehozott statisztikák partíciónkénti statisztikák. Amikor OFFa rendszer az összes partíció statisztikáit kombinálja. Az alapértelmezett érték a OFF.

Ha a partíciónkénti statisztikák nem támogatottak, hiba jön létre. A növekményes statisztikák nem támogatottak a következő statisztikai típusokhoz:

• Olyan indexekkel létrehozott statisztikák, amelyek nincsenek partícióhoz igazítva az alaptáblához.
• Az Always On olvasható másodlagos adatbázisokon létrehozott statisztikák.
• Írásvédett adatbázisokon létrehozott statisztikák.
• Szűrt indexeken létrehozott statisztikák.
• Nézeteken létrehozott statisztikák.
• Belső táblákon létrehozott statisztikák.
• Térbeli indexekkel vagy XML-indexekkel létrehozott statisztikák.

MAXDOP = max_degree_of_parallelism

A következőkre vonatkozik: SQL Server 2016 (13.x) SP 2, SQL Server 2017 (14.x) CU 3 és újabb verziók

Felülbírálja a maximális párhuzamossági konfigurációs beállítást a statisztikai művelet során. További információ: kiszolgálókonfiguráció: a párhuzamosság maximális foka. A párhuzamos terv végrehajtásához használt processzorok számának korlátozására használható MAXDOP . A maximális érték 64 processzor.

max_degree_of_parallelism lehet:

• 1: Letiltja a párhuzamos tervgenerálást.
• >1: A párhuzamos indexműveletekben használt processzorok maximális számát a megadott számra korlátozza.
• 0 (alapértelmezett): A processzorok tényleges számát használja, vagy kevesebbet az aktuális rendszerterhelés alapján.

update_stats_stream_option

Csak tájékoztatási célokra van meghatározva. Nem támogatott. A jövőbeli kompatibilitás nem garantált.

AUTO_DROP = { ON | KI }

A következőkre vonatkozik: SQL Server 2022 (16.x) és újabb verziók, valamint Azure SQL Database, Felügyelt Azure SQL-példány

Az SQL Server 2022 (16.x) előtt, ha a statisztikákat egy felhasználó vagy egy külső eszköz manuálisan hozza létre egy felhasználói adatbázisban, ezek a statisztikai objektumok blokkolhatják vagy zavarhatják az ügyfél által kívánt sémamódosításokat.

Az SQL Server 2022 -től (16.x) kezdődően a AUTO_DROP beállítás alapértelmezés szerint engedélyezve van az összes új és migrált adatbázisban. A AUTO_DROP tulajdonság lehetővé teszi a statisztikai objektumok létrehozását olyan módban, hogy a statisztikai objektum nem blokkolja az azt követő sémamódosítást, hanem szükség szerint elveti a statisztikákat. Így a manuálisan létrehozott, AUTO_DROP engedélyezett statisztikák automatikusan létrehozott statisztikákhoz hasonlóan viselkednek.

Megjegyzés

Az automatikusan létrehozott statisztikák Auto_Drop tulajdonságának beállítása vagy törlése hibákat eredményezhet. Az automatikusan létrehozott statisztikák mindig automatikus elvetést használnak. Egyes biztonsági másolatok visszaállításakor előfordulhat, hogy ez a tulajdonság helytelenül van beállítva a statisztikai objektum következő frissítéséig (manuálisan vagy automatikusan). Az automatikusan létrehozott statisztikák azonban mindig úgy viselkednek, mint az automatikus csökkenési statisztikák. Amikor egy adatbázist az SQL Server 2022-re (16.x) állít vissza egy korábbi verzióból, ajánlott az adatbázison végrehajtani sp_updatestats a statisztikai AUTO_DROP funkció megfelelő metaadatainak beállításával.

További információ: AUTO_DROP lehetőség.

Engedélyek

Az alábbi engedélyek egyikére van szükség:

• ALTER TABLE
• A felhasználó a tábla tulajdonosa
• Tagság a db_ddladmin rögzített adatbázis-szerepkörben

Megjegyzések

Az SQL Server a tempdb mintavételezett sorokat a statisztikák létrehozása előtt rendezheti.

Külső táblák statisztikái

Külső táblastatisztikák létrehozásakor az SQL Server importálja a külső táblát egy ideiglenes SQL Server-táblába, majd létrehozza a statisztikákat. A mintastatisztikák esetében csak a mintául szolgáló sorokat importálja a rendszer. Ha nagy külső táblázattal rendelkezik, gyorsabban használhatja az alapértelmezett mintavételezést a teljes vizsgálati lehetőség helyett.

Ha a külső tábla DELIMITEDTEXT, CSV, PARQUETvagy DELTA használ adattípusként, a külső táblák csak CREATE STATISTICS parancsonként egy oszlop statisztikáit támogatják.

Szűrt feltétellel rendelkező statisztikák

A szűrt statisztikák javíthatják a lekérdezési teljesítményt a jól definiált adathalmazokból kiválasztott lekérdezések esetében. A szűrt statisztikák a záradékban WHERE egy szűrő predikátumot használnak a statisztikákban szereplő adatok részhalmazának kiválasztásához.

Mikor érdemes használni a CREATE STATISTICS-t?

A használat CREATE STATISTICSidőpontjáról további információt a Statisztika című témakörben talál.

Hivatkozott függőségek szűrt statisztikákhoz

A sys.sql_expression_dependencies katalógusnézet hivatkozási függőségként követi nyomon a szűrt statisztikai predikátum minden oszlopát. A szűrt statisztikák létrehozása előtt vegye figyelembe a táblaoszlopokon végrehajtott műveleteket. A szűrt statisztikai predikátumban definiált táblaoszlopok definícióját nem lehet elvetni, átnevezni vagy módosítani.

Korlátozások

• A statisztikák frissítése külső táblákban nem támogatott. Egy külső tábla statisztikáinak frissítéséhez dobja le és hozza létre újra a statisztikákat.
• Statisztikai objektumonként legfeljebb 64 oszlopot listázhat.
• A MAXDOP beállítás nem kompatibilis a beállításokkal STATS_STREAMés ROWCOUNT a beállításokkalPAGECOUNT.
• A MAXDOP beállítást a Resource Governor számítási feladatcsoport MAX_DOP beállítás korlátozza, ha használja.
• CREATE és DROP STATISTICS a külső táblák nem támogatottak az Azure SQL Database-ben.

Példák

A cikkben szereplő kódminták a AdventureWorks2025 vagy AdventureWorksDW2025 mintaadatbázist használják, amelyet a Microsoft SQL Server-minták és közösségi projektek kezdőlapjáról tölthet le.

Egy. A CREATE STATISTICS használata SZÁZALÉK MINTAszámmal

A következő példa hozza létre a ContactMail1 statisztikákat, az AdventureWorks2025 adatbázis táblázatának és oszlopainak BusinessEntityID 5 EmailPromotionPerson százalékát tartalmazó véletlenszerű mintával.

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

B. A CREATE STATISTICS használata a FULLSCAN és a NORECOMPUTE használatával

Az alábbi példa létrehozza a NamePurchase tábla összes sorának BusinessEntityID és EmailPromotion oszlopának statisztikáját, és letiltja a Person statisztikák automatikus újrafordítását.

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

C. Szűrt statisztikák létrehozása a CREATE STATISTICS használatával

Az alábbi példa a szűrt statisztikákat ContactPromotion1hozza létre. Az adatbázismotor az adatok 50 százalékát mintázta, majd kiválasztja a 2-vel EmailPromotion egyenlő sorokat.

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

D. Statisztikák létrehozása külső táblán

Egyetlen döntést kell hoznia, amikor statisztikákat hoz létre egy külső táblán az oszlopok listájának megadása mellett, hogy a statisztikát a sorok mintavételezésével vagy az összes sor vizsgálatával hozza-e létre. CREATE és DROP STATISTICS a külső táblák nem támogatottak az Azure SQL Database-ben.

Mivel az SQL Server adatokat importál a külső táblából egy ideiglenes táblába a statisztikák létrehozásához, a teljes vizsgálati lehetőség sokkal tovább tart. Nagy táblák esetében az alapértelmezett mintavételezési módszer általában elegendő.

--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. A CREATE STATISTICS használata FULLSCAN és PERSIST_SAMPLE_PERCENT

Az alábbi példa létrehozza a NamePurchase táblázat összes sorának BusinessEntityID és EmailPromotion oszlopának statisztikáját Person , és 100 százalékos mintavételi százalékot állít be minden olyan későbbi frissítéshez, amely nem határoz meg explicit módon mintavételezési százalékot.

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

Példák az AdventureWorksDW-adatbázis használatára

F. Két oszlop statisztikáinak létrehozása

Az alábbi példa a CustomerStats1 táblázat oszlopai és CustomerKey oszlopai alapján hozza létre a EmailAddress statisztikákatDimCustomer. A statisztikák a táblázat sorainak Customer statisztikailag jelentős mintavételezése alapján jönnek létre.

CREATE STATISTICS CustomerStats1
    ON DimCustomer(CustomerKey, EmailAddress);

G. Statisztikák létrehozása teljes vizsgálattal

Az alábbi példa a CustomerStatsFullScan táblázat összes sorának vizsgálata alapján hozza létre a DimCustomer statisztikákat.

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

H. Statisztikák létrehozása a minta százalékos értékének megadásával

Az alábbi példa a CustomerStatsSampleScan táblázat sorainak 50 százalékának vizsgálata alapján hozza létre a DimCustomer statisztikákat.

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

Én. A CREATE STATISTICS használata AUTO_DROP

Az automatikus csepegtetési statisztikák használatához egyszerűen adja hozzá az alábbiakat a WITH statisztikák létrehozásának vagy frissítésének záradékához.

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

A meglévő statisztikák automatikus elvetési beállításának kiértékeléséhez használja a auto_dropsys.stats oszlopot:

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

Kapcsolódó tartalom

• Statisztika
• Statisztikák a Fabric Data Warehouse-ban
• STATISZTIKÁK FRISSÍTÉSE (Transact-SQL)
• sp_updatestats (Transact-SQL)
• DBCC SHOW_STATISTICS (Transact-SQL)
• DROP STATISZTIKÁK (Transact-SQL)
• sys.stats (Transact-SQL)
• sys.stats_columns (Transact-SQL)