Ladění výkonu s využitím uspořádaného clusterovaného indexu columnstore
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.
- Dotazy mají predikáty rovnosti, nerovnosti nebo rozsahu.
- 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.
Tady je příklad porovnání výkonu dotazů mezi CCI a seřazeným CCI.
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.
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:
- Vytvořte oddíly v cílové velké tabulce (s názvem
Table_A
). - Vytvořte prázdnou seřazenou tabulku CCI (s názvem
Table_B
) se stejnou tabulkou a schématem oddílů jakoTable_A
. - Přepněte jeden oddíl z
Table_A
naTable_B
. - Spuštěním příkazu
ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID>
znovu sestavte přepnulý oddíl naTable_B
. - Opakujte kroky 3 a 4 pro každý oddíl v nástroji
Table_A
. - Jakmile se všechny oddíly přepnou z
Table_A
Table_B
a znovu se sestaví, vypusťteTable_A
a přejmenujteTable_B
naTable_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í tempdb
pří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říkladcolumn LIKE 'string%'
. Odstranění segmentů není podporováno pro použití funkce LIKE bez předpony, jakocolumn 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
- Další tipy pro vývoj najdete v přehledu vývoje.
- Indexy columnstore – přehled
- Novinky v indexech columnstore
- Indexy columnstore – pokyny k návrhu
- Indexy columnstore – výkon dotazů
Váš názor
https://aka.ms/ContentUserFeedback.
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:Odeslat a zobrazit názory pro