Teljesítmény-finomhangolás rendezett fürtözött oszlopcentrikus index használatával

  • Cikk

A következőkre vonatkozik: Azure Synapse Analytics dedikált SQL-készlet, SQL Server 2022 (16.x) és újabb verziók

Amikor a felhasználók egy dedikált SQL-készletben lévő oszlopcentrikus táblát kérdeznek le, az optimalizáló ellenőrzi az egyes szegmensekben tárolt minimális és maximális értékeket. A lekérdezési predikátum határain kívül eső szegmensek nem lesznek beolvasva a lemezről a memóriába. A lekérdezések gyorsabban befejeződhetnek, ha az olvasandó szegmensek száma és a teljes méretük kicsi.

Rendezett és nem rendezett fürtözött oszlopcentrikus index

Alapértelmezés szerint minden indexbeállítás nélkül létrehozott tábla esetében egy belső összetevő (indexszerkesztő) létrehoz egy nem rendezett fürtözött oszlopcentrikus indexet (CCI). Az egyes oszlopokban lévő adatok külön CCI sorcsoport-szegmensbe lesznek tömörítve. Az egyes szegmensek értéktartományában vannak metaadatok, így a lekérdezési predikátum határain kívül eső szegmensek nem lesznek beolvasva a lemezről a lekérdezés végrehajtása során. A CCI a legmagasabb szintű adattömörítést biztosítja, és csökkenti az olvasási szegmensek méretét, hogy a lekérdezések gyorsabban fussanak. Mivel azonban az indexszerkesztő nem rendezi az adatokat, mielőtt szegmensekbe tömörítené őket, egymást átfedő értéktartományokkal rendelkező szegmensek fordulhatnak elő, így a lekérdezések több szegmenst olvasnak be a lemezről, és tovább tart a befejezésük.

Rendezett fürtözött oszlopcentrikus indexek a hatékony szegmenseliiminálás engedélyezésével, ami sokkal gyorsabb teljesítményt eredményez azáltal, hogy kihagy nagy mennyiségű rendezett adatot, amelyek nem felelnek meg a lekérdezési predikátumnak. Rendezett CCI létrehozásakor a dedikált SQL-készletmotor a rendelési kulcs(ok) alapján rendezi a meglévő adatokat a memóriában, mielőtt az indexszerkesztő indexszegmensekbe tömörítené őket. A rendezett adatok esetében a szegmensek átfedése csökken, így a lekérdezések hatékonyabb szegmenseliminálást és ezáltal gyorsabb teljesítményt eredményeznek, mivel a lemezről beolvasandó szegmensek száma kisebb. Ha az összes adat egyszerre rendezhető a memóriában, akkor elkerülhető a szegmensek átfedése. Az adattárházakban lévő nagy táblák miatt ez a forgatókönyv nem gyakran fordul elő.

Egy oszlop szegmenstartományainak ellenőrzéséhez futtassa a következő parancsot a tábla nevével és az oszlop nevével:

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;

Megjegyzés

Egy rendezett CCI-táblában az ugyanazon DML- vagy adatbetöltési műveletekből származó új adatok az adott kötegen belül vannak rendezve, a tábla összes adata között nincs globális rendezés. A felhasználók újraépíthetik a rendezett CCI-t a tábla összes adatának rendezéséhez. A dedikált SQL-készletben az oszlopcentrikus index REBUILD egy offline művelet. Particionált tábla esetén a REBUILD egy partíciót hoz létre egyszerre. Az újraépített partícióban lévő adatok "offline" állapotban vannak, és nem érhetők el, amíg a REBUILD be nem fejeződik az adott partícióhoz.

Lekérdezési teljesítmény

A lekérdezések rendezett CCI-ből származó teljesítménynövekedése a lekérdezési mintáktól, az adatok méretétől, az adatok rendezettségétől, a szegmensek fizikai szerkezetétől, valamint a lekérdezés végrehajtásához kiválasztott DWU- és erőforrásosztálytól függ. A felhasználóknak érdemes áttekinteniük ezeket a tényezőket, mielőtt kiválasztanák a rendezési oszlopokat egy rendezett CCI-tábla tervezésekor.

Az összes ilyen mintával rendelkező lekérdezések általában gyorsabban futnak a rendezett CCI-vel.

  1. A lekérdezések egyenlőségi, egyenlőtlenségi vagy tartománypredikátumokkal rendelkeznek
  2. A predikátumoszlopok és a rendezett CCI-oszlopok megegyeznek.

Ebben a példában a T1 tábla egy fürtözött oszlopcentrikus indexet tartalmaz, amely Col_C, Col_B és Col_A sorrendben van rendezve.

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

Az 1. lekérdezés és a 2. lekérdezés teljesítménye jobban kihasználhatja a rendezett CCI-t, mint a többi lekérdezést, mivel az összes rendezett CCI-oszlopra hivatkoznak.

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

Adatbetöltési teljesítmény

A rendezett CCI-táblába való adatbetöltés teljesítménye hasonló a particionált táblákhoz. Az adatok rendezett CCI-táblába való betöltése hosszabb időt vehet igénybe, mint a nem rendezett CCI-táblák az adatrendezési művelet miatt, de a lekérdezések később gyorsabban futhatnak a rendezett CCI-vel.

Íme egy példa az adatok különböző sémákkal rendelkező táblákba való betöltésének teljesítmény-összehasonlítására.

Sávdiagram, amely az adatok különböző sémákkal rendelkező táblákba való betöltésének teljesítmény-összehasonlítását mutatja be.

Íme egy példa a CCI és a rendezett CCI lekérdezési teljesítményének összehasonlítására.

Sávdiagram a data_loading alatti teljesítményt hasonlítja össze. A rendezett fürtözött oszlopcentrikus indexek időtartama alacsonyabb.

Szegmens átfedésének csökkentése

Az egymást átfedő szegmensek száma a rendezendő adatok méretétől, a rendelkezésre álló memóriától és a maximális párhuzamossági foktól (MAXDOP) függ a rendezett CCI-létrehozás során. A következő stratégiák csökkentik a szegmensek átfedését a rendezett CCI létrehozásakor.

  • Használjon xlargerc erőforrásosztályt egy magasabb szintű DWU-n, hogy több memóriát biztosíthasson az adatrendezéshez, mielőtt az indexszerkesztő szegmensekbe tömörítené az adatokat. Az indexszegmensben az adatok fizikai helye nem módosítható. Nincs adatrendezés egy szegmensen belül vagy szegmensek között.

  • Rendezett CCI létrehozása a paranccsal OPTION (MAXDOP = 1). A rendezett CCI-létrehozáshoz használt szálak az adatok egy részhalmazán működnek, és helyileg rendezik őket. Nincs globális rendezés a különböző szálak szerint rendezett adatok között. A párhuzamos szálak használata csökkentheti a rendezett CCI-k létrehozásának idejét, de több egymást átfedő szegmenst eredményez, mint egyetlen szál használata. Egyetlen szálláncos művelettel a legmagasabb tömörítési minőséget biztosítja. Például:

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

Megjegyzés

Jelenleg az Azure Synapse Analytics dedikált SQL-készleteiben a MAXDOP beállítás csak a parancs használatával CREATE TABLE AS SELECT rendezett CCI-tábla létrehozásához támogatott. A rendezett CCI-k vagy CREATE TABLE parancsok használatával CREATE INDEX történő létrehozása nem támogatja a MAXDOP beállítást. Ez a korlátozás nem vonatkozik SQL Server 2022-SQL Server és újabb verziókra, ahol megadhatja a MAXDOP-t a vagy CREATE TABLE parancsokkalCREATE INDEX.

  • Rendezze előre az adatokat a rendezési kulcs(ok) alapján, mielőtt betöltené őket a táblákba.

Íme egy példa egy rendezett CCI-táblaeloszlásra, amelynek nulla szegmense átfedésben van a fenti javaslatok alapján. A rendezett CCI-tábla egy DWU1000c adatbázisban jön létre CTAS-en keresztül egy 20 GB-os halomtáblából a MAXDOP 1 és xlargerca használatával. A CCI egy BIGINT oszlopra van rendezve, ismétlődések nélkül.

Képernyőkép szöveges adatokról, amelyeken nincs átfedésben a szegmens.

Rendezett CCI létrehozása nagy táblákon

A rendezett CCI létrehozása offline művelet. Partíció nélküli táblák esetén az adatok nem lesznek elérhetők a felhasználók számára, amíg a megrendelt CCI-létrehozási folyamat be nem fejeződik. Particionált táblák esetén, mivel a motor partíciónként hozza létre a rendezett CCI-partíciót, a felhasználók továbbra is hozzáférhetnek az adatokhoz olyan partíciókban, ahol a rendezett CCI-létrehozás nincs folyamatban. Ezzel a beállítással minimalizálhatja az állásidőt a nagy táblákon rendezett CCI-létrehozás során:

  1. Hozzon létre partíciókat a cél nagy táblán (nevű Table_A).
  2. Hozzon létre egy üres rendezett CCI-táblát (nevű Table_B) ugyanazzal a táblával és partíciós sémával, mint a következővel Table_A: .
  3. Váltson át egy partícióról Table_A a -re Table_B.
  4. Futtassa a parancsot ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> a bekapcsolt partíció újraépítéséhez a következőn: Table_B.
  5. Ismételje meg a 3. és a 4. lépést a partíciók esetében.Table_A
  6. Miután az összes partíció át lett váltva a-ról Table_A a-re Table_B , és újraépítették, helyezze a Table_Aelemet, és nevezze át a következőre Table_B : Table_A.

Tipp

Rendezett CCI-vel rendelkező dedikált SQL-készlettáblák esetén az ALTER INDEX REBUILD újrarendezi az adatokat a használatával tempdb. Monitorozás tempdb az újraépítési műveletek során. Ha több tempdb helyre van szüksége, vertikálisan skálázza fel a készletet. Az index újraépítése után vertikálisan leskálázható.

Rendezett CCI-vel rendelkező dedikált SQL-készlettáblák esetén az ALTER INDEX REORGANIZE nem rendezi újra az adatokat. Az adatok használatához használja az ALTER INDEX REBUILD parancsot.

A CCI rendezett karbantartásával kapcsolatos további információkért lásd: Fürtözött oszlopcentrikus indexek optimalizálása.

Funkcióbeli különbségek SQL Server 2022 képességeiben

SQL Server 2022 -ben (16.x) rendezett fürtözött oszlopcentrikus indexek jelentek meg, hasonlóan a dedikált SQL-készletek Azure Synapse funkcióhoz.

  • Jelenleg csak SQL Server 2022-es (16.x) és újabb verziók támogatják a fürtözött oszlopcentrikus bővített szegmenseliminálási képességeket a sztring-, bináris- és guid adattípusokhoz, valamint a datetimeoffset adattípust a kettőnél nagyobb skálázáshoz. Korábban ez a szegmenseliminálás a numerikus, dátum- és időadattípusokra, valamint a datetimeoffset adattípusra vonatkozik, amely skálázása kisebb vagy egyenlő kettőnél.
  • Jelenleg csak a 2022-SQL Server (16.x) és újabb verziók támogatják a fürtözött oszlopcentrikus sorcsoport eltávolítását a predikátumok előtagja LIKE esetében, például column LIKE 'string%'. A szegmensek megszüntetése nem támogatott a LIKE előtag nélküli használatához, például column LIKE '%string': .

További információ: Az oszlopcentrikus indexek újdonságai.

Példák

A. A rendezett oszlopok és a sorrendi sorrend ellenőrzése:

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. Az oszlop sorszámának módosításához vegyen fel vagy távolítson el oszlopokat a rendelési listából, vagy váltson a CCI-ről rendezett CCI-ra:

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

Következő lépések