Statistiky v Synapse SQL
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.
Váš názor
Připravujeme: V průběhu roku 2024 budeme postupně vyřazovat problémy z GitHub coby mechanismus zpětné vazby pro obsah a nahrazovat ho novým systémem zpětné vazby. Další informace naleznete v tématu: https://aka.ms/ContentUserFeedback.
Odeslat a zobrazit názory pro