Oszlopbolti indexek – adatbetöltési útmutató

Vonatkozik a következőkre:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalitikai Platform System (PDW)SQL adatbázis a Microsoft Fabric-ben

Az adatok oszlopcentrikus indexbe való betöltésének lehetőségei és javaslatai a standard SQL tömeges betöltési és beszúrási módszerekkel. Az adatok oszlopcentrikus indexbe való betöltése minden adattárházi folyamat alapvető része, mivel az adatok az elemzés előkészítése során az indexbe kerülnek.

Újdonság az oszlopos tárolású indexek terén? Lásd : Oszlopcentrikus indexek – áttekintés és oszlopcentrikus indexarchitektúra.

Mi az a tömeges betöltés?

A tömeges betöltés azt jelenti, hogy a rendszer nagy számú sort ad hozzá egy adattárhoz. Ez az adatok oszlopbolt indexbe való áthelyezésének leghatékonyabb módja, mivel sorok kötegein működik. A tömeges betöltés kitölti a sorcsoportokat a maximális kapacitás érdekében, és közvetlenül az oszloptárba tömöríti őket. A deltastore-ba csak azok a sorok kerülnek, amelyek egy terhelés végén nem felelnek meg a sorcsoportonként legalább 102 400 sornak.

Tömeges terhelés végrehajtásához használhatja a bcp Segédprogramot, az Integration Servicest, vagy kiválaszthat sorokat egy előkészítési táblából.

Ahogy a diagram is sugallja, tömeges terhelés:

• Nem rendezi előre az adatokat. Az adatok a kapott sorrendben kerülnek be a sorcsoportokba.
• Ha a köteg mérete >= 102400, a sorok közvetlenül a tömörített sorcsoportokba lesznek betöltve. Hatékony tömeges importáláshoz válasszon egy >=102400 kötegméretet, mert elkerülheti, hogy az adatsorok delta sorcsoportokba kerüljenek, mielőtt egy háttérszál végül tömörített sorcsoportokba helyezi őket, a tuple mover (TM) által.
• Ha a köteg mérete < 102 400, vagy ha a többi sor < 102 400, akkor a sorok delta sorcsoportokba lesznek betöltve.

Note

A nem oszlopcentrikus indexadatokkal rendelkező sortártáblákon az SQL Server mindig beszúrja az adatokat az alaptáblába. Az adatokat soha nem szúrják be közvetlenül az oszlop-tároló indexbe.

A tömeges betöltés a következő beépített teljesítményoptimalizálásokkal rendelkezik:

• Párhuzamos terhelések: Több egyidejű tömeges betöltéssel (bcp vagy tömeges beszúrás) is rendelkezhet, amelyek mindegyike külön adatfájlt tölt be. Ellentétben a rowstore tömeges betöltéssel az SQL Serverbe, nem kell megadnia TABLOCK , mert minden tömeges importálási szál kizárólag külön sorcsoportokba (tömörített vagy delta sorcsoportokba) tölti be az adatokat kizárólagos zárolással.

• Csökkentett naplózás: A tömörített sorcsoportokba közvetlenül betöltött adatok jelentősen csökkentik a napló méretét. Ha például az adatok 10-szeres tömörítése történik, a megfelelő tranzakciónapló nagyjából 10-szer kisebbé válik anélkül, hogy szükség lenne TABLOCK tömegesen naplózott vagy egyszerű helyreállítási modellre. A delta sorcsoportba tartozó összes adat teljes mértékben naplózva van. Ide tartoznak a 102 400 sornál kisebb kötegméretek. Ajánlott eljárás a batchsize >= 102400 használata. Mivel nincs TABLOCK szükség rá, az adatokat párhuzamosan is betöltheti.

• Minimális naplózás: Ha betartja a minimális naplózás előfeltételeit, további naplózási csökkentést érhet el. Az adatok rowstore-ba való betöltésétől eltérően azonban a TABLOCK a táblán X (kizárólagos) zárolást eredményez ahelyett, hogy BU (tömeges frissítési) zárolást alkalmazna, ezért a párhuzamos adatbetöltés nem végezhető el. A zárolásról további információt a Zárolás és a sorverziók című témakörben talál.

• Zárolás optimalizálása: A X rendszer automatikusan beolvasja a sorcsoport zárolását, amikor adatokat tölt be egy tömörített sorcsoportba. Ha azonban tömegesen tölt be egy delta sorcsoportba, a rendszer zárolást alkalmaz a sorcsoportra, X de az adatbázismotor továbbra is zárolja az oldalszinteket és a kiterjedéseket, mert a X sorcsoport zárolása nem része a zárolási hierarchiának.

Ha egy oszlop-tároló indexen nem fürtözött B-fa index található, akkor az index önmagában nem használ zárolási vagy naplózási optimalizálást, de a korábban ismertetett fürtözött oszlop-tároló index optimalizációk alkalmazhatók.

Tömeges terhelés méretének tervezése a deltasorcsoportok minimalizálása érdekében

Az oszlopcentrikus indexek akkor teljesítenek a legjobban, ha a sorok többsége az oszloptárba van tömörítve, és nem a delta sorcsoportokban található. A legjobb, ha úgy méretezi az adatcsomagokat, hogy a sorok közvetlenül az oszloptárba kerüljenek, és amennyire csak lehet, elkerüljék a "deltastore"-t.

Ezek a forgatókönyvek azt írják le, hogy a betöltött sorok mikor kerülnek közvetlenül az oszloptárba, vagy mikor lépnek a deltastore-ba. A példában minden sorcsoport sorcsoportonként 102 400–1 048 576 sort tartalmazhat. A gyakorlatban a sorcsoportok maximális mérete 1 048 576 sornál kisebb lehet memóriaterhelés esetén.

Sorok tömeges betöltéshez	A tömörített sorcsoporthoz hozzáadott sorok	A delta sorcsoporthoz hozzáadott sorok
102,000	0	102,000
145,000	145,000 Sorcsoport mérete: 145 000	0
1,048,577	1,048,576 Sorcsoport mérete: 1 048 576.	1
2,252,152	2,252,152 Sorcsoport mérete: 1 048 576, 1 048 576, 155 000.	0

Az alábbi példa 1 048 577 sor táblázatba való betöltésének eredményeit mutatja be. Az eredmények azt mutatják, hogy egy TÖMÖRÍTETT sorcsoport található az oszloptárban (mint tömörített oszlopszegmensek), és 1 sor a delta tárban.

SELECT object_id, index_id, partition_number, row_group_id, delta_store_hobt_id,
    state, state_desc, total_rows, deleted_rows, size_in_bytes
FROM sys.dm_db_column_store_row_group_physical_stats;

Előkészítési táblázat használata a teljesítmény javítása érdekében

Ha csak a további átalakítások előtti előkészítés céljából tölti be az adatokat, a hozzátáblázatba való betöltés sokkal gyorsabb, mint a fürtözött oszlopos táblába való betöltés. Emellett az adatok [ideiglenes táblákba][ideiglenes] való betöltése is sokkal gyorsabban fog betöltődni, mint egy tábla végleges tárolóba való betöltése.

Az adatbetöltés gyakori mintája az adatok átmeneti táblába való betöltése, némi átalakítás, majd a céltáblába való betöltése az alábbi paranccsal:

INSERT INTO [<columnstore index>]
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Ez a parancs az adatokat a bcp-hez vagy tömeges beszúráshoz hasonlóan, de egyetlen kötegben tölti be az oszloptár-indexbe. Ha az 102400-es előkészítési táblában < lévő sorok száma, a sorok egy delta sorcsoportba vannak betöltve, ellenkező esetben a sorok közvetlenül a tömörített sorcsoportba lesznek betöltve. Az egyik fő korlátozás az volt, hogy ez a INSERT művelet egyetlen szálas volt. Az adatok párhuzamos betöltéséhez létrehozhat több átmeneti táblát, vagy problémákat INSERT/SELECT okozhat az átmeneti tábla sorainak nem egymást átfedő tartományaival kapcsolatban. Ez a korlátozás megszűnik az SQL Server 2016 -tal (13.x). Az alábbi parancs párhuzamosan tölti be az előkészítési táblából származó adatokat, de meg kell adnia TABLOCK. Ez ellentmondhat a korábban a tömeges adatbetöltéssel kapcsolatban elhangzottaknak, de a fő különbség az, hogy az átmeneti táblából származó párhuzamos adatbetöltés ugyanazon a tranzakción belül lesz végrehajtva.

INSERT INTO [<columnstore index>] WITH (TABLOCK)
SELECT col1 /* include actual list of columns in place of col1*/
FROM [<Staging Table>]

Az előkészítési táblából fürtözött oszlopcentrikus indexbe való betöltésekor az alábbi optimalizálások érhetők el:

• Naplóoptimalizálás: Csökkentett naplózás az adatok tömörített sorcsoportba való betöltésekor.
• Zárolás optimalizálása: Ha adatokat tölt be egy tömörített sorcsoportba, a X sorcsoport zárolása be lesz szerezve. Ha azonban tömegesen tölt be egy delta sorcsoportba, a rendszer zárolást alkalmaz a sorcsoportra, X de az adatbázismotor továbbra is zárolja az oldalszinteket és a kiterjedéseket, mert a X sorcsoport zárolása nem része a zárolási hierarchiának.

Ha rendelkezik egy vagy több nem klaszterezett indexszel, az indexükhöz nincs zárolási vagy naplózási optimalizálás, de a fürtözött oszlopcentrikus index korábban ismertetett optimalizálásai továbbra is elérhetők.

Mi az a csöpögő beszúrás?

A trükkös beszúrás az egyes sorok oszlopcentrikus indexbe való áthelyezésének módját jelenti. A trükkös beszúrások az INSERT INTO utasítást használják. A trükkös beszúrással az összes sor a deltastore-ba kerül. Ez kis számú sor esetén hasznos, de nagy terhelés esetén nem praktikus.

INSERT INTO [<table-name>] VALUES ('some value' /*replace with actual set of values*/)

Note

Egyidejű szálak az INSERT INTO parancs segítségével értékeket szúrnak be egy fürtölt oszlopindexbe, ezáltal sorokat beszúrhatnak ugyanabba a deltastore sorcsoportba.

Ha a sorcsoport 1 048 576 sort tartalmaz, a kijelölt delta sorcsoport bezárva van, de továbbra is elérhető lekérdezésekhez és frissítési/törlési műveletekhez, de az újonnan beszúrt sorok egy meglévő vagy újonnan létrehozott deltastore sorcsoportba kerülnek. Van egy adatmozgató (TM) nevű háttérszál, amely nagyjából 5 percenként tömöríti a zárt delta sorcsoportokat. A bezárt delta sorcsoport tömörítéséhez explicit módon meghívhatja a következő parancsot.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE

Ha bezárt és tömörített delta sorcsoportot szeretne kényszeríteni, hajtsa végre a következő parancsot. Ha végzett a sorok betöltésével, és nem vár új sorokat, futtassa ezt a parancsot. A delta sorcsoport explicit bezárásával és tömörítésével tovább mentheti a tárolót, és javíthatja az elemzési lekérdezés teljesítményét. Ajánlott eljárás a parancs meghívása, ha nem számít új sorok beszúrására.

ALTER INDEX [<index-name>] on [<table-name>] REORGANIZE with (COMPRESS_ALL_ROW_GROUPS = ON)

A particionált táblákba való betöltés működése

Particionált adatok esetén az adatbázismotor először hozzárendeli az egyes sorokat egy partícióhoz, majd oszlopcentrikus műveleteket hajt végre a partíción belüli adatokon. Minden partíció saját sorcsoportokkal és legalább egy delta sorcsoporttal rendelkezik.

Kapcsolódó tartalom

• Adatbetöltési teljesítményre vonatkozó szempontok fürtözött oszlopcentrikus indexekkel