Poznámka
Přístup k této stránce vyžaduje autorizaci. Můžete se zkusit přihlásit nebo změnit adresáře.
Přístup k této stránce vyžaduje autorizaci. Můžete zkusit změnit adresáře.
Doporučení a příklady použití particí tabulky ve vyhrazeném SQL poolu.
Co jsou partice tabulky?
Oddíly tabulky umožňují rozdělit data do menších skupin dat. Ve většině případů se oddíly tabulky vytvářejí ve sloupci kalendářního data. Particionování se podporuje u všech dedikovaných typů tabulek fondu SQL; včetně clusterovaného columnstore, clusterovaného indexu a haldy. Particionování je také podporováno u všech typů distribuce, včetně distribuce typu hash nebo distribuce typu round robin.
Particionace může zlepšit údržbu dat a výkon dotazů. Bez ohledu na to, jestli je přínosem obojí nebo jen jeden, závisí na tom, jak se data načtou a jestli je možné použít stejný sloupec pro oba účely, protože dělení je možné provést pouze na jednom sloupci.
Výhody pro zátěž
Primární výhodou rozdělení v rámci vyhrazeného fondu SQL je zlepšení efektivity a výkonu načítání dat pomocí odstranění oddílů, přepínání oddílů a sloučení oddílů. Ve většině případů jsou data rozdělena do sloupce kalendářního data, který je úzce svázán s pořadím načtení dat do fondu SQL. Jednou z největších výhod použití oddílů k údržbě dat je zabránění protokolování transakcí. I když pouhé vložení, aktualizace nebo odstranění dat může být nejjednodušší přístup, při použití rozdělení během načítání můžete s trochou úsilí výrazně zvýšit výkon.
Přepínání particí se dá použít k rychlému odebrání nebo nahrazení části tabulky. Například tabulka faktů prodeje může obsahovat jenom data za posledních 36 měsíců. Na konci každého měsíce se z tabulky odstraní nejstarší měsíc prodejních dat. Tato data je možné odstranit pomocí příkazu delete k odstranění dat za nejstarší měsíc.
Odstranění velkého množství datových řádků po řádku pomocí příkazu delete však může zabrat příliš mnoho času a vytvářet riziko velkých transakcí, které trvá dlouho vrátit zpět, pokud se něco nepovede. Optimaličtějším přístupem je vyřadit nejstarší oddíl dat. Pokud odstranění jednotlivých řádků může trvat hodiny, může odstranění celého oddílu trvat několik sekund.
Výhody dotazů
Dělení je také možné použít ke zlepšení výkonu dotazů. Dotaz, který použije filtr na dělená data, může omezit kontrolu pouze na opravňující oddíly. Tato metoda filtrování se může vyhnout prohledávání celé tabulky a prohledávat pouze menší podmnožinu dat. Díky zavedení clusterovaných columnstore indexů jsou výhody z vyřazení predikátu pro výkon méně významné, ale v některých případech mohou být pro dotazy přínosné.
Pokud je například tabulka faktů prodeje rozdělená na 36 měsíců pomocí pole datum prodeje, můžou dotazy, které filtrují datum prodeje, přeskočit vyhledávání v oddílech, které neodpovídají filtru.
Velikost oddílů
I když je možné dělení použít ke zlepšení výkonu v některých scénářích, vytvoření tabulky s příliš mnoha oddíly může za určitých okolností poškodit výkon. Tyto obavy platí zejména pro clusterované tabulky columnstore.
Aby rozdělení bylo užitečné, je důležité pochopit, kdy použít partitioning a kolik oddílů je potřeba vytvořit. Neexistuje žádné rychlé pravidlo, kolik oddílů je příliš mnoho, závisí to na datech a na tom, kolik oddílů načítáte současně. Obvykle platné schéma rozdělení obsahuje desítky až stovky částí, ne tisíce.
Při vytváření oddílů v clusterovaných tabulkách columnstore je důležité zvážit, kolik řádků patří do každého oddílu. Pro optimální kompresi a výkon clusterovaných tabulek columnstore je potřeba minimálně 1 milion řádků na distribuci a oddíl. Před vytvořením oddílů už vyhrazený fond SQL rozdělí každou tabulku na 60 distribucí.
Jakékoli členění přidané do tabulky je navíc ke stávajícím distribucím vytvořeným na pozadí. V tomto příkladu platí, že pokud tabulka faktů o prodeji obsahovala 36 měsíčních částí a vzhledem k tomu, že vyhrazený fond SQL má 60 distribucí, měla by tabulka faktů o prodeji obsahovat 60 milionů řádků za měsíc nebo 2,1 miliardy řádků, když jsou všechny měsíce zaplněny. Pokud tabulka obsahuje méně než doporučený minimální počet řádků na oddíl, zvažte použití méně oddílů, abyste zvýšili počet řádků na oddíl.
Další informace najdete v článku indexování , který obsahuje dotazy, které můžou vyhodnotit kvalitu indexů columnstore clusteru.
Rozdíly v syntaxi oproti SQL Serveru
Vyhrazený fond SQL představuje způsob, jak definovat oddíly, které jsou jednodušší než SQL Server. Funkce a schémata dělení se nepoužívají ve vyhrazeném fondu SQL, protože jsou na SQL Serveru. Místo toho stačí identifikovat dělený sloupec a hraniční body.
I když se syntaxe dělení může mírně lišit od SQL Serveru, základní koncepty jsou stejné. SQL Server a vyhrazený fond SQL podporují jeden sloupec oddílů na tabulku, který může být rozdělený do rozsahu. Další informace o dělení najdete v tématu Dělené tabulky a indexy.
Následující příklad používá příkaz CREATE TABLE k rozdělení FactInternetSales tabulky na OrderDateKey sloupec:
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
)
)
);
Migrace oddílů z SQL Serveru
Pokud chcete migrovat definice oddílů SQL Serveru do vyhrazeného fondu SQL, stačí:
- Eliminujte schéma oddílů SQL Serveru.
- Přidejte definici partition funkce k vašemu CREATE TABLE.
Pokud migrujete dělenou tabulku z instance SQL Serveru, následující SQL vám může pomoct zjistit počet řádků v jednotlivých oddílech. Mějte na paměti, že pokud se ve vyhrazeném fondu SQL používá stejná členitost dělení, počet řádků na oddíl se sníží o faktor 60.
-- 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];
Přepínání oddílů
Vyhrazené SQL úložiště podporuje dělení, slučování a přepínání oddílů. Každá z těchto funkcí se provádí pomocí příkazu ALTER TABLE .
Pokud chcete přepínat oddíly mezi dvěma tabulkami, musíte zajistit, aby se oddíly zarovnaly na jejich odpovídajících hranicích a aby se definice tabulek shodovaly. Vzhledem k tomu, že kontrolní omezení nejsou k dispozici pro určení rozsahu hodnot v tabulce, musí zdrojová tabulka obsahovat stejné hranice oddílů jako cílová tabulka. Pokud hranice oddílů nejsou stejné, přepínač oddílu selže, protože metadata oddílu nebudou synchronizována.
Rozdělení oddílu vyžaduje, aby příslušný oddíl (ne nutně celá tabulka) byl prázdný, pokud má tabulka clusterovaný index columnstore (CCI). Ostatní oddíly ve stejné tabulce můžou obsahovat data. Oddíl, který obsahuje data, se nedá rozdělit, způsobí chybu: 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. Jako alternativní řešení rozdělení oddílu obsahujícího data najdete v tématu Postup rozdělení oddílu obsahujícího data.
Rozdělení oddílu obsahujícího data
Nejúčinnější metodou rozdělení oddílu, který již obsahuje data, je použití CTAS příkazu. Pokud je dělená tabulka clusterovým úložištěm sloupců, musí být oddíl tabulky prázdný, aby bylo možné ji rozdělit.
Následující příklad vytvoří dělenou tabulku columnstore. Vloží jeden řádek do každé partice.
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);
Následující dotaz najde počet řádků pomocí sys.partitions zobrazení katalogu:
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';
Následující příkaz rozdělení obdrží chybovou zprávu:
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.
Můžete ale použít CTAS k vytvoření nové tabulky pro uložení dat.
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;
Jakmile jsou hranice oddílů zarovnány, je povoleno přepnout. Zdrojová tabulka bude mít prázdný oddíl, který můžete následně rozdělit.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Vše, co zbývá, je zarovnat data k novým hranicím oddílů pomocí CTASa pak data přepnout zpět do hlavní tabulky.
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;
Po dokončení přesunu dat je vhodné aktualizovat statistiky cílové tabulky. Aktualizace statistik zajišťuje, aby statistiky přesně odrážely nové rozdělení dat v příslušných oddílech.
UPDATE STATISTICS [dbo].[FactInternetSales];
V případě jednorázového přepnutí oddílu pro přesun dat můžete nakonec odstranit tabulky vytvořené pro přepnutí oddílu FactInternetSales_20000101_20010101 a FactInternetSales_20000101. Případně můžete chtít ponechat prázdné tabulky pro pravidelné, automatizované přesuny oddílů.
Načtení nových dat do oddílů, které obsahují data v jednom kroku
Načtení dat do oddílů s přepínáním oddílů je pohodlný způsob, jak rozfázovat nová data v tabulce, která není viditelná pro uživatele. U zaneprázdněných systémů může být náročné řešit kolize uzamčení související s přepínáním oddílů.
Pokud bylo potřeba vymazat existující data v oddílu, ALTER TABLE se dříve vyžadovalo k výměně dat. Potom bylo nutné použít jiný ALTER TABLE pro přepnutí na nová data.
Ve vyhrazeném fondu SQL TRUNCATE_TARGET se možnost ALTER TABLE podporuje v příkazu.
TRUNCATE_TARGET Příkaz ALTER TABLE přepíše existující data v oddílu novými daty. Níže je příklad, který používá CTAS k vytvoření nové tabulky s existujícími daty, vloží nová data a pak přepne všechna data zpět do cílové tabulky a přepíše stávající data.
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);
Správa zdrojového kódu dělení tabulky
Poznámka:
Pokud není nástroj pro řízení verzí nakonfigurovaný tak, aby ignoroval schémata oddílů, může změna schématu tabulky pro účely aktualizace oddílů vést k vyřazení a opětovnému vytvoření tabulky v rámci nasazení, což může být nepraktické. Může být nezbytné vlastní řešení pro implementaci takové změny, jak je popsáno níže. Zkontrolujte, že to umožňuje nástroj pro kontinuální integraci nebo průběžné nasazování (CI/CD). V nástrojích SQL Server Data Tools (SSDT) vyhledejte rozšířené nastavení publikování 'Ignorovat schémata oddílů', abyste se vyhnuli vygenerovanému skriptu, který způsobí vyřazení tabulky a její opětovné vytvoření.
Tento příklad je užitečný při aktualizaci schémat oddílů prázdné tabulky. Pokud chcete průběžně nasazovat změny oddílů v tabulce s daty, postupujte podle kroků v části Rozdělení oddílu, který obsahuje data. Současně s nasazením dočasně přesuňte data z každého oddílu před použitím funkce SPLIT RANGE pro oddíly. To je nezbytné, protože nástroj CI/CD neví, které oddíly obsahují data.
Abyste se vyhnuli tomu, že definice tabulky zreziví v systému správy zdrojového kódu, měli byste zvážit následující přístup:
Vytvořte tabulku jako dělenou tabulku, ale bez hodnot oddí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 () ) );SPLITtabulka jako součást procesu nasazení:-- 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;
V tomto přístupu zůstává kód ve správě zdrojového kódu statický a hodnoty hranic rozdělení mohou být dynamické, vyvíjí se spolu s SQL fondem v průběhu času.
Související obsah
Další informace o vývoji tabulek najdete v přehledu tabulek.