Statisztika a Synapse SQL-ben

Cikk
03/25/2023

Ebben a cikkben olyan javaslatokat és példákat talál, amelyekkel lekérdezésoptimalizálási statisztikák hozhatók létre és frissíthetők a Synapse SQL-erőforrások használatával: 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 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éstervek költségeit, majd kiválasztja a legalacsonyabb költségű csomagot. A legtöbb esetben azt a tervet választja ki, amely a leggyorsabb végrehajtást végzi el.

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. 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észlet motorja elemzi a hiányzó statisztikák bejövő felhasználói lekérdezéseit, ha az adatbázis AUTO_CREATE_STATISTICS beállítás értéke ON. 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ési terv 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 nincs engedélyezve AUTO_CREATE_STATISTICS, javasoljuk, hogy engedélyezze ezt a tulajdonságot a következő parancs futtatásával:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Ezek az utasítások elindítják a statisztikák automatikus létrehozását:

SELECT
INSERT-SELECT
CTAS
UPDATE
DELETE
MAGYARÁZAT illesztés vagy predikátum jelenlétének észlelésekor

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 szinkronban történik. Így előfordulhat, hogy a lekérdezési teljesítmény némileg csökkent, ha az oszlopokból hiányoznak statisztikák. 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 először a teljesítményteszt számítási feladatának a rendszer profilkészítése előtt történő végrehajtásával győződjön meg arról, hogy a statisztikák létre lettek hozva.

Megjegyzés

A statisztikák létrehozása más felhasználói környezetben van bejelentkezve 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ót a Hex>_<8 számjegyű táblaazonosítóban a Hexben>. 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 a statisztikai adatait meg szeretné jeleníteni.

Statisztika 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 sorok töltődnek be az adattárházba, új betöltési dátumok vagy tranzakciódátumok lesznek hozzáadva. Ezek a kiegészítések megváltoztatják az adatelosztá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ételezve, hogy az eloszlás állandó az ügyfelek között, az új sorok hozzáadása a táblaváltozathoz 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 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	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álja az alapértelmezett mintavételezést (20 százalék). Több mint 1 milliárd sort használjon két százalékos mintavételezéssel.

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

A lekérdezések hibaelhárítása során az első kérdések egyike a következő: "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ása lényegesen megváltozik, 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ák korának ismerete a kép egy részét is biztosíthatja.

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

Megjegyzés

Ha egy oszlop értékeinek eloszlása lényegesen megváltozik, akkor 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ázak dátumoszlopainak például általában gyakori statisztikai frissítésekre van szükségük. Minden alkalommal, amikor új sorok töltődnek be az adattárházba, új betöltési dátumok vagy tranzakciódátumok lesznek hozzáadva. Ezek a kiegészítések megváltoztatják az adatelosztá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ételezve, hogy az eloszlás állandó az ügyfelek között, az új sorok hozzáadása a táblaváltozathoz 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 bizonyos felügyeleti folyamatok 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.
Összpontosítson a JOIN, GROUP BY, ORDER BY és DISTINCT záradékokban részt vevő oszlopokra.
É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.
Érdemes lehet ritkábban frissíteni a statikus terjesztési oszlopokat.
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ágbecslés.

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

Ezek a példák bemutatják, hogyan használhat különböző lehetőségeket 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ó felhasználásá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-készlet a tábla 20 százalékát mintázta, 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 esetben elegendő az alapértelmezett 20 százalékos mintavételezési arány. A mintavételezési arányt azonban módosíthatja. 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 statisztika 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 statisztika 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, hogy megfeleljenek a partíciókban 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ó szűrt statisztikákat használjon az elosztott lekérdezésterv kiválasztásakor, a lekérdezésnek el kell férnie a statisztikai objektum definíciójában. 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 statisztika 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 referenciaért lásd a STATISZTIKA LÉTREHOZÁSA című témakört.

Többoszlopos statisztika 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 objektum definíciójában szereplő első oszlophoz érhető el.

Ebben a példában a hisztogram product_category 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 korreláció áll fenn product_category és product_sub_category között, a többoszlopos statisztikai objektumok akkor lehetnek hasznosak, ha ezek az oszlopok egyszerre érhetők el. A tábla lekérdezésekor a többoszlopos statisztika javítja az illesztések, a GROUP BY aggregációk, az eltérő számok és a WHERE szűrők számossági becslését (feltéve, hogy 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 a SQL Server rendszer által tárolt eljárással.sp_create_stats 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ákmal.

Az alábbi példa segítséget nyújt az adatbázis-tervezés első lépéseihez. 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 szeretne létrehozni az alapértelmezett értékekkel, hajtsa végre a tárolt eljárást.

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

Ha a tábla összes oszlopára vonatkozóan szeretne statisztikákat létrehozni egy fullscan paranccsal, hívja meg ezt az eljárást:

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

Ha mintastatisztikát szeretne létrehozni a tábla összes oszlopában, írja be a 3 értéket és a minta százalé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: Statisztikák frissítése

A statisztikák frissítéséhez a következőket teheti:

Frissítsen egy statisztikai objektumot. Adja meg a frissíteni kívánt statisztikai objektum nevét.
Frissítse egy 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]);

Adott statisztikai objektumok frissítésével minimalizálhatja a statisztikák kezeléséhez szükséges időt és erőforrásokat. Ehhez a művelethez szükséges, hogy átgondolja a frissítendő legjobb statisztikai objektumok kiválasztását.

Tábla 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 a tábla összes statisztikáját frissíti, és a szükségesnél több munkát igényel.

Ha a teljesítmény nem jelent problémát, 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 vizsgálatot végez az egyes statisztikai objektumok táblájának mintavételéhez. Ha a táblázat nagy, és sok oszlopot és sok statisztikát tartalmaz, az egyes statisztikák igény szerinti frissítése hatékonyabb lehet.

Az eljárás végrehajtásához UPDATE STATISTICS lásd: Ideiglenes táblák. 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 szintaxist a Statisztika frissítése című témakörben tekintheti meg.

Statisztikai metaadatok

Számos rendszernézetet és függvényt használhat a statisztikákkal kapcsolatos információk kereséséhez. Például a STATS_DATE() függvénnyel 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ókat:

Katalógusnézet	Description
sys.columns	Minden oszlophoz egy sor tartozik.
sys.objects	Az adatbázis minden objektumához egy sor tartozik.
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ázathoz egy sor tartozik (külső táblákat is tartalmaz).
sys.table_types	Minden adattípushoz egy sor tartozik.

Rendszerfüggvények statisztikákhoz

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

Rendszerfüggvény	Description
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 eloszlásáról a statisztikai objektum által értelmezett módon.

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 hoz létre.

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 megjeleníti az értékek eloszlását a statisztikai objektum első kulcsoszlopában.

A sűrűségvektor az oszlopközi korrelációt méri. A dedikált SQL-készlet számossági becslést ad 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() eltérései

DBCC SHOW_STATISTICS()szigorúbban implementálva van a dedikált SQL-készletben, mint a SQL Server:

A nem dokumentált funkciók nem támogatottak.
A Stats_stream nem használható.
A statisztikai adatok adott részhalmazaihoz nem lehet eredményeket illeszteni. Például STAT_HEADER JOIN DENSITY_VECTOR.
NO_INFOMSGS nem állítható be az üzenetek mellőzéséhez.
A statisztikai nevek körüli szögletes zárójelek nem használhatók.
Nem lehet oszlopneveket használni a statisztikai objektumok azonosításához.
A 2767-s egyéni hiba nem támogatott.

Statisztika a kiszolgáló nélküli SQL-készletekben

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

Megjegyzés

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

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. Az adatok statisztikáinak gyűjtése a lekérdezések optimalizálásának egyik legfontosabb feladata.

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éstervek költségeit, majd kiválasztja a legalacsonyabb költségű csomagot. A legtöbb esetben azt a tervet választja ki, amely a leggyorsabb lesz.

Ha például az optimalizáló becslést ad arról, hogy 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 hiányoznak statisztikák, a lekérdezésoptimalizáló statisztikákat hoz létre a lekérdezési predikátum vagy illesztés egyes oszlopairól a lekérdezésterv számosságbecsléseinek javítása érdekében.

A SELECT utasítás elindítja a statisztikák automatikus létrehozását.

Megjegyzés

A statisztikai adatok automatikus létrehozásához mintavételezést használnak, és a legtöbb esetben a mintavétel százalékos aránya kevesebb, mint 100%. Ez a folyamat minden fájlformátumban megegyezik. Ne feledje, hogy az elemző 1.0-s verziójú CSV-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ággal (sorok számával) rendelkező 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 automatikus statisztikák jönnek létre a CSV-hez is az elemző 1.0-s verziójával.

A statisztikák automatikus létrehozása szinkron módon történik, így kismértékben csökkenthet 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 a CSV-vel, valószínűleg manuálisan kell létrehoznia a statisztikákat, mivel 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%.

Az alábbi példákból megtudhatja, hogyan hozhat létre manuálisan statisztikákat.

Statisztika 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 elavulttá teszi a statisztikákat. Ebben az esetben frissíteni kell a statisztikákat.

A kiszolgáló nélküli SQL-készlet automatikusan újra létrehozza a statisztikákat, ha az adatok jelentősen módosulnak. A statisztikák automatikus létrehozásakor a rendszer az adathalmaz aktuális állapotát is menti: fájlelérési utakat, méreteket, utolsó módosítási dátumokat.

Ha a statisztikák elavultak, a rendszer újakat hoz létre. Az algoritmus végighalad az adatokon, és összehasonlítja azokat 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 régi statisztikák törlődnek, és újra létrejönnek az új adatkészleten keresztül.

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

Megjegyzés

A statisztikai adatok automatikus felüdüléséhez mintavételezést használnak, és a mintavétel százalékos aránya a legtöbb esetben kevesebb, mint 100%. Ez a folyamat minden fájlformátumban megegyezik. Ne feledje, hogy ha az elemző 1.0-s verziójú CSV-t olvassa, a mintavételezés nem támogatott, és a statisztikák automatikus feltöltődése nem történik meg 100%-nál kisebb mintavételezési százalékkal. Ebben az esetben manuálisan kell elvetnie és újból létre kell hoznia a statisztikákat. Tekintse meg az alábbi példákat a statisztikák elvetésével és létrehozásával kapcsolatban. A becsült alacsony számossággal (sorok számával) rendelkező kis táblák esetében az automatikus statisztikák 100%-os mintavételezési százalékkal aktiválódnak. Ez alapvetően azt jelenti, hogy a fullscan aktiválódik, és automatikus statisztikák jönnek létre a CSV-hez is az elemző 1.0-s verziójával.

A lekérdezések hibaelhárítása során az egyik első kérdés a következő: "Naprakészek a statisztikák?"

Ha a sorok száma jelentősen megváltozott, vagy egy oszlop értékeinek eloszlása lényegesen megváltozott, akkor ideje frissíteni a statisztikákat.

Megjegyzés

Ha egy oszlop értékeinek eloszlása lényegesen változik, akkor 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 annak érdekében, 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 a frissítés a statisztikai frissítés részeként frissíti a méretadatokat (sorok számát és oldalszámát).
A WHERE, JOIN, GROUP BY, ORDER BY és DISTINCT záradékokban részt vevő oszlopokra összpontosíthat.
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 frissítése ritkábban.

További információ: Számosságbecslés.

Példák: Oszlop statisztikájának létrehozása az OPENROWSET elérési úton

Az alábbi példák bemutatják, hogyan hozhat létre statisztikákat Azure Synapse kiszolgáló nélküli SQL-készletekben. 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 és a végrehajtásához sp_create_openrowset_statisticssp_drop_openrowset_statisticsa következő engedélyek szükségesek: TÖMEGES MŰVELETEK FELÜGYELETE vagy ADATBÁZIS TÖMEGES MŰVELETEINEK FELÜGYELETE.

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' – Olyan Transact-SQL utasítást ad meg, amely a statisztikákhoz használandó oszlopértékeket adja vissza. A TABLESAMPLE használatával megadhatja a használandó adatok mintáit. Ha a TABLESAMPLE nincs megadva, a RENDSZER a FULLSCAN függvényt fogja használni.

<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 1.0-s elemzőverziójú CSV esetében.

Egyoszlopos statisztika 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ákra van szüksége.

Alapértelmezés szerint, ha nem ad meg másként a statisztikák manuális létrehozásakor, a kiszolgáló nélküli SQL-készlet az adathalmazban megadott adatok 100%-át használja a statisztikák létrehozásakor.

Ha például az adathalmaz egy statisztikai oszlopához alapértelmezett beállításokkal (FULLSCAN) szeretne statisztikákat 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 statisztika létrehozása a minta méretének megadásával

A mintaméretet százalékként adhatja meg:

/* 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 frissítése

A statisztikák frissítéséhez el kell dobnia és 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' – Ugyanazt a Transact-SQL utasítást adja meg, amelyet a statisztikák létrehozásakor használnak.

A fájlon alapuló adathalmaz év oszlopának statisztikáinak frissítéséhez el kell helyeznie és létre kell hoznia a population.csv 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 használhat különböző lehetőségeket 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.

Megjegyzés

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

Ha statisztikákat szeretne létrehozni egy oszlopon, adja meg a statistics 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 A statisztikai adatok létrehozásához használható külső táblát adja meg.

FULLSCAN Számítási statisztikák az összes sor vizsgálatával. A FULLSCAN és a SAMPLE 100 PERCENT ugyanazokkal az eredményekkel rendelkezik. A FULLSCAN nem használható a MINTA beállítással.

MINTAszám SZÁZALÉK A tábla vagy indexelt nézet sorainak hozzávetőleges százalékát vagy számát adja meg, hogy a lekérdezésoptimalizáló statisztikai adatok létrehozásakor használhassa. 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 1.0-s elemzőverziójú CSV esetében.

Egyoszlopos statisztika létrehozása minden sor vizsgálatával

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

Egyoszlopos statisztika 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: Statisztikák frissítése

A statisztikák frissítéséhez el kell dobnia és létre kell hoznia a statisztikákat. Először vetje el 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ókat:

Katalógusnézet	Description
sys.columns	Minden oszlophoz egy sor tartozik.
sys.objects	Az adatbázis minden objektumához egy sor tartozik.
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ázathoz egy sor tartozik (külső táblákat is tartalmaz).
sys.table_types	Minden adattípushoz egy sor tartozik.

Rendszerfüggvények statisztikákhoz

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

Rendszerfüggvény	Description
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 hoz létre.

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 számítási feladat figyelését és a dedikált SQL-készlet ajánlott eljárásait.

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észletre vonatkozó ajánlott eljárásokat.

Statisztika a Synapse SQL-ben

Statisztikák dedikált SQL-készletben

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

Statisztikák automatikus létrehozása

Statisztika 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 statisztika létrehozása a minta méretének megadásával

Egyoszlopos statisztika létrehozása csak néhány sorra

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

Többoszlopos statisztika 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: Statisztikák frissítése

Egy adott statisztikai objektum frissítése

Tábla statisztikáinak frissítése

Statisztikai metaadatok

Katalógusnézetek statisztikákhoz

Rendszerfüggvények statisztikákhoz

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() eltérései

Statisztika a kiszolgáló nélküli SQL-készletekben

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

Statisztikák automatikus létrehozása

Statisztikák manuális létrehozása

Statisztika frissítése

Statisztikakezelés implementálása

Példák: Oszlop statisztikájának létrehozása az OPENROWSET elérési úton

Egyoszlopos statisztika létrehozása minden sor vizsgálatával

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

Példák: Statisztikák frissítése

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

Egyoszlopos statisztika létrehozása minden sor vizsgálatával

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

Példák: Statisztikák frissítése

Statisztikai metaadatok

Katalógusnézetek statisztikákhoz

Rendszerfüggvények statisztikákhoz

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

Következő lépések

További források