Dělení tabulek ve vyhrazeném fondu SQL
Doporučení a příklady pro 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í je podporováno u všech typů vyhrazených tabulek fondu SQL. včetně clusterovaného columnstore, clusterovaného indexu a haldy. Dělení je také podporováno u všech typů distribuce, včetně distribuovaných hodnot hash nebo kruhového dotazování.
Dělení může prospět údržbě dat a výkonu dotazů. To, jestli to prospívá oběma nebo jenom jednomu, závisí na tom, jak se data načítají a jestli se stejný sloupec dá použít pro oba účely, protože dělení se dá provést jenom u jednoho sloupce.
Výhody zatížení
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ílů, přepínání a sloučení. Ve většině případů jsou data rozdělená na sloupec kalendářních dat, který je úzce svázán s pořadím načítání 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ž jednoduché vkládání, aktualizace nebo odstraňování dat může být nejjednodušší přístup, s trochou přemýšlení a úsilí, použití dělení během procesu načítání může výrazně zvýšit výkon.
Přepínání oddílů lze použít k rychlému odebrání nebo nahrazení oddílu tabulky. Například tabulka faktů o prodeji 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ší data o prodeji. Tato data je možné odstranit pomocí příkazu delete, který odstraní data za nejstarší měsíc.
Odstranění velkého množství dat řádek po řádku pomocí příkazu delete však může trvat příliš dlouho, stejně jako riziko velkých transakcí, u které bude trvat dlouhou dobu, než se vrátí zpět, pokud se něco nepovede. Optimálním přístupem je odstranění nejstaršího oddílu dat. Odstranění jednotlivých řádků může trvat několik hodin a odstranění celého oddílu může trvat několik sekund.
Výhody dotazů
K vylepšení výkonu dotazů můžete použít také dělení. 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. Se zavedením clusterovaných indexů columnstore jsou výhody odstranění predikátu z 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ů o prodeji rozdělená do 36 měsíců pomocí pole data prodeje, můžou dotazy, které filtrují datum prodeje, přeskočit vyhledávání v oddílech, které neodpovídají filtru.
Nastavení velikosti oddílů
I když se v některých scénářích dá dělení použít ke zvýšení výkonu, 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 bylo dělení užitečné, je důležité vědět, kdy se má dělení použít, a počet oddílů, které se mají vytvořit. Neexistuje žádné pevně rychlé pravidlo, kolik oddílů je příliš mnoho, závisí na vašich 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ří jednotlivým oddílům. 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 do 60 distribucí.
Jakékoli dělení přidané do tabulky je navíc k distribucím vytvořeným na pozadí. Pokud tabulka faktů o prodeji obsahovala 36 měsíčních oddílů a vyhrazený fond SQL má 60 distribucí, měla by tabulka faktů o prodeji obsahovat 60 milionů řádků měsíčně nebo 2,1 miliardy řádků, pokud se naplní všechny měsíce. 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 o indexování , který obsahuje dotazy, které můžou vyhodnotit kvalitu indexů columnstore clusteru.
Rozdíly v syntaxi od SQL Server
Vyhrazený fond SQL představuje způsob definování oddílů, který je jednodušší než SQL Server. Funkce a schémata dělení se ve vyhrazeném fondu SQL nepoužívají, protože jsou ve SQL Server. 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 Server, základní koncepty jsou stejné. SQL Server a vyhrazený fond SQL podporují jeden sloupec oddílu na tabulku, který může být v rozsahu oddílu. 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 ve sloupci OrderDateKey :
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 Server
Pokud chcete migrovat definice oddílů SQL Server do vyhrazeného fondu SQL, jednoduše:
- Eliminujte schéma SQL Server oddílů.
- Přidejte definici funkce oddílu do CREATE TABLE.
Pokud migrujete tabulku s oddíly z SQL Server instance, 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 rozdě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 byly oddíly zarovnané na příslušný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 se hranice oddílů neshodují, přepínač oddílů selže, protože metadata oddílu nebudou synchronizována.
Rozdělení oddílu vyžaduje, aby byl příslušný oddíl (ne nutně celá tabulka) prázdný, pokud má tabulka clusterovaný index columnstore (CCI). Další oddíly ve stejné tabulce můžou obsahovat data. Oddíl, který obsahuje data, nelze rozdělit. Výsledkem bude chyba: 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. Alternativní řešení rozdělení oddílu obsahujícího data najdete v tématu Rozdělení oddílu, který obsahuje data.
Jak rozdělit oddíl, který obsahuje data
Nejúčinnější metodou rozdělení oddílu, který už obsahuje data, je použití CTAS příkazu. Pokud je dělená tabulka clusterovaným úložištěm sloupců, musí být oddíl tabulky před rozdělením prázdný.
Následující příklad vytvoří tabulku columnstore s oddíly. 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 vyhledá 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;
Když jsou hranice oddílů zarovnané, je přepínač povolený. Zdrojová tabulka tak zůstane 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);
Zbývá jen zarovnat data k novým hranicím oddílů pomocí CTASa pak je 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 v cílové tabulce. 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];
Nakonec v případě jednorázového přepínače oddílů pro přesun dat můžete vypustit tabulky vytvořené pro přepínač FactInternetSales_20000101_20010101 oddílů 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čítání dat do oddílů s přepínáním oddílů je pohodlný způsob, jak vytvořit nová data v tabulce, která není viditelná pro uživatele. V zaneprázdněných systémech může být náročné řešit kolize s uzamykáním souvisejícím s přepínáním oddílů.
Pokud chcete vymazat existující data v oddílu, ALTER TABLE je potřeba použít k přepnutí dat. ALTER TABLE Pak bylo potřeba, aby se nová data přepínala.
Ve vyhrazeném TRUNCATE_TARGET fondu SQL je tato možnost v příkazu podporovaná ALTER TABLE . Příkazem TRUNCATE_TARGETALTER TABLE přepíše stávají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í na tabulky
Poznámka
Pokud váš nástroj pro správu zdrojového kódu není nakonfigurovaný tak, aby ignoroval schémata oddílů, může změna schématu tabulky tak, aby aktualizoval oddíly, způsobit vyřazení a opětovné vytvoření tabulky v rámci nasazení, což může být neproveditelné. Může být nutné použít vlastní řešení pro implementaci takové změny, jak je popsáno níže. Zkontrolujte, jestli to váš nástroj pro kontinuální integraci/průběžné nasazování (CI/CD) umožňuje. V 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 tématu Rozdělení oddílu obsahujícího data společně s nasazením, abyste před použitím oblasti ROZDĚLENÍ oddílu dočasně přesunuli data z jednotlivých oddílů. To je nezbytné, protože nástroj CI/CD neví, které oddíly obsahují data.
Pokud chcete zabránit tomu, aby se definice tabulky ve vašem systému správy zdrojového kódu zrezivěla , 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 v rámci 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;
Při 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é; s využitím fondu SQL v průběhu času.
Další kroky
Další informace o vývoji tabulek najdete v článcích o přehledu tabulek.