Sdílet prostřednictvím

Ladění výkonu s seřazeným clusterovaným indexem columnstore ve službě Azure Synapse Analytics

  • Článek

Platí pro: Vyhrazené fondy SQL služby Azure Synapse Analytics

Když se uživatelé dotazují na tabulku columnstore ve vyhrazeném fondu SQL, optimalizátor zkontroluje minimální a maximální hodnoty uložené v jednotlivých segmentech. Segmenty, které jsou mimo hranice predikátu dotazu, se nečtou z disku do paměti. Dotaz se může dokončit rychleji, pokud je počet segmentů ke čtení a jejich celková velikost malá.

Poznámka:

Tento článek se týká vyhrazených fondů SQL služby Azure Synapse Analytics. Informace o seřazených indexech columnstore na SQL Serveru a dalších platformách SQL najdete v tématu Ladění výkonu s seřazenými clusterovanými indexy columnstore.

Seřazený vs. neřazený clusterovaný index columnstore

Ve výchozím nastavení pro každou tabulku vytvořenou bez možnosti indexu vytvoří interní komponenta (tvůrce indexů) neřazený clusterovaný index columnstore (CCI). Data v každém sloupci se komprimují do samostatného segmentu skupiny řádků CCI. V rozsahu hodnot jednotlivých segmentů jsou metadata, takže segmenty, které jsou mimo hranice predikátu dotazu, se během provádění dotazu nečtou z disku. CCI nabízí nejvyšší úroveň komprese dat a snižuje velikost segmentů pro čtení, aby dotazy mohly běžet rychleji. Vzhledem k tomu, že tvůrce indexů neřadí data před jejich komprimací do segmentů, může dojít k segmentům s překrývajícími se rozsahy hodnot, což způsobí, že dotazy budou číst další segmenty z disku a jejich dokončení trvá déle.

Uspořádané clusterované indexy columnstore tím, že umožňují efektivní odstranění segmentů, což vede k mnohem rychlejšímu výkonu přeskočením velkých objemů seřazených dat, která neodpovídají predikátu dotazu. Při vytváření uspořádané CCI seřadí vyhrazený modul fondu SQL existující data v paměti podle klíčů pořadí před tím, než je tvůrce indexů zkomprimuje do segmentů indexu. Díky seřazeným datům se zmenší překrývání segmentů, což umožňuje dotazům dosáhnout efektivnějšího odstranění segmentů a tím rychlejšího výkonu, protože počet segmentů pro čtení z disku je menší. Pokud lze řadit všechna data v paměti najednou, můžete se vyhnout překrývání segmentů. Vzhledem k velkým tabulkám v datových skladech se tento scénář často neprovádí.

Pokud chcete zkontrolovat rozsahy segmentů pro sloupec, spusťte následující příkaz s názvem tabulky a názvem sloupce:

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

Poznámka:

V seřazené tabulce CCI se nová data vyplývající ze stejné dávky DML nebo operací načítání dat se seřadí v rámci této dávky, takže neexistuje globální řazení napříč všemi daty v tabulce. Uživatelé mohou uspořádané CCI znovu sestavit, aby seřadily všechna data v tabulce. Ve vyhrazeném fondu SQL je index columnstore REBUILD offline operací. U dělené tabulky se funkce REBUILD provádí po jednom oddílu. Data v znovu sestaveném oddílu jsou offline a nedostupná, dokud se pro tento oddíl nedokončí opětovné sestavení.

Výkon dotazů

Výkon dotazu z seřazeného CCI závisí na vzorech dotazu, velikosti dat, způsobu řazení dat, fyzické struktuře segmentů a třídě DWU a třídy prostředků zvolené pro provádění dotazu. Uživatelé by měli před výběrem sloupců řazení při návrhu uspořádané tabulky CCI zkontrolovat všechny tyto faktory.

Dotazy se všemi těmito vzory obvykle běží rychleji pomocí seřazených CCI.

  1. Dotazy mají predikáty rovnosti, nerovnosti nebo rozsahu.
  2. Sloupce predikátu a seřazené sloupce CCI jsou stejné.

V tomto příkladu má tabulka T1 clusterovaný index columnstore seřazený v posloupnosti Col_C, Col_B a Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON  T1
ORDER (Col_C, Col_B, Col_A);

Výkon dotazu 1 a dotazu 2 může těžit z seřazených CCI než u ostatních dotazů, protože odkazují na všechny seřazené sloupce CCI.

-- Query #1:

SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- Query #2

SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';

-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';

Výkon načítání dat

Výkon načítání dat do seřazené tabulky CCI se podobá dělené tabulce. Načtení dat do seřazené tabulky CCI může trvat déle než tabulka CCI, která není seřazená, protože operace řazení dat však dotazy můžou běžet rychleji s seřazenými CCI.

Tady je příklad porovnání výkonu načítání dat do tabulek s různými schématy.

Pruhový graf znázorňující porovnání výkonu načítání dat do tabulek s různými schématy

Tady je příklad porovnání výkonu dotazů mezi CCI a seřazeným CCI.

Pruhový graf porovnávající výkon během data_loading Seřazený clusterovaný index columnstore má nižší dobu trvání.

Zmenšení překrývajících se segmentů

Počet překrývajících se segmentů závisí na velikosti dat, která se mají seřadit, dostupnou paměť a maximální stupeň paralelismu (MAXDOP) během seřazeného vytváření CCI. Následující strategie snižují překrývající se segmenty při vytváření uspořádaných CCI.

  • Třída xlargerc prostředků ve vyšší dwU umožňuje více paměti pro řazení dat, než tvůrce indexu zkomprimuje data do segmentů. Jakmile je v indexovém segmentu, fyzické umístění dat nelze změnit. V rámci segmentu nebo napříč segmenty neexistuje žádné řazení dat.

  • Vytvořit seřazené CCI s OPTION (MAXDOP = 1). Každé vlákno použité pro seřazené vytváření CCI funguje na podmnožině dat a místně je seřadí. Neexistuje globální řazení mezi daty seřazenými podle různých vláken. Použití paralelních vláken může zkrátit dobu vytvoření seřazeného CCI, ale vygeneruje překrývající se segmenty než použití jednoho vlákna. Použití jedné operace s vlákny poskytuje nejvyšší kvalitu komprese. Příklad:

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Poznámka:

V současné době se ve vyhrazených fondech SQL ve službě Azure Synapse Analytics podporuje možnost MAXDOP pouze při vytváření seřazené tabulky CCI pomocí CREATE TABLE AS SELECT příkazu. Vytvoření uspořádané CCI prostřednictvím příkazů CREATE INDEX CREATE TABLE nepodporuje možnost MAXDOP. Toto omezení neplatí pro SQL Server 2022 a novější verze, kde můžete zadat MAXDOP pomocí CREATE INDEX příkazů nebo CREATE TABLE příkazů.

  • Před načtením dat do tabulek je předem seřaďte podle klíčů řazení.

Tady je příklad seřazené distribuce tabulky CCI, která má nulový segment překrývající se podle výše uvedených doporučení. Seřazená tabulka CCI se vytvoří v databázi DWU1000c prostřednictvím CTAS z tabulky haldy 20 GB pomocí MAXDOP 1 a xlargerc. CCI je seřazený ve sloupci BIGINT bez duplicit.

Snímek obrazovky s textovými daty zobrazující nepřekrývání segmentů

Vytvoření seřazených CCI ve velkých tabulkách

Vytvoření seřazeného CCI je offline operace. U tabulek bez oddílů nebudou data přístupná uživatelům, dokud se nedokončí uspořádaný proces vytváření CCI. V případě dělených tabulek, protože modul vytvoří uspořádaný oddíl CCI podle oddílu, mají uživatelé stále přístup k datům v oddílech, kde se seřazené vytváření CCI nepracuje. Tuto možnost můžete použít k minimalizaci výpadků během objednaných vytváření CCI u velkých tabulek:

  1. Vytvořte oddíly v cílové velké tabulce (volali Table_A).
  2. Vytvořte prázdnou uspořádanou tabulku CCI (volanou Table_B) se stejnou tabulkou a schématem oddílů jako Table_A.
  3. Přepněte jeden oddíl z Table_A do Table_B.
  4. Spusťte ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> opětovné sestavení přepínacího oddílu na .Table_B
  5. Opakujte krok 3 a 4 pro každý oddíl v souboru Table_A.
  6. Jakmile jsou všechny oddíly přepnuty z Table_A Table_B a byly znovu sestaveny, odstraňte Table_Aa přejmenujte Table_B na Table_A.

Tip

Pro vyhrazenou tabulku fondu SQL s seřazeným CCI, ALTER INDEX REBUILD znovu seřadí data pomocí tempdb. Monitorování tempdb během operací opětovného sestavení Pokud potřebujete více tempdb místa, vertikálně navyšte kapacitu fondu. Po dokončení opětovného sestavení indexu vertikálně snížit kapacitu.

Pro vyhrazenou tabulku fondu SQL s seřazeným CCI, ALTER INDEX REORGANIZE nepřeřazuje data. Pokud chcete data uchystit, použijte PŘÍKAZ ALTER INDEX REBUILD.

Další informace o objednané údržbě CCI naleznete v tématu Optimalizace clusterovaných indexů columnstore.

Rozdíly mezi funkcemi SQL Serveru 2022

SQL Server 2022 (16.x) zavedl uspořádané clusterované indexy columnstore podobné funkci ve vyhrazených fondech SQL Azure Synapse.

  • V současné době podporují pro řetězcové, binární a guid datové typy pouze SQL Server 2022 (16.x) a novější verze clusterované funkce odstranění segmentů columnstore s rozšířeným rozsahem a datový typ datetimeoffset pro škálování větší než dvě. Dříve se odstranění tohoto segmentu vztahuje na číselné datové typy, datum a čas a datový typ datetimeoffset s měřítkem menší nebo rovnou dvěma.
  • V současné době pouze SQL Server 2022 (16.x) a novější verze podporují odstranění skupin řádků clusterovaného columnstore pro předponu LIKE predikátů, například column LIKE 'string%'. Vyloučení segmentů není podporováno pro použití typu LIKE, například column LIKE '%string'.

Další informace najdete v tématu Co je nového v indexech Columnstore.

Příklady

A. Kontrola seřazených sloupců a řadových sloupců:

SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;

B. Pokud chcete změnit pořadí sloupců, přidejte nebo odeberte sloupce ze seznamu objednávek nebo změňte pořadí CCI na seřazené CCI:

CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);

Další kroky