Share via

Ladění výkonu s využitím uspořádaného clusterovaného indexu columnstore

  • Článek

Platí pro: vyhrazené fondy SQL Azure Synapse Analytics, SQL Server 2022 (16.x) a novější

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 každém segmentu. 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ů, které se mají přečíst, a jejich celková velikost malá.

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 CCI rowgroup. V rozsahu hodnot každého segmentu 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 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 více segmentů z disku a jejich dokončení bude trvat déle.

Seřazené clusterované indexy columnstore díky tomu, že umožňují efektivní odstranění segmentů, což vede k mnohem rychlejšímu výkonu díky přeskočení velkých objemů uspořádaných dat, která neodpovídají predikátu dotazu. Při vytváření seřazeného CCI seřadí vyhrazený modul fondu SQL existující data v paměti podle klíčů objednávek předtím, než je tvůrce indexů zkomprimuje do segmentů indexu. U seřazených dat dochází k omezení překrývání segmentů, což umožňuje dotazům efektivnější odstranění segmentů a tím i rychlejší výkon, protože počet segmentů, které se mají číst z disku, je menší. Pokud se všechna data dají řadit v paměti najednou, můžete se vyhnout překrývání segmentů. Kvůli velkým tabulkám v datových skladech k tomuto scénáři nedochází často.

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 jsou nová data vyplývající ze stejné dávky DML nebo operací načítání dat seřazena v rámci této dávky, neexistuje žádné globální řazení všech dat v tabulce. Uživatelé můžou uspořádaný CCI znovu sestavit a seřadit všechna data v tabulce. Ve vyhrazeném fondu SQL je index columnstore REBUILD offline operací. V případě tabulky s oddíly se funkce REBUILD provádí po jednom oddílu. Data v oddílu, který se znovu sestavuje, jsou offline a nedostupná, dokud se opětovné sestavení pro tento oddíl nedokončí.

Výkon dotazů

Zvýšení výkonu dotazu z seřazeného CCI závisí na vzorech dotazu, velikosti dat, tom, jak dobře jsou data seřazená, na fyzické struktuře segmentů a na dwu a třídě prostředků zvolené pro provádění dotazu. Před výběrem sloupců řazení při návrhu seřazené tabulky CCI by uživatelé měli zkontrolovat všechny tyto faktory.

Dotazy se všemi těmito vzory obvykle běží rychleji s seřazeným 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 pořadí Col_C, Col_B a Col_A.

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

Výkon dotazů 1 a dotaz 2 může mít větší užitek z seřazených CCI než ostatní dotazy, 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 je podobný jako u dělené tabulky. Načtení dat do seřazené tabulky CCI může kvůli operaci řazení dat trvat déle než neuspořádaná tabulka CCI, ale dotazy pak můžou s seřazeným CCI běžet rychleji.

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 při 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á kratší dobu trvání.

Omezení překrývání segmentů

Počet překrývajících se segmentů závisí na velikosti dat, která se mají seřadit, dostupné paměti a nastavení maximálního stupně paralelismu (MAXDOP) během vytváření CCI. Následující strategie omezují překrývání segmentů při vytváření uspořádaných CCI.

  • Použijte xlargerc třídu prostředků na vyšší DWU, abyste umožnili více paměti pro řazení dat předtím, než tvůrce indexů zkomprimuje data do segmentů. Jakmile jste v indexovém segmentu, nelze změnit fyzické umístění dat. Data se neřadí v rámci segmentu ani mezi segmenty.

  • Pomocí příkazu vytvořte uspořádané CCI OPTION (MAXDOP = 1). Každé vlákno použité pro seřazené vytvoření CCI funguje na podmnožině dat a místně je seřadí. Neexistuje žádné globální řazení dat seřazených podle různých vláken. Použití paralelních vláken může zkrátit dobu vytváření seřazeného CCI, ale vygeneruje více překrývajících se segmentů než při použití jednoho vlákna. Použití operace s jedním vláknem 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í seřazeného CCI pomocí CREATE INDEX příkazů nebo CREATE TABLE nepodporuje možnost MAXDOP. Toto omezení se nevztahuje na SQL Server 2022 a novější verze, kde můžete zadat MAXDOP pomocí CREATE INDEX příkazů nebo CREATE TABLE .

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

Tady je příklad seřazené distribuce tabulky CCI, která má překrývání nulového segmentu podle výše uvedených doporučení. Seřazená tabulka CCI se vytvoří v databázi DWU1000c prostřednictvím CTAS z tabulky haldy o velikosti 20 GB pomocí maxdOP 1 a xlargerc. CCI je seřazený podle sloupce BIGINT bez duplicit.

Snímek obrazovky s textovými daty, ve kterých se nepřekrývá žádný segment

Vytvoření uspořádaných CCI u velkých tabulek

Vytvoření seřazeného CCI je offline operace. U tabulek bez oddílů nebudou data uživatelům přístupná, dokud se nedokončí uspořádaný proces vytváření CCI. V případě dělených tabulek platí, že vzhledem k tomu, že modul vytváří seřazený oddíl CCI podle oddílů, mají uživatelé stále přístup k datům v oddílech, kde se neprobíjí seřazené vytváření CCI. Pomocí této možnosti můžete minimalizovat prostoje při vytváření objednaných CCI u velkých tabulek:

  1. Vytvořte oddíly v cílové velké tabulce (s názvem Table_A).
  2. Vytvořte prázdnou seřazenou tabulku CCI (s názvem Table_B) se stejnou tabulkou a schématem oddílů jako Table_A.
  3. Přepněte jeden oddíl z Table_A na Table_B.
  4. Spuštěním příkazu ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> znovu sestavte přepnulý oddíl na Table_B.
  5. Opakujte kroky 3 a 4 pro každý oddíl v nástroji Table_A.
  6. Jakmile se všechny oddíly přepnou z Table_ATable_B a znovu se sestaví, vypusťte Table_Aa přejmenujte Table_B na Table_A.

Tip

V případě tabulky vyhrazeného fondu SQL s seřazeným CCI příkazEM ALTER INDEX REBUILD znovu seřadí data pomocí tempdbpříkazu . 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ě snižte kapacitu.

V případě tabulky vyhrazeného fondu SQL s seřazeným CCI příkaz ALTER INDEX REORGANIZE data nepřeřadí. Pokud chcete data využít, použijte příkaz ALTER INDEX REBUILD.

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

Rozdíly ve funkcích SQL Server 2022

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

  • V současné době pouze SQL Server 2022 (16.x) a novější verze podporují clusterované možnosti odstranění rozšířeného segmentu columnstore pro datové typy string, binary a GUID a datový typ datetimeoffset pro škálování větší než dva. Dříve se toto vyloučení segmentu týká číselných datových typů, datových typů data, data a času a datového typu datetimeoffset se škálováním menším nebo rovným dvěma.
  • V současné době podporují skupinové odstranění skupiny řádků columnstore pouze SQL Server 2022 (16.x) a novější verze pro předponu LIKE predikátů, například column LIKE 'string%'. Odstranění segmentů není podporováno pro použití funkce LIKE bez předpony, jako column LIKE '%string'je .

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

Příklady

A. Postup kontroly seřazených sloupců a pořadových řad:

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 pořadí nebo změňte z CCI na seřazené CCI:

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

Další kroky