Statisztikák a Synapse SQL-ben

A cikkben található javaslatok és példák a lekérdezésoptimalizálási statisztikák Synapse SQL-erőforrások használatával történő létrehozására és frissítésére: dedikált SQL-készlet és kiszolgáló nélküli SQL-készlet.

Statisztikák dedikált SQL-készletben

Miért érdemes statisztikákat használni?

Minél több dedikált SQL-készlet tud az adatokról, annál gyorsabban hajthat végre lekérdezéseket. Miután adatokat töltött be egy dedikált SQL-készletbe, az adatok statisztikáinak gyűjtése az egyik legfontosabb teendő a lekérdezésoptimalizáláshoz.

A dedikált SQL-készlet lekérdezésoptimalizálója egy költségalapú optimalizáló. Összehasonlítja a különböző lekérdezési tervek költségeit, majd a legalacsonyabb költséggel választja ki a csomagot. A legtöbb esetben a leggyorsabban végrehajtó tervet választja ki.

Ha például az optimalizáló becslése szerint a lekérdezés szűrésének dátuma egy sort ad vissza, akkor egy tervet fog választani. Ha úgy becsüli, hogy a kiválasztott dátum 1 millió sort ad vissza, akkor egy másik tervet ad vissza.

Statisztikák automatikus létrehozása

A dedikált SQL-készletmotor elemezni fogja a bejövő felhasználói lekérdezéseket, ha hiányzó statisztikákat fedez fel, amikor az adatbázis AUTO_CREATE_STATISTICS beállítása ON értékre van állítva. Hiányzó statisztikák esetén a lekérdezésoptimalizáló a lekérdezési predikátumban vagy az illesztési feltételben lévő egyes oszlopokra hoz létre statisztikákat.

Ez a függvény a lekérdezésterv számosságbecsléseinek javítására szolgál.

Fontos

A statisztikák automatikus létrehozása jelenleg alapértelmezés szerint be van kapcsolva.

Az alábbi parancs futtatásával ellenőrizheti, hogy az adattárház AUTO_CREATE_STATISTICS van-e konfigurálva:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Ha az adattárház nem rendelkezik AUTO_CREATE_STATISTICS engedélyezve, javasoljuk, hogy engedélyezze ezt a tulajdonságot az alábbi parancs futtatásával:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Ezek az utasítások a statisztikák automatikus létrehozását váltják ki:

Kiválaszt
INSERT-SELECT
CTAS
Frissítés
töröl
EXPLAIN, amikor illesztést vagy predikátum jelenlétet észlelnek

Megjegyzés

A statisztikák automatikus létrehozása nem ideiglenes vagy külső táblákon jön létre.

A statisztikák automatikus létrehozása szinkron módon történik. Előfordulhat, hogy a hiányzó statisztikák miatt némileg romlik a lekérdezési teljesítmény az oszlopok esetében. Az egyetlen oszlop statisztikáinak létrehozásához szükséges idő a tábla méretétől függ.

A mérhető teljesítménycsökkenés elkerülése érdekében a rendszer profilkészítése előtt győződjön meg arról, hogy a statisztikák először a teljesítményteszt számítási feladatainak végrehajtásával lettek létrehozva.

Megjegyzés

A statisztikák létrehozását a rendszer egy másik felhasználói környezetben naplózza sys.dm_pdw_exec_requests .

Az automatikus statisztikák létrehozásakor a következő formában jelennek meg: WA_Sys<8 számjegyű oszlopazonosító Hex formátumban>_<8 számjegyű táblázatazonosító Hex formátumban>. A már létrehozott statisztikákat a DBCC SHOW_STATISTICS parancs futtatásával tekintheti meg:

DBCC SHOW_STATISTICS (<table_name>, <target>)

A table_name a megjelenítendő statisztikákat tartalmazó tábla neve, amely nem lehet külső tábla. A cél annak a célindexnek, statisztikai adatnak vagy oszlopnak a neve, amelynek statisztikai adatait meg szeretné jeleníteni.

Statisztikák frissítése

Az egyik ajánlott eljárás a dátumoszlopok statisztikáinak napi frissítése új dátumok hozzáadásakor. Minden alkalommal, amikor új sorokat tölt be az adattárházba, új betöltési dátumokat vagy tranzakciódátumokat ad hozzá. Ezek a kiegészítések megváltoztatják az adateloszlást, és elavultá teszik a statisztikákat.

Előfordulhat, hogy egy ügyféltábla ország- vagy régióoszlopának statisztikáit nem kell frissíteni, mert az értékek eloszlása általában nem változik. Feltéve, hogy az eloszlás állandó az ügyfelek között, az új sorok hozzáadása a táblázat változatához nem változtatja meg az adateloszlást.

Ha azonban az adattárház csak egy országot vagy régiót tartalmaz, és új országból vagy régióból hoz be adatokat, frissítenie kell az ország vagy régió oszlop statisztikáit.

A statisztikák frissítésére vonatkozó javaslatok a következők:

Típus szerint	Ajánlás
A statisztikafrissítések gyakorisága	Konzervatív: Naponta az adatok betöltése vagy átalakítása után
Mintavételezés	Kevesebb mint 1 milliárd sor, használjon alapértelmezett mintavételezést (20 százalék). Több mint 1 milliárd sor esetén két százalékos mintavételezést használjon.

Az utolsó statisztikai frissítés meghatározása

Az egyik első kérdés, amelyet fel kell tenni egy lekérdezés hibaelhárítása során: "Naprakészek a statisztikák?"

Ez a kérdés nem az adatok kora alapján megválaszolható kérdés. Egy naprakész statisztikai objektum régi lehet, ha nem történt lényeges változás a mögöttes adatokban. Ha a sorok száma jelentősen megváltozott, vagy egy oszlop értékeinek eloszlásában lényeges változás következik be, akkor ideje frissíteni a statisztikákat.

Nem érhető el dinamikus felügyeleti nézet annak megállapításához, hogy a tábla adatai megváltoztak-e a statisztikák legutóbbi frissítése óta. A statisztikáid korának ismerete részleges képet adhat.

Az alábbi lekérdezés segítségével megállapíthatja, hogy az egyes táblák statisztikái mikor frissültek utoljára.

Megjegyzés

Ha egy oszlop értékeinek eloszlása lényegesen megváltozik, a legutóbbi frissítés időpontjától függetlenül frissítenie kell a statisztikákat.

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

Az adattárház dátumoszlopaihoz például általában gyakori statisztikai frissítésekre van szükség. Minden alkalommal, amikor új sorokat tölt be az adattárházba, új betöltési dátumokat vagy tranzakciódátumokat ad hozzá. Ezek a kiegészítések megváltoztatják az adateloszlást, és elavultá teszik a statisztikákat.

Előfordulhat, hogy egy ügyféltábla nemi oszlopának statisztikáit soha nem kell frissíteni. Feltéve, hogy az eloszlás állandó az ügyfelek között, az új sorok hozzáadása a táblázat változatához nem változtatja meg az adateloszlást.

Ha azonban az adattárház csak egy nemet tartalmaz, és egy új követelmény több nemet eredményez, akkor frissítenie kell a nemek oszlopának statisztikáit.

További információkért tekintse át a Statisztika cikket.

Statisztikakezelés implementálása

Gyakran érdemes kiterjeszteni az adatbetöltési folyamatot annak érdekében, hogy a statisztikák a terhelés végén frissüljenek. Az adatbetöltés az, amikor a táblák leggyakrabban módosítják a méretüket, az értékek eloszlását vagy mindkettőt. Így a terhelési folyamat logikus hely néhány felügyeleti folyamat implementálásához.

A terhelési folyamat során a statisztikák frissítéséhez az alábbi irányelveket biztosítjuk:

Győződjön meg arról, hogy minden betöltött tábla legalább egy frissített statisztikai objektummal rendelkezik. Ez a folyamat frissíti a táblázat méretét (sorszám és oldalszám) a statisztikai frissítés részeként.
A JOIN, GROUP BY, ORDER BY és DISTINCT záradékokban részt vevő oszlopokra összpontosítson.
Érdemes lehet gyakrabban frissíteni a „növekvő kulcs” oszlopokat, például a tranzakciós dátumokat, mert ezek az értékek nem lesznek belefoglalva a statisztikai hisztogramba.
Fontolja meg a statikus terjesztési oszlopok ritkábban történő frissítését.
Ne feledje, hogy minden statisztikai objektum egymás után frissül. Az egyszerű implementálás UPDATE STATISTICS <TABLE_NAME> nem mindig ideális, különösen a sok statisztikai objektumot tartalmazó széles táblák esetében.

További információ: Számosság becslése.

Példák: Statisztikák létrehozása

Ezek a példák bemutatják, hogyan használhat különböző beállításokat a statisztikák létrehozásához. Az egyes oszlopokhoz használt beállítások az adatok jellemzőitől és az oszlop lekérdezésekben való használatának módjától függenek.

Egyoszlopos statisztikák létrehozása alapértelmezett beállításokkal

Ha statisztikákat szeretne létrehozni egy oszlopon, adja meg a statisztikai objektum nevét és az oszlop nevét. Ez a szintaxis az összes alapértelmezett beállítást használja. Alapértelmezés szerint a dedikált SQL tárolóerőforrás a tábla 20 százalékát mintázza, amikor statisztikákat hoz létre.

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name]);

Például:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

A legtöbb helyzetben elegendő az alapértelmezett 20 százalékos mintavételezési arány. Azonban módosíthatja a mintavételezési sebességet. A teljes táblázat mintájához használja a következő szintaxist:

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name])
    WITH FULLSCAN;

Például:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH FULLSCAN;

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

Egy másik lehetőség, hogy százalékként adja meg a mintaméretet:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH SAMPLE 50 PERCENT;

Egyoszlopos statisztikák létrehozása csak néhány sorra

A táblázat sorainak egy részére is létrehozhat statisztikákat, amelyeket szűrt statisztikáknak neveznek.

Szűrt statisztikákat például akkor használhat, ha egy nagy particionált tábla adott partícióját szeretné lekérdezni. Ha csak a partícióértékekre hoz létre statisztikákat, a statisztikák pontossága javulni fog. A lekérdezési teljesítmény is javulni fog.

Ez a példa egy értéktartomány statisztikáit hozza létre. Az értékek egyszerűen definiálhatók úgy, hogy megfeleljenek a partícióban lévő értékek tartományának.

CREATE STATISTICS stats_col1
    ON table1(col1)
    WHERE col1 > '2000101' AND col1 < '20001231';

Megjegyzés

Ahhoz, hogy a lekérdezésoptimalizáló az elosztott lekérdezési terv kiválasztásakor megfontolja a szűrt statisztikák használatát, a lekérdezésnek el kell férnie a statisztikai objektum definíciója között. Az előző példában a lekérdezés WHERE záradékának col1 értéket kell megadnia 2000101 és 20001231 között.

Egyoszlopos statisztikák létrehozása az összes beállítással

A beállításokat kombinálhatja is. Az alábbi példa egy szűrt statisztikai objektumot hoz létre egyéni mintamérettel:

CREATE STATISTICS stats_col1
    ON table1 (col1)
    WHERE col1 > '2000101' AND col1 < '20001231'
    WITH SAMPLE 50 PERCENT;

A teljes hivatkozásért tekintse meg a CREATE STATISTICS (STATISZTIKÁK LÉTREHOZÁSA) című témakört.

Többoszlopos statisztikák létrehozása

Többoszlopos statisztikai objektum létrehozásához használja az előző példákat, de adjon meg további oszlopokat.

Megjegyzés

A lekérdezés eredményében szereplő sorok számának becslésére használt hisztogram csak a statisztikai objektumdefinícióban szereplő első oszlophoz érhető el.

Ebben a példában a hisztogram a(z) product_category-n van. Az oszlopközi statisztikák kiszámítása product_category és product_sub_category történik:

CREATE STATISTICS stats_2cols
    ON table1 (product_category, product_sub_category)
    WHERE product_category > '2000101' AND product_category < '20001231'
    WITH SAMPLE 50 PERCENT;

Mivel a product_category és a product_sub_category között korreláció áll fenn, a többoszlopos statisztikai objektum akkor lehet hasznos, ha ezek az oszlopok egyszerre érhetők el. A tábla lekérdezésekor a többoszlopos statisztikák javítják az illesztések, a CSOPORTOSÍTÁSI ÖSSZESÍTÉSek, a különböző számok és a WHERE szűrők számosságbecslését (amennyiben az elsődleges statisztikai oszlop a szűrő része).

Statisztika létrehozása egy tábla összes oszlopán

A statisztikák létrehozásának egyik módja a CREATE STATISTICS parancsok kiadása a tábla létrehozása után:

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

Tárolt eljárás használata az adatbázis összes oszlopának statisztikáinak létrehozásához

Az SQL-készlet nem rendelkezik rendszertárolt eljárással, amely egyenértékű lenne az SQL Server sp_create_stats-vel. Ez a tárolt eljárás egyetlen oszlopos statisztikai objektumot hoz létre az adatbázis minden olyan oszlopán, amely még nem rendelkezik statisztikával.

Az alábbi példa segít az adatbázis tervezésének első lépéseiben. Nyugodtan alkalmazkodjon az igényeihez:

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

Ha a tábla összes oszlopára vonatkozó statisztikákat az alapértelmezett értékekkel szeretné létrehozni, hajtsa végre a tárolt eljárást.

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

Ha a táblázat összes oszlopára szeretne statisztikákat létrehozni fullscan használatával, hívja meg ezt az eljárást:

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

Ha a tábla összes oszlopában szeretne mintául szolgáló statisztikát létrehozni, adja meg a 3 értéket és a minta százalékos értékét. Az alábbi eljárás 20 százalékos mintaarányt használ.

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

Példák: Frissítési statisztikák

A statisztikák frissítéséhez az alábbiakat végezheti el:

Frissítsen egy statisztikai objektumot. Adja meg a frissíteni kívánt statisztikai objektum nevét.
Frissítse a tábla összes statisztikai objektumát. Adja meg a tábla nevét egy adott statisztikai objektum helyett.

Egy adott statisztikai objektum frissítése

Az alábbi szintaxissal frissíthet egy adott statisztikai objektumot:

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

Például:

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

Bizonyos statisztikai objektumok frissítésével minimalizálhatja a statisztikák kezeléséhez szükséges időt és erőforrásokat. Ez a művelet némi gondolkodást igényel a frissítendő legjobb statisztikai objektumok kiválasztásához.

Tábla összes statisztikáinak frissítése

A tábla összes statisztikai objektumának frissítésére szolgáló egyszerű módszer a következő:

UPDATE STATISTICS [schema_name].[table_name];

Például:

UPDATE STATISTICS dbo.table1;

Az UPDATE STATISTICS utasítás könnyen használható. Ne feledje, hogy frissíti a táblázat összes statisztikáit, és a szükségesnél több munkát kér.

Ha a teljesítmény nem probléma, ez a módszer a legegyszerűbb és legteljesebb módja annak, hogy a statisztikák naprakészek legyenek.

Megjegyzés

Egy tábla összes statisztikájának frissítésekor a dedikált SQL-készlet beolvassa a táblát mintavételezés céljából minden statisztikai objektum esetében. Ha a táblázat nagy, és sok oszlopot és sok statisztikát tartalmaz, akkor hatékonyabb lehet az egyes statisztikák igény szerinti frissítése.

Az eljárás végrehajtásához UPDATE STATISTICS tekintse meg az ideiglenes táblákat. A megvalósítási módszer kissé eltér az előző CREATE STATISTICS eljárástól, de az eredmény ugyanaz. A teljes szintaxisért tekintse meg a Statisztika frissítése című témakört.

Statisztikai metaadatok

Számos rendszernézet és függvény használható a statisztikákkal kapcsolatos információk megtalálásához. Például a STATS_DATE() függvény használatával ellenőrizheti, hogy egy statisztikai objektum elavult-e. STATS_DATE() lehetővé teszi a statisztikák legutóbbi létrehozásának vagy frissítésének megtekintését.

Katalógusnézetek statisztikákhoz

Ezek a rendszernézetek a statisztikákról nyújtanak információt:

Katalógusnézet	Leírás
sys.columns	Minden oszlophoz egy sor tartozik.
sys.objects	Egy sor az adatbázis minden objektumához.
sys.schemas	Az adatbázis minden sémájának egy sora.
sys.stats	Minden statisztikai objektumhoz egy sor tartozik.
sys.stats_columns	A statisztikai objektum minden oszlopához egy sor tartozik. A sys.columns fájlra mutató hivatkozások.
sys.tables	Minden táblához egy sor tartozik (külső táblákat is tartalmaz).
sys.table_types	Minden adattípushoz egy sor tartozik.

Statisztikákhoz tartozó rendszerfüggvények

Ezek a rendszerfüggvények hasznosak a statisztikák kezeléséhez:

Rendszerfüggvény	Leírás
STATS_DATE	A statisztikai objektum utolsó frissítésének dátuma.
DBCC SHOW_STATISTICS	Összegző szint és részletes információk az értékek statisztikai objektum által értelmezett eloszlásáról.

Statisztikai oszlopok és függvények egyesítése egyetlen nézetben

Ez a nézet a STATS_DATE() függvény statisztikáihoz és eredményeihez kapcsolódó oszlopokat egyesíti.

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

DBCC-SHOW_STATISTICS() példák

A DBCC SHOW_STATISTICS() a statisztikai objektumban tárolt adatokat jeleníti meg. Ezek az adatok három részből állnak:

Fejléc
Sűrűségvektor
Hisztogram

A fejléc a statisztikák metaadatai. A hisztogram a statisztikai objektum első kulcsoszlopában jeleníti meg az értékek eloszlását.

A sűrűségvektor oszlopközi korrelációt mér. A dedikált SQL-készlet számosságbecsléseket számít ki a statisztikai objektum bármely adatával.

Fejléc, sűrűség és hisztogram megjelenítése

Ez az egyszerű példa egy statisztikai objektum mindhárom részét mutatja be:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Például:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

A DBCC SHOW_STATISTICS() egy vagy több részének megjelenítése

Ha csak bizonyos részek megtekintésére kíváncsi, használja a WITH záradékot, és adja meg, hogy mely részek jelenjenek meg:

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
    WITH stat_header, histogram, density_vector

Például:

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
    WITH histogram, density_vector

DBCC SHOW_STATISTICS() különbségek

DBCC SHOW_STATISTICS() az SQL Serverhez képest szigorúbban implementálva van a dedikált SQL-készletben:

A nem dokumentált funkciók nem támogatottak.
Nem használható Stats_stream.
A statisztikai adatok adott részhalmazainak eredményei nem illeszthetők össze. Például STAT_HEADER JOIN DENSITY_VECTOR.
NO_INFOMSGS nem állítható be az üzenetek letiltására.
A statisztikai nevek körüli szögletes zárójelek nem használhatók.
Nem használhatók oszlopnevek statisztikai objektumok azonosítására.
2767-es egyéni hiba nem támogatott.

Statisztikák kiszolgáló nélküli SQL-készletben

A statisztikák adott oszloponként jönnek létre az adott adatkészlethez (tárolási útvonalhoz).

Megjegyzés

A LOB-oszlopokhoz nem hozhatók létre statisztikák.

Miért érdemes statisztikákat használni?

Minél több kiszolgáló nélküli SQL-készlet tud az adatokról, annál gyorsabban hajthat végre lekérdezéseket rajta. A lekérdezések optimalizálásához az adatok statisztikáinak gyűjtése az egyik legfontosabb teendő.

A kiszolgáló nélküli SQL-készlet lekérdezésoptimalizálója egy költségalapú optimalizáló. Összehasonlítja a különböző lekérdezési tervek költségeit, majd a legalacsonyabb költséggel választja ki a csomagot. A legtöbb esetben a leggyorsabban végrehajtó tervet választja ki.

Ha például az optimalizáló becslése szerint a lekérdezés szűrésének dátuma egy sort ad vissza, akkor egy tervet választ ki. Ha úgy becsüli, hogy a kiválasztott dátum 1 millió sort ad vissza, akkor egy másik tervet választ.

Statisztikák automatikus létrehozása

A kiszolgáló nélküli SQL-készlet a hiányzó statisztikák bejövő felhasználói lekérdezéseit elemzi. Ha a statisztikák hiányoznak, a lekérdezésoptimalizáló statisztikákat hoz létre a lekérdezési predikátum egyes oszlopaiban vagy összekapcsolási feltételében, hogy javítsa a lekérdezésterv számosságbecsléseit.

A SELECT utasítás automatikusan létrehozza a statisztikákat.

Megjegyzés

A statisztikai adatok automatikus létrehozásához mintavételezést használnak, és a mintavételi százalék a legtöbb esetben kevesebb, mint 100%. Ez a folyamat minden fájlformátum esetében ugyanaz. Ne feledje, hogy ha a CSV-t az elemző 1.0-s verziójával olvassa, a mintavételezés nem támogatott, és a statisztikák automatikus létrehozása nem történik meg 100-nál kisebb mintavételi százalékkal%. A becsült alacsony számosságot (sorok számát) tartalmazó kis táblák esetében az automatikus statisztikák létrehozása 100%-os mintavételezési százalékkal aktiválódik. Ez alapvetően azt jelenti, hogy a fullscan aktiválódik, és az automatikus statisztikák még az elemző 1.0-s verziójával rendelkező CSV-hez is létrejönnek.

A statisztikák automatikus létrehozása szinkron módon történik, így kismértékben csökkenhet a lekérdezési teljesítmény, ha az oszlopokból hiányoznak statisztikák. Az egyetlen oszlop statisztikáinak létrehozásához szükséges idő a megcélzott fájlok méretétől függ.

Statisztikák manuális létrehozása

A kiszolgáló nélküli SQL-készlet lehetővé teszi a statisztikák manuális létrehozását. Ha az elemző 1.0-s verzióját használja CSV-vel, akkor valószínűleg manuálisan kell létrehoznia a statisztikákat, mert ez az elemzőverzió nem támogatja a mintavételezést. Az elemző 1.0-s verziója esetén a statisztikák automatikus létrehozása nem történik meg, kivéve, ha a mintavételi százalék 100%.

A statisztikák manuális létrehozásával kapcsolatos útmutatásért tekintse meg az alábbi példákat.

Statisztikák frissítése

A fájlok adatainak módosítása, a fájlok törlése és hozzáadása adatterjesztési változásokat eredményez, és a statisztikákat elavulttá teszi. Ebben az esetben a statisztikákat frissíteni kell.

A kiszolgáló nélküli SQL-készlet automatikusan létrehozza az OPENROWSET-oszlopok statisztikáit, ha az adatok jelentősen módosulnak. Minden alkalommal, amikor a statisztika automatikusan létrejön, az adathalmaz aktuális állapota is mentésre kerül: fájl elérési útjai, méretei, utolsó módosítási dátumok.

Ha a statisztikák elavultak, újak jönnek létre. Az algoritmus végighalad az adatokon, és összehasonlítja az adathalmaz aktuális állapotával. Ha a módosítások mérete nagyobb, mint az adott küszöbérték, akkor a rendszer törli a régi statisztikákat, és az új adatkészleten újra létrejön.

A manuális statisztikák soha nem lesznek elavultnak nyilvánítva.

Megjegyzés

A statisztikai adatok automatikus újrahasználatához mintavételezést használnak, és a mintavételi százalék a legtöbb esetben kevesebb, mint 100%. Ez a folyamat minden fájlformátum esetében ugyanaz. Ne feledje, hogy ha a CSV-t az elemző 1.0-s verziójával olvassa, a mintavételezés nem támogatott, és a statisztikák automatikus rekreációja nem történik meg 100-nál kisebb mintavételi százalékkal%. Ebben az esetben manuálisan el kell vetnie, majd újból létre kell hoznia a statisztikákat. Tekintse meg az alábbi példákat a statisztikák elvetésére és létrehozására. A becsült alacsony számossággal (sorok számával) rendelkező kis táblák esetében az automatikus statisztikák 100% mintavételezési százalékával indulnak be. Ez alapvetően azt jelenti, hogy a fullscan aktiválódik, és az automatikus statisztikák még az elemző 1.0-s verziójával rendelkező CSV-hez is létrejönnek.

Az egyik első kérdés, amelyet fel kell tenni egy lekérdezés hibaelhárítása során: "Naprakészek a statisztikák?"

Ha a sorok száma jelentősen megváltozott, vagy lényeges változás történt egy oszlop értékeinek eloszlásában, akkor ideje frissíteni a statisztikákat.

Megjegyzés

Ha egy oszlop értékeinek eloszlása lényegesen megváltozik, a legutóbbi frissítés időpontjától függetlenül frissítenie kell a statisztikákat.

Statisztikakezelés implementálása

Érdemes lehet kiterjeszteni az adatfolyamot, hogy a statisztikák frissüljenek, ha az adatok jelentősen módosulnak a fájlok hozzáadásával, törlésével vagy módosításával.

A statisztikák frissítéséhez az alábbi irányelveket biztosítjuk:

Győződjön meg arról, hogy az adathalmaz legalább egy frissített statisztikai objektummal rendelkezik. Ez frissíti a méret (sorszám és oldalszám) adatait a statisztikai frissítés részeként.
A WHERE, JOIN, GROUP BY, ORDER BY és DISTINCT záradékokban részt vevő oszlopokra összpontosítson.
A "növekvő kulcs" oszlopokat, például a tranzakciós dátumokat gyakrabban frissítse, mert ezek az értékek nem lesznek belefoglalva a statisztikai hisztogramba.
A statikus terjesztési oszlopok ritkábban frissülnek.

További információ: Számosság becslése.

Példák: Az OPENROWSET-elérési út oszlopainak statisztikáinak létrehozása

Az alábbi példák bemutatják, hogyan hozhat létre statisztikákat az Azure Synapse kiszolgáló nélküli SQL-készleteiben. Az egyes oszlopokhoz használt beállítások az adatok jellemzőitől és az oszlop lekérdezésekben való használatának módjától függenek. Az ezekben a példákban használt tárolt eljárásokkal kapcsolatos további információkért tekintse át a sys.sp_create_openrowset_statistics és a sys.sp_drop_openrowset_statistics, amelyek csak a kiszolgáló nélküli SQL-készletekre vonatkoznak.

Megjegyzés

Egyoszlopos statisztikákat csak ebben a pillanatban hozhat létre.

A sp_create_openrowset_statistics és sp_drop_openrowset_statistics végrehajtásához a következő engedélyek szükségesek: TÖMEGES MŰVELETEK ADMINISZTRÁLÁSA vagy AZ ADATBÁZIS TÖMEGES MŰVELETEINEK ADMINISZTRÁLÁSA.

A rendszer a következő tárolt eljárást használja a statisztikák létrehozásához:

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

Argumentumok: [ @stmt = ] N'statement_text' – Egy Transact-SQL utasítást ad vissza, amely a statisztikákhoz használandó oszlopértékeket adja vissza. A TABLESAMPLE használatával megadhatja a használandó adatmintákat. Ha a TABLESAMPLE nincs megadva, a FULLSCAN lesz használva.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Megjegyzés

A CSV-mintavételezés nem működik, ha az elemző 1.0-s verzióját használja, csak a FULLSCAN támogatott az elemző 1.0-s verziójával rendelkező CSV-hez.

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

Ha statisztikákat szeretne létrehozni egy oszlopon, adjon meg egy lekérdezést, amely azt az oszlopot adja vissza, amelyhez statisztikára van szüksége.

Alapértelmezés szerint, ha manuálisan hoz létre statisztikákat és nem adja meg másként, a serverless SQL-pool az adathalmazban megadott adatok 100%-át használja, amikor statisztikákat hoz létre.

Ha például az adathalmaz egy sokaságoszlopához alapértelmezett beállításokkal (FULLSCAN) rendelkező statisztikákat szeretne létrehozni a us_population.csv fájl alapján:


EXEC sys.sp_create_openrowset_statistics N'SELECT 
    population
FROM OPENROWSET(
    BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE)
AS [r]'

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

A mintaméretet százalékként is megadhatja:

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Példák: Frissítési statisztikák

A statisztikák frissítéséhez törölnie kell és újra létre kell hoznia a statisztikákat. További információ: sys.sp_create_openrowset_statistics és sys.sp_drop_openrowset_statistics.

A sys.sp_drop_openrowset_statistics tárolt eljárás a statisztikák elvetésére szolgál:

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

Megjegyzés

Argumentumok: [ @stmt = ] N'statement_text' – A statisztikák létrehozásakor használt Transact-SQL utasítást adja meg.

A population.csv fájl alapján készült adathalmaz év oszlopának statisztikáinak frissítéséhez újra kell generálni a statisztikákat:

EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'
GO

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

Példák: Statisztikák létrehozása külső táblaoszlophoz

Az alábbi példák bemutatják, hogyan hozhat létre statisztikákat különböző lehetőségek felhasználásával. Az egyes oszlopokhoz használt beállítások az adatok jellemzőitől és az oszlop lekérdezésekben való használatának módjától függenek.

Megjegyzés

Egyoszlopos statisztikákat csak ebben a pillanatban hozhat létre.

Ha statisztikákat szeretne létrehozni egy oszlopon, adja meg a statisztikai objektum nevét és az oszlop nevét.

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

Argumentumok: "external_table" Külső táblák meghatározása, amelyekhez statisztikákat kell létrehozni.

A FULLSCAN az összes sor beolvasásával számít statisztikákat. A FULLSCAN és a 100 SZÁZALÉKOS MINTAVÉTEL ugyanazt az eredményt adja. A FULLSCAN nem használható a SAMPLE opcióval.

A minta szám/százalék megadja a tábla vagy indexelt nézet sorainak hozzávetőleges százalékát vagy mennyiségét, amelyet a lekérdezésoptimalizáló a statisztikák létrehozásakor használ. A szám 0 és 100 között lehet.

A SAMPLE nem használható a FULLSCAN beállítással.

Megjegyzés

A CSV-mintavételezés nem működik, ha az elemző 1.0-s verzióját használja, csak a FULLSCAN támogatott az elemző 1.0-s verziójával rendelkező CSV-hez.

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

Példák: Frissítési statisztikák

A statisztikák frissítéséhez törölnie kell és újra létre kell hoznia a statisztikákat. Először csökkentse a statisztikákat:

DROP STATISTICS census_external_table.sState

És hozzon létre statisztikákat:

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

Statisztikai metaadatok

Megjegyzés

A statisztikai metaadatok csak külső táblaoszlopokhoz érhetők el. A statisztikai metaadatok nem érhetők el az OPENROWSET-oszlopokhoz.

Katalógusnézetek statisztikákhoz

Ezek a rendszernézetek a statisztikákról nyújtanak információt:

Katalógusnézet	Leírás
sys.columns	Minden oszlophoz egy sor tartozik.
sys.objects	Egy sor az adatbázis minden objektumához.
sys.schemas	Az adatbázis minden sémájának egy sora.
sys.stats	Minden statisztikai objektumhoz egy sor tartozik.
sys.stats_columns	A statisztikai objektum minden oszlopához egy sor tartozik. A sys.columns fájlra mutató hivatkozások.
sys.tables	Minden táblához egy sor tartozik (külső táblákat is tartalmaz).
sys.table_types	Minden adattípushoz egy sor tartozik.

Statisztikákhoz tartozó rendszerfüggvények

Ezek a rendszerfüggvények hasznosak a statisztikák kezeléséhez:

Rendszerfüggvény	Leírás
STATS_DATE	A statisztikai objektum utolsó frissítésének dátuma.

Statisztikai oszlopok és függvények egyesítése egyetlen nézetben

Ez a nézet a STATS_DATE() függvény statisztikáihoz és eredményeihez kapcsolódó oszlopokat egyesíti.

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   st.[user_created] = 1
;

Következő lépések

A dedikált SQL-készlet lekérdezési teljesítményének további javításához tekintse meg a dedikált SQL-készlet számítási feladatainak és ajánlott eljárásainak monitorozását.

A kiszolgáló nélküli SQL-készlet lekérdezési teljesítményének további javításához tekintse meg a kiszolgáló nélküli SQL-készlet ajánlott eljárásait.

Visszacsatolás

Hasznos volt ez az oldal?

Last updated on 2025-04-04

Megosztás:

Statisztikák a Synapse SQL-ben

Statisztikák dedikált SQL-készletben

Miért érdemes statisztikákat használni?

Statisztikák automatikus létrehozása

Statisztikák frissítése

Az utolsó statisztikai frissítés meghatározása

Statisztikakezelés implementálása

Példák: Statisztikák létrehozása

Egyoszlopos statisztikák létrehozása alapértelmezett beállításokkal

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

Egyoszlopos statisztikák létrehozása csak néhány sorra

Egyoszlopos statisztikák létrehozása az összes beállítással

Többoszlopos statisztikák létrehozása

Statisztika létrehozása egy tábla összes oszlopán

Tárolt eljárás használata az adatbázis összes oszlopának statisztikáinak létrehozásához

Példák: Frissítési statisztikák

Egy adott statisztikai objektum frissítése

Tábla összes statisztikáinak frissítése

Statisztikai metaadatok

Katalógusnézetek statisztikákhoz

Statisztikákhoz tartozó rendszerfüggvények

Statisztikai oszlopok és függvények egyesítése egyetlen nézetben

DBCC-SHOW_STATISTICS() példák

Fejléc, sűrűség és hisztogram megjelenítése

A DBCC SHOW_STATISTICS() egy vagy több részének megjelenítése

DBCC SHOW_STATISTICS() különbségek

Statisztikák kiszolgáló nélküli SQL-készletben

Miért érdemes statisztikákat használni?

Statisztikák automatikus létrehozása

Statisztikák manuális létrehozása

Statisztikák frissítése

Statisztikakezelés implementálása

Példák: Az OPENROWSET-elérési út oszlopainak statisztikáinak létrehozása

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

Példák: Frissítési statisztikák

Példák: Statisztikák létrehozása külső táblaoszlophoz

Egyoszlopos statisztikák létrehozása minden sor vizsgálatával

Egyoszlopos statisztikák létrehozása a minta méretének megadásával

Példák: Frissítési statisztikák

Statisztikai metaadatok

Katalógusnézetek statisztikákhoz

Statisztikákhoz tartozó rendszerfüggvények

Statisztikai oszlopok és függvények egyesítése egyetlen nézetben

Következő lépések

Visszacsatolás

További források