Dělení tabulek ve vyhrazeném fondu SQL
Doporučení a příklady použití oddílů tabulky ve vyhrazeném fondu SQL
Co jsou oddíly 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. Dělení se podporuje u všech vyhrazených typů tabulek fondu SQL; včetně clusterovaného columnstore, clusterovaného indexu a haldy. Dělení se podporuje také u všech typů distribuce, včetně hodnoty hash nebo kruhového dotazování distribuovaného.
Dělení může těžit z údržby dat a výkonu 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 načtení
Primární výhodou dělení ve vyhrazeném fondu SQL je zlepšení efektivity a výkonu načítání dat pomocí odstranění oddílu, přepínání a sloučení. 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í. Při pouhém vkládání, aktualizaci nebo odstraňování dat může být nejjednodušší přístup s trochou myšlenky a úsilím, díky dělení během procesu načítání může výrazně zvýšit výkon.
Přepínání oddílů se dá použít k rychlému odebrání nebo nahrazení oddílu 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 trvat příliš dlouho a může způsobit riziko velkých transakcí, které trvá delší dobu, než se vrátí 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 indexů columnstore jsou predikátové výhody pro odstranění výkonu méně výhodné, 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.
Změna velikosti 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 dělení bylo užitečné, je důležité pochopit, kdy použít dělení a počet oddílů k vytvoření. 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ě. Úspěšné schéma dělení má obvykle desítky až stovky oddílů, 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 dělení přidané do tabulky je navíc k distribucím vytvořeným na pozadí. V tomto příkladu platí, že pokud tabulka faktů prodeje obsahovala 36 měsíčních oddílů 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ů při naplnění všech měsíců. Pokud tabulka obsahuje méně než doporučený minimální počet řádků na oddíl, zvažte použití menšího počtu 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 do tabulky CREATE TABLE definici funkce oddílu.
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ý fond SQL podporuje dělení oddílů, slučování a přepínání. 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 omezení kontroly nejsou k dispozici pro vynucení 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ží do každého oddílu jeden řádek:
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;
Jak jsou hranice oddílů zarovnané, je povolen přepínač. Tím ponecháte zdrojovou tabulku s prázdným oddílem, 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řepínač FactInternetSales_20000101_20010101 oddílu a FactInternetSales_20000101. Případně můžete chtít zachovat prázdné tabulky pro běžné automatické přepínače 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 chcete vymazat existující data v oddílu, ALTER TABLE je nutné je použít k přepnutí dat. Potom bylo nutné přepnout v nových datech jinou ALTER TABLE .
Ve vyhrazeném TRUNCATE_TARGET ALTER TABLE fondu SQL se tato možnost podporuje v příkazu. ALTER TABLE Příkaz TRUNCATE_TARGET 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 správu zdrojového kódu nakonfigurovaný tak, aby ignoroval schémata oddílů, může změna schématu tabulky na aktualizaci oddílů způsobit vyřazení a opětovné vytvoření tabulky v rámci nasazení, což může být neproveditelné. 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í a opětovné vytvoření tabulky.
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 společně s nasazením, a před použitím rozdělení rozsahu oddílu dočasně přesuňte data z každého oddílu. To je nezbytné, protože nástroj CI/CD neví, které oddíly obsahují data.
Abyste se vyhnuli definici tabulky v reziste v systému správy zdrojového kódu, měli byste zvážit následující přístup:
Vytvoření tabulky jako dělené tabulky, 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 dělení mohou být dynamické; vývoj s fondem SQL v průběhu času.
Související obsah
Další informace o vývoji tabulek najdete v přehledu tabulek.