Sdílet prostřednictvím

Statistiky v Synapse SQL

  • Článek

Tento článek obsahuje doporučení a příklady pro vytváření a aktualizaci statistik optimalizace dotazů pomocí prostředků Synapse SQL: vyhrazený fond SQL a bezserverový fond SQL.

Statistika ve vyhrazeném fondu SQL

Proč používat statistiky

Čím více vyhrazený fond SQL ví o vašich datech, tím rychleji může spouštět dotazy. Shromažďování statistik o datech po načtení do vyhrazeného fondu SQL je jednou z nejdůležitějších věcí, které můžete pro optimalizaci dotazů udělat.

Optimalizátor dotazů vyhrazeného fondu SQL je optimalizátor založený na nákladech. Porovná náklady na různé plány dotazů a pak zvolí plán s nejnižšími náklady. Ve většině případů zvolí plán, který se provede nejrychleji.

Pokud například optimalizátor odhadne, že datum, podle kterého dotaz filtruje, vrátí jeden řádek, zvolí jeden plán. Pokud odhadne, že vybrané datum vrátí 1 milion řádků, vrátí jiný plán.

Automatické vytváření statistik

Vyhrazený modul fondu SQL bude analyzovat příchozí dotazy uživatelů z důvodu chybějících statistik, pokud je možnost AUTO_CREATE_STATISTICS databáze nastavená na ONhodnotu . Pokud statistiky chybí, vytvoří optimalizátor dotazů statistiku u jednotlivých sloupců v predikátu dotazu nebo v podmínce spojení.

Tato funkce se používá ke zlepšení odhadů kardinality pro plán dotazu.

Důležité

Automatické vytváření statistik je aktuálně ve výchozím nastavení zapnuté.

Spuštěním následujícího příkazu můžete zkontrolovat, jestli je váš datový sklad nakonfigurovaný AUTO_CREATE_STATISTICS:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Pokud váš datový sklad nemá povolené AUTO_CREATE_STATISTICS, doporučujeme tuto vlastnost povolit spuštěním následujícího příkazu:

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

Tyto příkazy aktivují automatické vytváření statistik:

  • SELECT
  • INSERT-SELECT
  • CTAS
  • UPDATE
  • DELETE
  • EXPLAIN při výskytu spojení nebo zjištění přítomnosti predikátu

Poznámka

Automatické vytváření statistik se negeneruje u dočasných nebo externích tabulek.

Automatické vytváření statistik se provádí synchronně. Pokud tedy ve sloupcích chybí statistika, může dojít k mírnému snížení výkonu dotazů. Doba potřebná k vytvoření statistiky pro jeden sloupec závisí na velikosti tabulky.

Abyste se vyhnuli měřitelnému snížení výkonu, měli byste nejprve zajistit, aby se statistiky vytvořily spuštěním úlohy srovnávacího testu a teprve potom profilací systému.

Poznámka

Vytváření statistik se protokoluje v sys.dm_pdw_exec_requests v jiném kontextu uživatele.

Když se vytvoří automatické statistiky, budou mít podobu: WA_Sys<8místné ID sloupce v hex>_<8místné tabulce v šestnáctkovém> formátu. Již vytvořené statistiky můžete zobrazit spuštěním příkazu DBCC SHOW_STATISTICS :

DBCC SHOW_STATISTICS (<table_name>, <target>)

Table_name je název tabulky, která obsahuje statistické údaje, které se mají zobrazit, což nemůže být externí tabulka. Cíl je název cílového indexu, statistiky nebo sloupce, pro který se mají zobrazit statistické informace.

Aktualizační statistika

Jedním z osvědčených postupů je aktualizovat statistiky sloupců kalendářních dat každý den při přidání nových dat. Pokaždé, když se do datového skladu načtou nové řádky, přidají se nová data načtení nebo data transakcí. Tyto přidané hodnoty mění distribuci dat a za aktuální statistiky.

Statistiky sloupce země nebo oblasti v tabulce zákazníků nemusí být nikdy potřeba aktualizovat, protože rozdělení hodnot se obvykle nemění. Za předpokladu, že je distribuce mezi zákazníky konstantní, přidání nových řádků do varianty tabulky distribuci dat nezmění.

Pokud ale váš datový sklad obsahuje jenom jednu zemi nebo oblast a vy dodáte data z nové země nebo oblasti, budete muset aktualizovat statistiky sloupce země nebo oblasti.

Pro aktualizaci statistik jsou uvedená následující doporučení:

Typ Doporučení
Četnost aktualizací statistik Konzervativní: Každý den
po načtení nebo transformaci dat
Vzorkování Méně než 1 miliarda řádků, použijte výchozí vzorkování (20 procent).
S více než 1 miliardou řádků použijte vzorkování dvou procent.

Určení poslední aktualizace statistiky

Jednou z prvních otázek, které si při řešení potíží s dotazem položte, je : "Jsou statistiky aktuální?"

Na tuto otázku se nedá odpovědět podle stáří dat. Objekt aktuální statistiky může být starý, pokud nedošlo k žádné významné změně podkladových dat. Když se počet řádků podstatně změní nebo dojde k podstatné změně v rozdělení hodnot sloupce, je čas aktualizovat statistiku.

K dispozici není zobrazení dynamické správy, které by určilo, jestli se data v tabulce od poslední aktualizace statistik změnila. Znalost stáří statistik vám může poskytnout část obrázku.

Pomocí následujícího dotazu můžete určit čas poslední aktualizace statistiky v každé tabulce.

Poznámka

Pokud dojde k významné změně v rozdělení hodnot pro sloupec, měli byste aktualizovat statistiky bez ohledu na to, kdy byly naposledy aktualizovány.

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;

Například sloupce kalendářních dat v datovém skladu obvykle vyžadují časté aktualizace statistik. Pokaždé, když se do datového skladu načtou nové řádky, přidají se nová data načtení nebo data transakcí. Tyto přidané hodnoty mění distribuci dat a za aktuální statistiky.

Statistiky sloupce pohlaví v tabulce zákazníků nemusí být nikdy potřeba aktualizovat. Za předpokladu, že je distribuce mezi zákazníky konstantní, přidání nových řádků do varianty tabulky distribuci dat nezmění.

Pokud ale váš datový sklad obsahuje pouze jedno pohlaví a nový požadavek má za následek více pohlaví, budete muset aktualizovat statistiku sloupce pohlaví.

Další informace najdete v článku o statistikách .

Implementace správy statistik

Často je vhodné proces načítání dat rozšířit, aby se statistiky na konci načítání aktualizovaly. K načítání dat dochází, když tabulky nejčastěji mění svou velikost, rozdělení hodnot nebo obojí. Proces načítání je proto logickým místem pro implementaci některých procesů správy.

Pro aktualizaci statistik během procesu načítání jsou k dispozici následující základní principy:

  • Ujistěte se, že každá načtená tabulka obsahuje alespoň jeden objekt statistiky aktualizovaný. Tento proces aktualizuje informace o velikosti tabulky (počet řádků a počet stránek) jako součást aktualizace statistiky.
  • Zaměřte se na sloupce, které se účastní klauzulí JOIN, GROUP BY, ORDER BY a DISTINCT.
  • Zvažte častější aktualizaci sloupců "vzestupného klíče", jako jsou data transakcí, protože tyto hodnoty nebudou zahrnuty do histogramu statistiky.
  • Zvažte méně častou aktualizaci statických distribučních sloupců.
  • Nezapomeňte, že každý objekt statistiky se postupně aktualizuje. Jednoduchá implementace UPDATE STATISTICS <TABLE_NAME> není vždy ideální, zejména u širokých tabulek se spoustou objektů statistiky.

Další informace najdete v tématu Odhad kardinality.

Příklady: Vytvoření statistiky

Tyto příklady ukazují, jak používat různé možnosti pro vytváření statistik. Možnosti, které použijete pro každý sloupec, závisí na charakteristikách dat a na tom, jak se bude sloupec používat v dotazech.

Vytvoření jednosloupcové statistiky s výchozími možnostmi

Pokud chcete vytvořit statistiku sloupce, zadejte název objektu statistiky a název sloupce. Tato syntaxe používá všechny výchozí možnosti. Ve výchozím nastavení vyhrazený fond SQL při vytváření statistik vzorkuje 20 procent tabulky.

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

Příklad:

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

Vytvoření jednosloupkové statistiky prozkoumáním každého řádku

Výchozí vzorkovací frekvence 20 procent je pro většinu situací dostačující. Vzorkovací frekvenci ale můžete upravit. K vzorkování celé tabulky použijte tuto syntaxi:

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

Příklad:

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

Vytvoření jednosloupceové statistiky zadáním velikosti vzorku

Další možností, kterou máte, je zadat velikost vzorku jako procento:

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

Vytvoření jednosloupceové statistiky jenom u některých řádků

Můžete také vytvořit statistiku pro část řádků v tabulce, která se nazývá filtrovaná statistika.

Filtrované statistiky můžete použít například v případě, že plánujete dotazovat konkrétní oddíl velké tabulky s oddíly. Tím, že se vytvoří statistika pouze pro hodnoty oddílů, přesnost statistiky se zlepší. Dojde také ke zlepšení výkonu dotazů.

Tento příklad vytvoří statistiku pro rozsah hodnot. Hodnoty lze snadno definovat tak, aby odpovídaly rozsahu hodnot v oddílu.

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

Poznámka

Aby optimalizátor dotazů při volbě distribuovaného plánu dotazu zvažoval použití filtrovaných statistik, musí se dotaz vejít do definice objektu statistiky. V předchozím příkladu musí klauzule WHERE dotazu zadat hodnoty col1 mezi 2000101 a 20001231.

Vytvoření jednosloupceové statistiky se všemi možnostmi

Tyto možnosti můžete také zkombinovat. Následující příklad vytvoří objekt filtrované statistiky s vlastní velikostí vzorku:

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

Úplné referenční informace najdete v tématu CREATE STATISTICS.

Vytváření statistik s více sloupci

Pokud chcete vytvořit objekt statistiky s více sloupci, použijte předchozí příklady, ale zadejte více sloupců.

Poznámka

Histogram, který se používá k odhadu počtu řádků ve výsledku dotazu, je k dispozici pouze pro první sloupec uvedený v definici objektu statistiky.

V tomto příkladu je histogram na product_category. Statistiky napříč sloupci se počítají podle product_category a product_sub_category:

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

Vzhledem k tomu, že mezi product_category a product_sub_category existuje korelace, může být objekt statistiky s více sloupci užitečný, pokud se k těmto sloupcům přistupuje současně. Při dotazování této tabulky vylepšuje statistika s více sloupci odhad kardinality pro spojení, agregace GROUP BY, jedinečné počty a filtry WHERE (pokud je primární sloupec statistiky součástí filtru).

Vytvoření statistiky pro všechny sloupce v tabulce

Jedním ze způsobů, jak vytvořit statistiku, je vydat příkazy CREATE STATISTICS po vytvoření tabulky:

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);

Použití uložené procedury k vytvoření statistiky pro všechny sloupce v databázi

Fond SQL nemá systémovou uloženou proceduru, která odpovídá sp_create_stats SQL Server. Tato uložená procedura vytvoří jeden objekt statistiky sloupce pro každý sloupec databáze, který ještě nemá statistiky.

Následující příklad vám pomůže začít s návrhem databáze. Nebojte se ho přizpůsobit svým potřebám:

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;

Pokud chcete vytvořit statistiku pro všechny sloupce v tabulce pomocí výchozích nastavení, spusťte uloženou proceduru.

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

Pokud chcete vytvořit statistiku pro všechny sloupce v tabulce pomocí úplného prohledání, zavolejte tento postup:

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

Pokud chcete vytvořit vzorkované statistiky pro všechny sloupce v tabulce, zadejte 3 a procento vzorku. Následující postup používá vzorkovací frekvenci 20 procent.

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

Příklady: Aktualizace statistik

Pokud chcete aktualizovat statistiky, můžete:

  • Aktualizujte jeden objekt statistiky. Zadejte název objektu statistiky, který chcete aktualizovat.
  • Aktualizace všech objektů statistiky v tabulce Místo jednoho konkrétního objektu statistiky zadejte název tabulky.

Aktualizace jednoho konkrétního objektu statistiky

K aktualizaci konkrétního objektu statistiky použijte následující syntaxi:

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

Příklad:

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

Aktualizací konkrétních objektů statistiky můžete minimalizovat čas a prostředky potřebné ke správě statistik. Tato akce vyžaduje určitou promyšlenou volbu nejlepších objektů statistik, které se mají aktualizovat.

Aktualizace všech statistik v tabulce

Jednoduchá metoda aktualizace všech objektů statistiky v tabulce je:

UPDATE STATISTICS [schema_name].[table_name];

Příklad:

UPDATE STATISTICS dbo.table1;

Použití příkazu UPDATE STATISTICS je snadné. Mějte na paměti, že aktualizuje všechny statistiky v tabulce a vyzve k tomu více práce, než je potřeba.

Pokud není problém s výkonem, je tato metoda nejjednodušším a nejúplnějším způsobem, jak zaručit aktuálnou statistiku.

Poznámka

Při aktualizaci všech statistik v tabulce provede vyhrazený fond SQL kontrolu, aby vzorek tabulky pro každý objekt statistiky. Pokud je tabulka velká a obsahuje mnoho sloupců a mnoho statistik, může být efektivnější aktualizovat jednotlivé statistiky na základě potřeby.

Implementaci UPDATE STATISTICS procedury najdete v tématu Dočasné tabulky. Metoda implementace se mírně liší od předchozího CREATE STATISTICS postupu, ale výsledek je stejný. Úplnou syntaxi najdete v tématu Aktualizace statistik.

Metadata statistiky

Existuje několik systémových zobrazení a funkcí, které můžete použít k vyhledání informací o statistikách. Pomocí funkce STATS_DATE() můžete například zjistit, jestli je objekt statistiky zastaralý. STATS_DATE() umožňuje zjistit, kdy se statistiky naposledy vytvořily nebo aktualizovaly.

Zobrazení katalogu pro statistiky

Tato systémová zobrazení poskytují informace o statistikách:

Zobrazení katalogu Popis
sys.columns Jeden řádek pro každý sloupec.
Sys.objects Jeden řádek pro každý objekt v databázi.
sys.schemas Jeden řádek pro každé schéma v databázi.
sys.stats Jeden řádek pro každý objekt statistiky.
sys.stats_columns Jeden řádek pro každý sloupec v objektu statistiky. Odkazuje zpět na sys.columns.
sys.tables Jeden řádek pro každou tabulku (včetně externích tabulek)
sys.table_types Jeden řádek pro každý datový typ.

Systémové funkce pro statistiky

Tyto systémové funkce jsou užitečné pro práci se statistikami:

Systémová funkce Popis
STATS_DATE Datum poslední aktualizace objektu statistiky
DBCC SHOW_STATISTICS Souhrnná úroveň a podrobné informace o rozdělení hodnot podle statistického objektu.

Kombinování sloupců a funkcí statistiky do jednoho zobrazení

Toto zobrazení spojuje sloupce, které se týkají statistiky, a výsledky funkce STATS_DATE().

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
;

Příklady SHOW_STATISTICS() DBCC

DBCC SHOW_STATISTICS() zobrazuje data uložená v objektu statistiky. Tato data se dělí na tři části:

  • Hlavička
  • Vektor hustoty
  • Histogram

Hlavička představuje metadata o statistikách. Histogram zobrazuje rozdělení hodnot v prvním klíčovém sloupci objektu statistiky.

Vektor hustoty měří korelaci mezi sloupci. Vyhrazený fond SQL vypočítá odhad kardinality s libovolnými daty v objektu statistiky.

Zobrazit záhlaví, hustotu a histogram

Tento jednoduchý příklad ukazuje všechny tři části objektu statistiky:

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

Příklad:

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

Zobrazení jedné nebo více částí DBCC SHOW_STATISTICS()

Pokud vás zajímá jenom zobrazení konkrétních částí, použijte klauzuli WITH a určete, které části chcete zobrazit:

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

Příklad:

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

Rozdíly mezi SHOW_STATISTICS() DBCC

DBCC SHOW_STATISTICS()se ve vyhrazeném fondu SQL implementuje striktněji v porovnání s SQL Server:

  • Nezdokumentované funkce nejsou podporovány.
  • Nelze použít Stats_stream.
  • Nelze spojit výsledky pro konkrétní podmnožinu dat statistiky. Například STAT_HEADER JOIN DENSITY_VECTOR.
  • NO_INFOMSGS nelze nastavit pro potlačení zpráv.
  • U názvů statistik nelze použít hranaté závorky.
  • Názvy sloupců nelze použít k identifikaci objektů statistiky.
  • Vlastní chyba 2767 se nepodporuje.

Statistiky v bezserverovém fondu SQL

Pro konkrétní sloupec se vytvářejí statistiky pro konkrétní datovou sadu (cesta k úložišti).

Poznámka

Pro sloupce LOB nelze vytvořit statistiku.

Proč používat statistiky

Čím více bezserverových fondů SQL ví o vašich datech, tím rychleji na něj může provádět dotazy. Shromažďování statistik o datech je jednou z nejdůležitějších věcí, které můžete udělat pro optimalizaci dotazů.

Optimalizátor dotazů fondu SQL bez serveru je optimalizátor založený na nákladech. Porovná náklady na různé plány dotazů a pak zvolí plán s nejnižšími náklady. Ve většině případů zvolí plán, který se provede nejrychleji.

Pokud například optimalizátor odhadne, že datum, podle kterého se dotaz filtruje, vrátí jeden řádek, zvolí jeden plán. Pokud odhadne, že vybrané datum vrátí 1 milion řádků, vybere jiný plán.

Automatické vytváření statistik

Bezserverový fond SQL analyzuje příchozí dotazy uživatelů z důvodu chybějících statistik. Pokud chybí statistika, vytvoří optimalizátor dotazů statistiku pro jednotlivé sloupce v predikátu dotazu nebo podmínce spojení, aby zlepšil odhad kardinality pro plán dotazu.

Příkaz SELECT aktivuje automatické vytváření statistik.

Poznámka

Pro automatické vytváření statistik se používá vzorkování a ve většině případů bude procento vzorkování menší než 100 %. Tento tok je stejný pro každý formát souboru. Mějte na paměti, že při čtení sdíleného svazku clusteru s analyzátorem verze 1.0 se vzorkování nepodporuje a automatické vytváření statistik se nestane s procentem vzorkování nižším než 100 %. U malých tabulek s odhadovanou nízkou kardinalitou (počet řádků) se aktivuje automatické vytváření statistik s procentem vzorkování 100 %. To v podstatě znamená, že se aktivuje úplné prohledání a vytvoří se automatické statistiky i pro sdílený svazek clusteru s analyzátorem verze 1.0.

Automatické vytváření statistik se provádí synchronně, takže pokud ve sloupcích chybí statistika, může dojít k mírnému snížení výkonu dotazů. Doba potřebná k vytvoření statistiky pro jeden sloupec závisí na velikosti cílových souborů.

Ruční vytváření statistik

Bezserverový fond SQL umožňuje vytvářet statistiky ručně. Pokud používáte analyzátor verze 1.0 se sdíleným svazkem clusteru, budete pravděpodobně muset vytvořit statistiky ručně, protože tato verze analyzátoru vzorkování nepodporuje. V případě analyzátoru verze 1.0 nedojde k automatickému vytvoření statistiky, pokud procento vzorkování nebude 100 %.

Pokyny k ručnímu vytváření statistik najdete v následujících příkladech.

Aktualizační statistika

Změny dat v souborech, odstranění a přidání souborů vedou ke změnám distribuce dat a statistiky jsou zastaralé. V takovém případě je potřeba aktualizovat statistiky.

Bezserverový fond SQL automaticky znovu vytváří statistiky, pokud se data výrazně změní. Pokaždé, když se automaticky vytvoří statistika, uloží se také aktuální stav datové sady: cesty k souborům, velikosti a data poslední změny.

Když jsou statistiky zastaralé, vytvoří se nové. Algoritmus projde data a porovná je s aktuálním stavem datové sady. Pokud je velikost změn větší než konkrétní prahová hodnota, staré statistiky se odstraní a znovu se vytvoří v nové datové sadě.

Ruční statistiky nejsou nikdy deklarovány jako zastaralé.

Poznámka

Pro automatickou rekreaci statistik se používá vzorkování a ve většině případů bude procento vzorkování menší než 100 %. Tento tok je stejný pro každý formát souboru. Mějte na paměti, že při čtení sdíleného svazku clusteru s analyzátorem verze 1.0 se vzorkování nepodporuje a nedojde k automatickému vytvoření statistiky s procentem vzorkování nižším než 100 %. V takovém případě je potřeba statistiku ručně vypustit a znovu vytvořit. V následujících příkladech se dozvíte, jak odstranit a vytvořit statistiku. U malých tabulek s odhadovanou nízkou kardinalitou (počet řádků) se aktivuje automatická rekreace statistik s procentem vzorkování 100 %. To v podstatě znamená, že se aktivuje úplné prohledání a vytvoří se automatické statistiky i pro sdílený svazek clusteru s analyzátorem verze 1.0.

Jednou z prvních otázek, které si při řešení potíží s dotazem položte, je : "Jsou statistiky aktuální?"

Pokud se počet řádků podstatně změnil nebo došlo k podstatné změně rozdělení hodnot sloupce, je čas aktualizovat statistiku.

Poznámka

Pokud dojde k významné změně v rozdělení hodnot pro sloupec, měli byste aktualizovat statistiky bez ohledu na to, kdy byly naposledy aktualizovány.

Implementace správy statistik

Datový kanál můžete chtít rozšířit, aby se statistiky aktualizovaly, když se data výrazně změní přidáním, odstraněním nebo změnou souborů.

Pro aktualizaci statistik jsou k dispozici následující základní principy:

  • Ujistěte se, že datová sada obsahuje alespoň jeden objekt statistiky aktualizovaný. Tím se v rámci aktualizace statistiky aktualizují informace o velikosti (počet řádků a počet stránek).
  • Zaměřte se na sloupce, které se účastní klauzulí WHERE, JOIN, GROUP BY, ORDER BY a DISTINCT.
  • Aktualizujte sloupce "vzestupného klíče", jako jsou data transakcí, častěji, protože tyto hodnoty nebudou zahrnuty do histogramu statistiky.
  • Méně často aktualizujte statické distribuční sloupce.

Další informace najdete v tématu Odhad kardinality.

Příklady: Vytvoření statistiky pro sloupec v cestě OPENROWSET

Následující příklady ukazují, jak používat různé možnosti pro vytváření statistik v Azure Synapse bezserverových fondech SQL. Možnosti, které použijete pro každý sloupec, závisí na charakteristikách dat a na tom, jak se bude sloupec používat v dotazech. Další informace o uložených procedurách použitých v těchto příkladech najdete v tématu sys.sp_create_openrowset_statistics a sys.sp_drop_openrowset_statistics, které platí jenom pro bezserverové fondy SQL.

Poznámka

Jednosloupcenou statistiku můžete vytvořit jenom v tuto chvíli.

Ke spouštění sp_create_openrowset_statistics a sp_drop_openrowset_statisticsjsou vyžadována následující oprávnění: SPRÁVA HROMADNÝCH OPERACÍ nebo SPRÁVA HROMADNÝCH OPERACÍ S DATABÁZÍ.

K vytvoření statistik se používá následující uložená procedura:

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

Argumenty: [ @stmt = ] N'statement_text' – Určuje příkaz jazyka Transact-SQL, který vrátí hodnoty sloupců, které se mají použít pro statistiku. Pomocí funkce TABLESAMPLE můžete určit vzorky dat, které se mají použít. Pokud není zadaný parametr TABLESAMPLE, použije se FULLSCAN.

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

Poznámka

Pokud používáte analyzátor verze 1.0, vzorkování csv nefunguje, pro sdílený svazek clusteru s analyzátorem verze 1.0 se podporuje pouze FULLSCAN.

Vytvoření jednosloupkové statistiky prozkoumáním každého řádku

Pokud chcete vytvořit statistiku sloupce, zadejte dotaz, který vrátí sloupec, pro který potřebujete statistiku.

Pokud při ručním vytváření statistik neurčíte jinak, bezserverový fond SQL při vytváření statistik použije 100 % dat zadaných v datové sadě.

Pokud chcete například vytvořit statistiku s výchozími možnostmi (FULLSCAN) pro sloupec populace datové sady na základě us_population.csv souboru:


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

Vytvoření jednosloupceové statistiky zadáním velikosti vzorku

Velikost vzorku můžete zadat jako procento:

/* 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říklady: Aktualizace statistik

Pokud chcete aktualizovat statistiky, musíte statistiku vypustit a vytvořit. Další informace najdete v tématu sys.sp_create_openrowset_statistics a sys.sp_drop_openrowset_statistics.

Uložená procedura sys.sp_drop_openrowset_statistics slouží k vyřazení statistik:

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

Poznámka

Ke spouštění sp_create_openrowset_statistics a sp_drop_openrowset_statisticsjsou vyžadována následující oprávnění: SPRÁVA HROMADNÝCH OPERACÍ nebo SPRÁVA HROMADNÝCH OPERACÍ S DATABÁZÍ.

Argumenty: [ @stmt = ] N'statement_text' – Určuje stejný příkaz jazyka Transact-SQL použitý při vytváření statistik.

Pokud chcete aktualizovat statistiku pro sloupec rok v datové sadě, která je založená na souboru, musíte statistiku population.csv odstranit a vytvořit:

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říklady: Vytvoření statistiky pro sloupec externí tabulky

Následující příklady ukazují, jak používat různé možnosti pro vytváření statistik. Možnosti, které použijete pro každý sloupec, závisí na charakteristikách dat a na tom, jak se bude sloupec používat v dotazech.

Poznámka

Jednosloupcenou statistiku můžete vytvořit jenom v tuto chvíli.

Pokud chcete vytvořit statistiku sloupce, zadejte název objektu statistiky a název sloupce.

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

Argumenty: external_table Určuje externí tabulku, která má vytvořit statistiku.

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

ČÍSLO VZORKU PROCENT: Určuje přibližné procento nebo počet řádků v tabulce nebo indexovém zobrazení, které optimalizátor dotazů použije při vytváření statistik. Číslo může být od 0 do 100.

SAMPLE nelze použít s možností FULLSCAN.

Poznámka

Pokud používáte analyzátor verze 1.0, vzorkování csv nefunguje, pro sdílený svazek clusteru s analyzátorem verze 1.0 se podporuje pouze FULLSCAN.

Vytvoření jednosloupkové statistiky prozkoumáním každého řádku

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

Vytvoření jednosloupceové statistiky zadáním velikosti vzorku

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

Příklady: Aktualizace statistik

Pokud chcete aktualizovat statistiky, musíte statistiku vypustit a vytvořit. Nejprve odstraňte statistiku:

DROP STATISTICS census_external_table.sState

A vytvořte statistiky:

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

Metadata statistiky

Existuje několik systémových zobrazení a funkcí, které můžete použít k vyhledání informací o statistikách. Pomocí funkce STATS_DATE() můžete například zjistit, jestli je objekt statistiky zastaralý. STATS_DATE() umožňuje zjistit, kdy se statistiky naposledy vytvořily nebo aktualizovaly.

Poznámka

Metadata statistiky jsou k dispozici pouze pro sloupce externí tabulky. Pro sloupce OPENROWSET nejsou k dispozici metadata statistiky.

Zobrazení katalogu pro statistiky

Tato systémová zobrazení poskytují informace o statistikách:

Zobrazení katalogu Popis
sys.columns Jeden řádek pro každý sloupec.
Sys.objects Jeden řádek pro každý objekt v databázi.
sys.schemas Jeden řádek pro každé schéma v databázi.
sys.stats Jeden řádek pro každý objekt statistiky.
sys.stats_columns Jeden řádek pro každý sloupec v objektu statistiky. Odkazuje zpět na sys.columns.
sys.tables Jeden řádek pro každou tabulku (včetně externích tabulek)
sys.table_types Jeden řádek pro každý datový typ.

Systémové funkce pro statistiky

Tyto systémové funkce jsou užitečné pro práci se statistikami:

Systémová funkce Popis
STATS_DATE Datum poslední aktualizace objektu statistiky

Kombinování sloupců a funkcí statistiky do jednoho zobrazení

Toto zobrazení spojuje sloupce, které se týkají statistiky, a výsledky funkce STATS_DATE().

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
;

Další kroky

Pokud chcete dále zlepšit výkon dotazů pro vyhrazený fond SQL, projděte si téma Monitorování úloh a Osvědčené postupy pro vyhrazený fond SQL.

Pokud chcete dále zlepšit výkon dotazů pro bezserverový fond SQL, projděte si téma Osvědčené postupy pro bezserverový fond SQL.