Teljesítményhangolás rendezett oszlopcentrikus indexekkel

A következőkre vonatkozik: Az SQL Server 2022 (16.x) és újabb verziói az Azure SQL DatabaseAzure SQL Managed InstanceSQL-adatbázist a Microsoft Fabricben

A hatékony szegmenseltörlés engedélyezésével a rendezett oszlopcentrikus indexek gyorsabb teljesítményt nyújtanak a lekérdezési predikátumnak nem megfelelő nagy mennyiségű rendezett adat kihagyásával. Az adatok rendezett oszlopcentrikus indexbe való betöltése és az indexek újraépítésével történő sorrendbe helyezése hosszabb időt vehet igénybe, mint a nem rendezett indexekben az adatrendező művelet miatt, de a rendezett oszlopcentrikus indexek lekérdezései később gyorsabban futhatnak.

Amikor a felhasználók 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 olvashatók lemezről memóriába. A lekérdezések gyorsabban befejeződhetnek, ha az olvasandó szegmensek száma és teljes méretük kisebb.

A rendezett oszloparchív index elérhetőségéről a Rendezett oszloparchív index elérhetőségecímű részben tájékozódhat.

Az oszlopcentrikus indexek nemrég hozzáadott funkcióiról további információt Az oszlopcentrikus indexek újdonságaicímű témakörben talál.

Rendezett és nem rendezett oszlopalapú index

Egy oszlopcentrikus indexben az egyes sorcsoportok minden oszlopában lévő adatok külön szegmensbe lesznek tömörítve. Minden szegmens a minimális és maximális értékeket leíró metaadatokat tartalmaz, í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.

Ha egy oszlopcentrikus index nincs rendezve, az indexszerkesztő nem rendezi az adatokat, mielőtt szegmensekbe tömörítené őket. Ez azt jelenti, hogy egymást átfedő értéktartományokkal rendelkező szegmensek is előfordulhatnak, így a lekérdezések több szegmenst olvasnak be a lemezről, és hosszabb időt vesz igénybe.

Rendezett oszlopcentrikus index létrehozásakor az adatbázismotor a megadott sorrendkulcsok alapján rendezi a meglévő adatokat, mielőtt az indexszerkesztő szegmensekbe tömörítené őket. A rendezett adatok esetén a szegmensek átfedése csökken vagy megszűnik, így a lekérdezések hatékonyabb szegmenseliszorítást és ezáltal gyorsabb teljesítményt eredményeznek, mivel kevesebb szegmenst lehet olvasni a lemezről.

A rendelkezésre álló memóriától, az adatmérettől, a párhuzamosság mértékétől, az index típusától (fürtözött vs. nem fürtözött), és az indexépítés típusától (offline vs. online) függően a rendezett oszlopcentrikus indexek rendezése teljes lehet (nincs szegmens átfedés) vagy részleges (nagyobb mértékű szegmens átfedés). Részleges rendezés például akkor fordul elő, ha a rendelkezésre álló memória nem elegendő a teljes rendezéshez. A rendezett oszlopcentrikus indexet használó lekérdezések gyakran gyorsabban futnak, mint a nem rendezett indexekkel, még akkor is, ha a rendezett index részleges rendezés használatával lett létrehozva.

Teljes körű rendezést biztosítanak a rendezett fürtözött oszlopcentrikus indexekhez, amelyeket ONLINE = ON és MAXDOP = 1 opciókkal hoznak létre vagy építenek újjá. Ebben az esetben a rendezést nem korlátozza a rendelkezésre álló memória, mert a tempdb adatbázis használatával az adatokat átmásolja, amelyek nem férnek be a memóriába. Ez a további tempdb I/O miatt az index buildelési folyamatát lelassíthatja. Az online index újraépítésével azonban a lekérdezések továbbra is használhatják a meglévő indexet, miközben az új rendezett indexet újraépítik.

A rendezett fürtözött és nem fürtözött oszlopcentrikus indexekhez is teljes rendezést lehet biztosítani, amelyeket ONLINE = OFF és MAXDOP = 1 beállításokkal hoztak létre vagy újjáépítettek, ha a rendezendő adatok mennyisége elég kicsi ahhoz, hogy teljesen elférjen a rendelkezésre álló memóriában.

Minden más esetben az oszlopalapú, rendezett indexek rendezése részleges.

Note

A rendezett oszlopcentrikus indexek jelenleg csak az Azure SQL Database-ben, az Azure SQL Managed Instance^AUTD-ben és az SQL Server 2025-ben (17.x) hozhatók létre vagy hozhatók létre újra online.

Ha ellenőrizni szeretné egy oszlop szegmenstartományait, és meg szeretné állapítani, hogy van-e szegmensfedés, használja az alábbi lekérdezést, és helyettesítse a helyőrzőket a sémával, a táblázattal és az oszlopnevekkel:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

A lekérdezés kimenete például egy teljesen rendezett oszlopcentrikus index esetében a következőképpen nézhet ki. Vegye figyelembe, hogy a különböző szegmensek min_data_id és max_data_id oszlopaiban nincs átfedés.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Note

Egy rendezett oszlopcentrikus indexben az azonos DML- vagy adatbetöltési műveletekből származó új adatok csak ezen a kötegen belül lesznek rendezve. Nincs olyan globális rendezés, amely meglévő adatokat tartalmaz a táblában.

Ha új adatok beszúrása vagy meglévő adatok frissítése után szeretne adatokat rendezni az indexben, építse újra az indexet.

Particionált oszlopcentrikus index offline újraépítéséhez az újraépítés egyszerre egy partícióval történik. Az újraépített partícióban lévő adatok nem érhetők el, amíg az újraépítés be nem fejeződik az adott partícióhoz.

Az adatok az online újraépítés során is elérhetők maradnak. További információ: Indexműveletek online végrehajtása.

Lekérdezési teljesítmény

A rendezett oszlopcentrikus index teljesítménynövekedése függ 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 és a lekérdezés végrehajtásához rendelkezésre álló számítási erőforrásoktól.

A következő mintákkal rendelkező lekérdezések általában gyorsabban futnak rendezett oszlopcentrikus indexekkel.

• Egyenlőségi, egyenlőtlenségi vagy tartomány-predikátumokkal rendelkező lekérdezések.
• Olyan lekérdezések, amelyekben a predikátumoszlopok és a rendezett CCI-oszlopok megegyeznek.

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

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

Az 1. és a 2. lekérdezés teljesítménye a 3. és a 4. lekérdezésnél jobban kihasználhatja a rendezett oszlopcentrikus indexet, mivel az összes rendezett oszlopra hivatkozik.

-- 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 oszlopcentrikus indexet tartalmazó táblákba való adatbetöltés teljesítménye hasonló a particionált táblához. Az adatok betöltése hosszabb időt is igénybe vehet, mint egy nem rendezett oszlopcentrikus index esetén az adatrendező művelet miatt, de a lekérdezések később gyorsabban futhatnak.

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

Az átfedésben lévő szegmensek száma a rendezendő adatok méretétől, a rendelkezésre álló memóriától és a párhuzamosság (MAXDOP) maximális mértékétől függ a rendezett oszlopcentrikus index összeállítása során. Az alábbi stratégiák csökkentik a szegmensek átfedését, de az indexkészítési folyamat hosszabb időt vehet igénybe.

• Ha elérhető az online index összeállítása, a rendezett fürtözött oszlopalapú index létrehozásakor a ONLINE = ON és MAXDOP = 1 beállításokat együtt használja. Ez egy teljesen rendezett indexet hoz létre.
• Ha az online index összeállítása nem érhető el, használja a MAXDOP = 1 lehetőséget.
• Előre rendezze az adatokat a betöltés előtti rendezési kulcsokkal.

Ha MAXDOP 1-nél nagyobb, a rendezett oszlopcentrikus indexek összeállításához használt szálak az adatok egy részhalmazán működnek, és helyileg rendezik őket. Nincs globális rendezés különböző szálak szerint rendezett adatok között. A párhuzamos szálak használata csökkentheti az index létrehozásának idejét, de több egymást átfedő szegmenst hoz létre, mint egyetlen szál használatakor. Egyetlen szálas művelettel a legmagasabb tömörítési minőséget biztosítja. A MAXDOP paramétert a CREATE INDEX paranccsal adhatja meg.

Examples

Rendezett oszlopok és sorszámok ellenőrzése

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

Rendezett oszlop-adattár index létrehozása

Csoportosított rendezett oszlop-tároló index

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Nem klaszterezett rendezett oszlophalmaz index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Rendelésoszlopok hozzáadása vagy eltávolítása, valamint meglévő rendezett oszlopcentrikus index újraépítése

Csoportosított rendezett oszlop-tároló index

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Nem klaszterezett rendezett oszlophalmaz index:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Rendezett fürtözött oszlophalmaz index online létrehozása teljes oszloprendezéssel egy bázistáblában.

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Rendezett fürtözött oszlopcentrikus index online újraépítése teljes rendezéssel

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Kapcsolódó tartalom

• oszloptárolós index tervezési irányelvei
• oszlopcentrikus indexek – adatbetöltési útmutató
• Ismerkedjen meg az oszlopalapú indexekkel a valós idejű üzemeltetési analitikához
• Oszlopbolt indexek adattárházakban
• Indexkarbantartás optimalizálása a lekérdezési teljesítmény javítása és az erőforrás-felhasználás csökkentése
• oszlopalapú indexarchitektúra
• INDEX LÉTREHOZÁSA (Transact-SQL)
• ALTER INDEX (Transact-SQL)