Táblák particionálása dedikált SQL-készletben

2025-01-23

Javaslatok és példák a táblapartíciók dedikált SQL-készletben való használatára.

Mik azok a táblapartíciók?

A táblapartíciók lehetővé teszik az adatok kisebb adatcsoportokra való felosztását. A legtöbb esetben a táblapartíciók egy dátumoszlopon jönnek létre. A particionálás minden dedikált SQL-készlettábla-típus esetében támogatott; beleértve a fürtözött oszloptárat, a fürtözött indexet és a halomot. A particionálás minden elosztási típus esetében támogatott, beleértve a kivonatolt vagy ciklikus elosztásút is.

A particionálás előnyös lehet az adatkarbantartás és a lekérdezési teljesítmény szempontjából. Az, hogy mindkettő előnyös-e, vagy csak egy, az adatok betöltésének módjától és attól függ, hogy ugyanaz az oszlop mindkét célra használható-e, mivel a particionálás csak egy oszlopon végezhető el.

A rakományok előnyei

A dedikált SQL-készlet particionálásának elsődleges előnye az adatok betöltése hatékonyságának és teljesítményének javítása partíciótörlés, váltás és egyesítés használatával. A legtöbb esetben az adatok particionálása olyan dátumoszlopon történik, amely szorosan kapcsolódik ahhoz a sorrendhez, amelyben az adatok betöltődnek az SQL-készletbe. A partíciók adatok karbantartására való használatának egyik legnagyobb előnye a tranzakciónaplózás elkerülése. Az adatok egyszerű beszúrása, frissítése vagy törlése a legegyértelmesebb módszer lehet, egy kis átgondolással és erőfeszítéssel a particionálás használata a betöltési folyamat során jelentősen javíthatja a teljesítményt.

A partícióváltással gyorsan eltávolíthatja vagy lecserélheti a tábla egy szakaszát. Előfordulhat például, hogy egy értékesítési ténytábla csak az elmúlt 36 hónap adatait tartalmazza. Minden hónap végén az értékesítési adatok legrégebbi hónapja törlődik a táblából. Ezek az adatok törölhetők egy törlési utasítással a legrégebbi hónap adatainak törléséhez.

Ha azonban nagy mennyiségű adatsort töröl sorról sorra egy törlési utasítással, az túl sok időt vehet igénybe, és olyan nagy tranzakciók kockázatát okozhatja, amelyek hosszú időt vesznek igénybe a visszaállításhoz, ha valami hiba történik. Az optimálisabb módszer az adatok legrégebbi partíciójának elvetése. Ahol az egyes sorok törlése órákat is igénybe vehet, a teljes partíció törlése másodperceket vehet igénybe.

A lekérdezések előnyei

A particionálás a lekérdezési teljesítmény javítására is használható. Egy olyan lekérdezés, amely szűrőt alkalmaz a particionált adatokra, csak a megfelelő partíciókra korlátozhatja a vizsgálatot. Ez a szűrési módszer elkerülheti a teljes táblavizsgálatot, és csak egy kisebb adathalmazt vizsgálhat. A fürtözött oszlopos indexek bevezetésével a predikátum kizárási teljesítmény előnyei kevésbé jelentősek, de bizonyos esetekben a lekérdezések szempontjából mégis hasznos lehet.

Ha például az értékesítési ténytábla 36 hónapra van particionálva az értékesítési dátum mező használatával, akkor az értékesítési dátumra szűrő lekérdezések kihagyhatják a szűrésnek nem megfelelő partíciókban való keresést.

Partícióméretezés

Bár a particionálással bizonyos forgatókönyvek javíthatók a teljesítményen, a túl sok partícióval rendelkező tábla létrehozása bizonyos körülmények között ronthatja a teljesítményt. Ezek az aggodalmak különösen érvényesek a fürtözött oszlop-táblázatokra.

Ahhoz, hogy a particionálás hasznos legyen, fontos tisztában lenni azzal, hogy mikor érdemes particionálást használni, és hogy hány partíciót kell létrehozni. Nincs nehéz, gyors szabály arról, hogy hány partíció túl sok, az adatoktól és az egyidejűleg betöltött partíciók számától függ. A sikeres particionálási sémák általában több tucat vagy száz partícióval rendelkeznek, nem több ezerrel.

A fürtözött oszlopcentrikus táblák partícióinak létrehozásakor fontos figyelembe venni, hogy hány sor tartozik az egyes partíciókhoz. A fürtözött oszloptárolású táblák optimális tömörítéséhez és teljesítményéhez eloszlásonként és partíciónként legalább 1 millió sorra van szükség. A partíciók létrehozása előtt a dedikált SQL-készlet már 60 eloszlásra osztja az egyes táblákat.

A táblákhoz hozzáadott particionálások kiegészítik a színfalak mögött létrehozott disztribúciókat. Ebben a példában, ha az értékesítési ténytábla 36 havi partíciót tartalmazott, és mivel egy dedikált SQL-készlet 60 eloszlással rendelkezik, akkor az értékesítési ténytáblának havonta 60 millió sort vagy 2,1 milliárd sort kell tartalmaznia, amikor az összes hónap ki van töltve. Ha egy tábla kevesebbet tartalmaz, mint az ajánlott minimális számú sor partíciónként, fontolja meg, hogy kevesebb partíciót használjon a partíciónkénti sorok számának növelése érdekében.

További információért tekintse meg az Indexelés című cikket, amely olyan lekérdezéseket tartalmaz, amelyek fel tudják mérni a fürt kolumnáris adattárház indexek minőségét.

Az SQL Server szintaktikai eltérései

A dedikált SQL-készlet az SQL Servernél egyszerűbb partíciók definiálásának módját mutatja be. A particionálási függvények és sémák nem használhatók dedikált SQL-készletben, mint az SQL Serverben. Ehelyett mindössze azonosítania kell a particionált oszlopot és a határpontokat.

Bár a particionálás szintaxisa kissé eltérhet az SQL Servertől, az alapfogalmak ugyanazok. Az SQL Server és a dedikált SQL-készlet táblánként egy partícióoszlopot támogat, amely tartományos partíció lehet. A particionálásról további információt a particionált táblák és indexek című témakörben talál.

Az alábbi példa a CREATE TABLE utasítást használja a FactInternetSales tábla particionálására az OrderDateKey oszlopban:

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

Partíciók migrálása az SQL Serverről

Sql Server-partíciódefiníciók áttelepítése dedikált SQL-készletbe egyszerűen:

Az SQL Server partíciós sémájának megszüntetése.
Adja hozzá a partíciófüggvény definícióját a CREATE TABLE táblához.

Ha particionált táblát migrál egy SQL Server-példányból, az alábbi SQL segíthet az egyes partíciók sorainak számának megállapításában. Ne feledje, hogy ha ugyanazt a particionálási részletességet használja a dedikált SQL-készletben, a partíciónkénti sorok száma 60-zal csökken.

-- Partition information for a SQL Server Database
SELECT      s.[name]                        AS      [schema_name]
,           t.[name]                        AS      [table_name]
,           i.[name]                        AS      [index_name]
,           p.[partition_number]            AS      [partition_number]
,           SUM(a.[used_pages]*8.0)         AS      [partition_size_kb]
,           SUM(a.[used_pages]*8.0)/1024    AS      [partition_size_mb]
,           SUM(a.[used_pages]*8.0)/1048576 AS      [partition_size_gb]
,           p.[rows]                        AS      [partition_row_count]
,           rv.[value]                      AS      [partition_boundary_value]
,           p.[data_compression_desc]       AS      [partition_compression_desc]
FROM        sys.schemas s
JOIN        sys.tables t                    ON      t.[schema_id]         = s.[schema_id]
JOIN        sys.partitions p                ON      p.[object_id]         = t.[object_id]
JOIN        sys.allocation_units a          ON      a.[container_id]      = p.[partition_id]
JOIN        sys.indexes i                   ON      i.[object_id]         = p.[object_id]
                                            AND     i.[index_id]          = p.[index_id]
JOIN        sys.data_spaces ds              ON      ds.[data_space_id]    = i.[data_space_id]
LEFT JOIN   sys.partition_schemes ps        ON      ps.[data_space_id]    = ds.[data_space_id]
LEFT JOIN   sys.partition_functions pf      ON      pf.[function_id]      = ps.[function_id]
LEFT JOIN   sys.partition_range_values rv   ON      rv.[function_id]      = pf.[function_id]
                                            AND     rv.[boundary_id]      = p.[partition_number]
WHERE       p.[index_id] <=1
GROUP BY    s.[name]
,           t.[name]
,           i.[name]
,           p.[partition_number]
,           p.[rows]
,           rv.[value]
,           p.[data_compression_desc];

Partícióváltás

A dedikált SQL-készlet támogatja a partíciófelosztást, az egyesítést és a váltást. Ezen függvények mindegyike az ALTER TABLE utasítással lesz végrehajtva.

Ha két tábla között szeretne partíciókat váltani, győződjön meg arról, hogy a partíciók a saját határaikhoz igazodnak, és hogy a tábladefiníciók egyeznek. Mivel a tábla értéktartományának kényszerítéséhez nem érhetők el ellenőrzési korlátozások, a forrástáblának ugyanazokat a partícióhatárokat kell tartalmaznia, mint a céltáblának. Ha a partíció határai nem azonosak, akkor a partíciókapcsoló meghiúsul, mivel a partíció metaadatai nem lesznek szinkronizálva.

A partíció felosztásához a megfelelő partíciónak (nem feltétlenül az egész táblának) üresnek kell lennie, ha a tábla fürtözött oszlopcentrikus indexet (CCI) tartalmaz. Az ugyanabban a táblában lévő többi partíció tartalmazhat adatokat. Az adatokat tartalmazó partíciók nem oszthatók fel, ezért hiba lép fel: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. Az adatokat tartalmazó partíció felosztásának megoldásaként tekintse meg a következő útmutatót: Hogyan osszunk fel egy adatokat tartalmazó partíciót.

Adatokat tartalmazó partíció felosztása

A már adatokat tartalmazó partíció felosztásának leghatékonyabb módja egy CTAS utasítás használata. Ha a particionált tábla fürtözött oszloptárolás, akkor a táblapartíciónak üresnek kell lennie, mielőtt feloszthatjuk.

Az alábbi példa egy particionált oszlopcentrikus táblát hoz létre. Minden partícióba beszúr egy sort:

CREATE TABLE [dbo].[FactInternetSales]
(
        [ProductKey]            int          NOT NULL
    ,   [OrderDateKey]          int          NOT NULL
    ,   [CustomerKey]           int          NOT NULL
    ,   [PromotionKey]          int          NOT NULL
    ,   [SalesOrderNumber]      nvarchar(20) NOT NULL
    ,   [OrderQuantity]         smallint     NOT NULL
    ,   [UnitPrice]             money        NOT NULL
    ,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                    (20000101
                    )
                )
);

INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);

INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);

A következő lekérdezés a katalógusnézettel keresi meg a sys.partitions sorok számát:

SELECT  QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
,       i.[name] as Index_name
,       p.partition_number as Partition_nmbr
,       p.[rows] as Row_count
,       p.[data_compression_desc] as Data_Compression_desc
FROM    sys.partitions p
JOIN    sys.tables     t    ON    p.[object_id]   = t.[object_id]
JOIN    sys.schemas    s    ON    t.[schema_id]   = s.[schema_id]
JOIN    sys.indexes    i    ON    p.[object_id]   = i.[object_Id]
                            AND   p.[index_Id]    = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';

A következő felosztási parancs hibaüzenetet kap:

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.

CTAS Az adatok tárolásához azonban létrehozhat egy új táblát.

CREATE TABLE dbo.FactInternetSales_20000101
    WITH    (   DISTRIBUTION = HASH(ProductKey)
            ,   CLUSTERED COLUMNSTORE INDEX              
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101
                                )
                            )
)
AS
SELECT *
FROM    FactInternetSales
WHERE   1=2;

Ha a partícióhatárok igazítva vannak, a kapcsolás engedélyezett. Ez egy üres partíciót hagy a forrástáblában, amelyet később feloszthat.

ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;

ALTER TABLE FactInternetSales SPLIT RANGE (20010101);

Már csak annyi maradt hátra, hogy az adatokat igazítsuk az új partícióhatárokhoz a CTAS segítségével, majd az adatokat visszahelyezzük a főtáblába.

CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales_20000101]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101;

ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;

Miután elvégezte az adatok áthelyezését, érdemes frissíteni a céltáblán lévő statisztikákat. A statisztikák frissítése biztosítja, hogy a statisztikák pontosan tükrözzék az adatok új eloszlását a saját partícióikon.

UPDATE STATISTICS [dbo].[FactInternetSales];

Végül az adatok áthelyezésére szolgáló egyszeri partíciókapcsoló esetén elvetheti a partíciókapcsolóhoz létrehozott táblákat, FactInternetSales_20000101_20010101 és FactInternetSales_20000101a . Másik lehetőségként érdemes lehet üres táblákat tartani a normál, automatizált partíciókapcsolókhoz.

Új adatok betöltése egy lépésben adatokat tartalmazó partíciókba

Az adatok partíciókba való betöltése partícióváltással kényelmes módja annak, hogy a felhasználók számára nem látható táblákban új adatokat alakítsanak ki. Az elfoglalt rendszerek esetében kihívást jelenthet a partícióváltáshoz kapcsolódó zárolási versengés kezelése.

A meglévő adatok törléséhez egy ALTER TABLE partícióban korábban szükség volt az adatok cseréjére. Ezután egy másikra ALTER TABLE volt szükség az új adatok közötti váltáshoz.

A dedikált SQL-készletben ez a TRUNCATE_TARGET beállítás támogatott a ALTER TABLE parancsban. A TRUNCATE_TARGET parancs felülírja a ALTER TABLE partíció meglévő adatait új adatokkal. Az alábbi példa CTAS segítségével létrehoz egy új táblát a meglévő adatokkal, új adatokat szúr be, majd az összes adatot visszahelyezi a cél táblába, felülírva a meglévő adatokat.

CREATE TABLE [dbo].[FactInternetSales_NewSales]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);

ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);

Táblaparticionálási forrásvezérlő

Feljegyzés

Ha a forrásvezérlő eszköz nincs úgy konfigurálva, hogy figyelmen kívül hagyja a partíciós sémákat, a tábla sémájának módosítása partíciók frissítésére okozhatja, hogy a rendszer elveti és újra létrehozza a táblát az üzembe helyezés részeként, ami nem feltétlenül valósítható meg. Szükség lehet egy egyéni megoldásra egy ilyen módosítás implementálásához az alábbiakban leírtak szerint. Ellenőrizze, hogy a folyamatos integrációs/folyamatos üzembe helyezési (CI/CD) eszköz lehetővé teszi-e ezt. Az SQL Server Data Tools (SSDT) alkalmazásban keresse meg a Speciális közzétételi beállítások "Partíciós sémák figyelmen kívül hagyása" lehetőséget, hogy elkerülje az olyan generált szkripteket, amelyek egy táblát elvetnek és újra létrehoznak.

Ez a példa egy üres tábla partíciós sémáinak frissítésekor hasznos. A partícióváltozások adatokkal rendelkező táblán történő folyamatos telepítéséhez kövesse a Hogyan osszunk fel egy adatokat tartalmazó partíciót című részben leírt lépéseket, és a telepítés során ideiglenesen helyezze át az adatokat az egyes partíciókból, mielőtt alkalmazná a partíció FELOSZTÁSI TARTOMÁNYÁT. Erre azért van szükség, mert a CI/CD eszköz nem tudja, hogy mely partíciók rendelkeznek adatokkal.

Annak érdekében, hogy a tábladefiníció ne rozsdásodjon a forrásvezérlő rendszerben, érdemes megfontolnia a következő megközelítést:

A tábla létrehozása particionált táblaként, de partícióértékek nélkül

CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,   DISTRIBUTION = HASH([ProductKey])
,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES () )
);

SPLIT a táblázat az üzembe helyezési folyamat részeként szerepel:

 -- Create a table containing the partition boundaries

CREATE TABLE #partitions
WITH
(
    LOCATION = USER_DB
,   DISTRIBUTION = HASH(ptn_no)
)
AS
SELECT  ptn_no
,       ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no
FROM    (
    SELECT CAST(20000101 AS INT) ptn_no
    UNION ALL
    SELECT CAST(20010101 AS INT)
    UNION ALL
    SELECT CAST(20020101 AS INT)
    UNION ALL
    SELECT CAST(20030101 AS INT)
    UNION ALL
    SELECT CAST(20040101 AS INT)
) a;

 -- Iterate over the partition boundaries and split the table

DECLARE @c INT = (SELECT COUNT(*) FROM #partitions)
,       @i INT = 1                                 --iterator for while loop
,       @q NVARCHAR(4000)                          --query
,       @p NVARCHAR(20)     = N''                  --partition_number
,       @s NVARCHAR(128)    = N'dbo'               --schema
,       @t NVARCHAR(128)    = N'FactInternetSales' --table;

WHILE @i <= @c
BEGIN
    SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i);
    SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');');

    -- PRINT @q;
    EXECUTE sp_executesql @q;
    SET @i+=1;
END

 -- Code clean-up

DROP TABLE #partitions;

Ezzel a megközelítéssel a forrásvezérlő kódja statikus marad, és a particionálási határértékek dinamikusak lehetnek; folyamatosan fejlődik az SQL-készlettel.

A táblák fejlesztésével kapcsolatos további információkért tekintse meg a Táblázat áttekintése című témakört.

Megosztás a következőn keresztül: