Partycjonowanie tabel w dedykowanej puli SQL
Zalecenia i przykłady użycia partycji tabeli w dedykowanej puli SQL.
Co to są partycje tabeli?
Partycje tabel umożliwiają podzielenie danych na mniejsze grupy danych. W większości przypadków partycje tabeli są tworzone w kolumnie daty. Partycjonowanie jest obsługiwane we wszystkich dedykowanych typach tabel puli SQL; w tym klastrowany magazyn kolumn, indeks klastrowany i stertę. Partycjonowanie jest również obsługiwane we wszystkich typach dystrybucji, w tym zarówno skrótu, jak i rozproszonego działania okrężnego.
Partycjonowanie może przynieść korzyści związane z konserwacją danych i wydajnością zapytań. Niezależnie od tego, czy jest to korzystne zarówno, jak i tylko jeden, zależy od sposobu ładowania danych i tego, czy ta sama kolumna może być używana w obu celach, ponieważ partycjonowanie może odbywać się tylko w jednej kolumnie.
Korzyści z ładowania
Główną zaletą partycjonowania w dedykowanej puli SQL jest zwiększenie wydajności i wydajności ładowania danych przy użyciu usuwania partycji, przełączania i scalania. W większości przypadków dane są partycjonowane w kolumnie daty, która jest ściśle powiązana z kolejnością, w której dane są ładowane do puli SQL. Jedną z największych zalet używania partycji do obsługi danych jest unikanie rejestrowania transakcji. Podczas gdy po prostu wstawianie, aktualizowanie lub usuwanie danych może być najprostszym podejściem, przy odrobinie przemyślenia i wysiłku, użycie partycjonowania podczas procesu ładowania może znacznie poprawić wydajność.
Przełączanie partycji może służyć do szybkiego usuwania lub zastępowania sekcji tabeli. Na przykład tabela faktów sprzedaży może zawierać tylko dane z ostatnich 36 miesięcy. Na koniec każdego miesiąca najstarsze miesiące danych sprzedaży są usuwane z tabeli. Te dane można usunąć za pomocą instrukcji delete, aby usunąć dane w najstarszym miesiącu.
Jednak usunięcie dużej ilości danych z wierszem po wierszu z instrukcją delete może zająć zbyt dużo czasu, a także utworzyć ryzyko dużych transakcji, które potrwają długo, aby wycofać się, jeśli coś pójdzie nie tak. Bardziej optymalnym podejściem jest usunięcie najstarszej partycji danych. Usunięcie poszczególnych wierszy może potrwać kilka godzin, usunięcie całej partycji może potrwać kilka sekund.
Korzyści z zapytań
Partycjonowanie może również służyć do poprawy wydajności zapytań. Zapytanie, które stosuje filtr do partycjonowanych danych, może ograniczyć skanowanie tylko do kwalifikujących się partycji. Ta metoda filtrowania może uniknąć pełnego skanowania tabeli i skanować tylko mniejszy podzestaw danych. Wraz z wprowadzeniem klastrowanych indeksów magazynu kolumn korzyści z wydajności eliminacji predykatu są mniej korzystne, ale w niektórych przypadkach może istnieć korzyść dla zapytań.
Jeśli na przykład tabela faktów sprzedaży jest podzielona na 36 miesięcy przy użyciu pola data sprzedaży, zapytania filtrujące datę sprzedaży mogą pominąć wyszukiwanie w partycjach, które nie są zgodne z filtrem.
Ustalanie rozmiaru partycji
Chociaż partycjonowanie może służyć do poprawy wydajności niektórych scenariuszy, utworzenie tabeli z zbyt dużą liczbą partycji może zaszkodzić wydajności w pewnych okolicznościach. Te obawy są szczególnie istotne w przypadku tabel magazynu kolumn klastrowanych.
Aby partycjonowanie było przydatne, należy zrozumieć, kiedy używać partycjonowania i liczby partycji do utworzenia. Nie ma trudnej reguły co do liczby partycji jest zbyt wiele, zależy od danych i liczby partycji, które są ładowane jednocześnie. Pomyślny schemat partycjonowania zwykle ma dziesiątki do setek partycji, a nie tysięcy.
Podczas tworzenia partycji w tabelach magazynu kolumn klastrowanych należy wziąć pod uwagę liczbę wierszy należących do każdej partycji. Aby uzyskać optymalną kompresję i wydajność tabel magazynu kolumn klastrowanych, wymagana jest co najmniej 1 milion wierszy na dystrybucję i partycję. Przed utworzeniem partycji dedykowana pula SQL dzieli już każdą tabelę na 60 dystrybucji.
Wszystkie partycjonowanie dodane do tabeli jest dodatkiem do dystrybucji utworzonych w tle. Korzystając z tego przykładu, jeśli tabela faktów sprzedaży zawiera 36 partycji miesięcznych i biorąc pod uwagę, że dedykowana pula SQL ma 60 dystrybucji, tabela faktów sprzedaży powinna zawierać 60 milionów wierszy miesięcznie lub 2,1 miliarda wierszy po wypełnieniu wszystkich miesięcy. Jeśli tabela zawiera mniej niż zalecaną minimalną liczbę wierszy na partycję, rozważ użycie mniejszej liczby partycji w celu zwiększenia liczby wierszy na partycję.
Aby uzyskać więcej informacji, zobacz artykuł Indeksowanie , który zawiera zapytania, które mogą ocenić jakość indeksów magazynu kolumn klastra.
Różnice składniowe z SQL Server
Dedykowana pula SQL wprowadza sposób definiowania partycji, które są prostsze niż SQL Server. Funkcje partycjonowania i schematy nie są używane w dedykowanej puli SQL, ponieważ znajdują się w SQL Server. Zamiast tego wystarczy zidentyfikować kolumnę partycjonowaną i punkty granic.
Chociaż składnia partycjonowania może się nieco różnić od SQL Server, podstawowe pojęcia są takie same. SQL Server i dedykowana pula SQL obsługują jedną kolumnę partycji na tabelę, która może być podzielona na partycje. Aby dowiedzieć się więcej na temat partycjonowania, zobacz Partycjonowane tabele i indeksy.
W poniższym przykładzie użyto instrukcji CREATE TABLE do partycjonowania FactInternetSales tabeli w kolumnie 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
)
)
);
Migrowanie partycji z SQL Server
Aby przeprowadzić migrację definicji partycji SQL Server do dedykowanej puli SQL, wystarczy:
- Wyeliminuj schemat partycji SQL Server.
- Dodaj definicję funkcji partycji do tabeli CREATE.
Jeśli migrujesz tabelę partycjonowaną z wystąpienia SQL Server, poniższy kod SQL może pomóc w poznać liczbę wierszy w każdej partycji. Należy pamiętać, że jeśli ten sam stopień szczegółowości partycjonowania jest używany w dedykowanej puli SQL, liczba wierszy na partycję zmniejsza się o współczynnik 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];
Przełączanie partycji
Dedykowana pula SQL obsługuje dzielenie partycji, scalanie i przełączanie. Każda z tych funkcji jest wykonywana przy użyciu instrukcji ALTER TABLE .
Aby przełączyć partycje między dwiema tabelami, należy upewnić się, że partycje są wyrównane do odpowiednich granic i że definicje tabeli są zgodne. Ponieważ ograniczenia sprawdzania nie są dostępne do wymuszania zakresu wartości w tabeli, tabela źródłowa musi zawierać te same granice partycji co tabela docelowa. Jeśli granice partycji nie są takie same, przełączenie partycji zakończy się niepowodzeniem, ponieważ metadane partycji nie zostaną zsynchronizowane.
Podział partycji wymaga, aby odpowiednia partycja (niekoniecznie cała tabela) stała się pusta, jeśli tabela ma indeks klastrowanego magazynu kolumn (CCI). Inne partycje w tej samej tabeli mogą zawierać dane. Nie można podzielić partycji zawierającej dane, co spowoduje błąd: 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 obejście podziału partycji zawierającej dane, zobacz Jak podzielić partycję zawierającą dane.
Jak podzielić partycję zawierającą dane
Najbardziej wydajną metodą dzielenia partycji, która zawiera już dane, jest użycie CTAS instrukcji . Jeśli partycjonowana tabela jest klastrowany magazyn kolumn, partycja tabeli musi być pusta, zanim będzie można ją podzielić.
Poniższy przykład tworzy partycjonowaną tabelę magazynu kolumn. Wstawia jeden wiersz do każdej partycji:
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);
Następujące zapytanie znajduje liczbę wierszy przy użyciu sys.partitions widoku wykazu:
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';
Następujące polecenie podziału otrzymuje komunikat o błędzie:
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.
Można jednak użyć CTAS polecenia , aby utworzyć nową tabelę do przechowywania danych.
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;
Ponieważ granice partycji są wyrównane, przełącznik jest dozwolony. Spowoduje to pozostawienie tabeli źródłowej z pustą partycją, którą można następnie podzielić.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Wszystko, co pozostało, to wyrównanie danych do nowych granic partycji przy użyciu polecenia CTAS, a następnie przełączenie danych z powrotem do tabeli głównej.
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 zakończeniu przenoszenia danych warto odświeżyć statystyki w tabeli docelowej. Aktualizowanie statystyk zapewnia dokładne odzwierciedlenie nowej dystrybucji danych w odpowiednich partycjach.
UPDATE STATISTICS [dbo].[FactInternetSales];
Na koniec w przypadku jednorazowego przełącznika partycji w celu przeniesienia danych można usunąć tabele utworzone dla przełącznika FactInternetSales_20000101_20010101 partycji i FactInternetSales_20000101. Alternatywnie możesz zachować puste tabele dla zwykłych, zautomatyzowanych przełączników partycji.
Ładowanie nowych danych do partycji zawierających dane w jednym kroku
Ładowanie danych do partycji z przełączaniem partycji to wygodny sposób na przygotowanie nowych danych w tabeli, która nie jest widoczna dla użytkowników. Może to być trudne w przypadku zajętych systemów, aby poradzić sobie z rywalizacją o blokowanie skojarzone z przełączaniem partycji.
Aby wyczyścić istniejące dane w partycji, ALTER TABLE wymagane jest przełączenie danych. Następnie trzeba było przełączyć się ALTER TABLE na nowe dane.
W dedykowanej puli TRUNCATE_TARGET SQL opcja jest obsługiwana w poleceniu ALTER TABLE . ALTER TABLE Polecenie TRUNCATE_TARGET zastępuje istniejące dane w partycji nowymi danymi. Poniżej przedstawiono przykład, który służy CTAS do tworzenia nowej tabeli z istniejącymi danymi, wstawia nowe dane, a następnie przełącza wszystkie dane z powrotem do tabeli docelowej, zastępując istniejące dane.
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);
Kontrola źródła partycjonowania tabel
Uwaga
Jeśli narzędzie kontroli źródła nie jest skonfigurowane do ignorowania schematów partycji, zmiana schematu tabeli w celu zaktualizowania partycji może spowodować, że tabela zostanie porzucona i utworzona ponownie w ramach wdrożenia, co może być niewykonalne. Konieczne może być niestandardowe rozwiązanie do zaimplementowania takiej zmiany, jak opisano poniżej. Sprawdź, czy narzędzie ciągłej integracji/ciągłego wdrażania (CI/CD) pozwala na to. W programie SQL Server Data Tools (SSDT) poszukaj ustawień zaawansowanych publikowania "Ignoruj schematy partycji", aby uniknąć wygenerowanego skryptu, który powoduje usunięcie i ponowne utworzenie tabeli.
Ten przykład jest przydatny podczas aktualizowania schematów partycji pustej tabeli. Aby stale wdrażać zmiany partycji w tabeli z danymi, wykonaj kroki opisane w temacie Jak podzielić partycję zawierającą dane wraz z wdrożeniem, aby tymczasowo przenieść dane z każdej partycji przed zastosowaniem zakresu podziału partycji. Jest to konieczne, ponieważ narzędzie ciągłej integracji/ciągłego wdrażania nie ma informacji o tym, które partycje mają dane.
Aby uniknąć zardzewiania definicji tabeli w systemie kontroli źródła, warto rozważyć następujące podejście:
Utwórz tabelę jako tabelę partycjonowaną, ale bez wartości partycji
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 () ) );SPLITtabela w ramach procesu wdrażania:-- 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;
W przypadku tego podejścia kod w kontroli źródła pozostaje statyczny, a wartości granic partycjonowania mogą być dynamiczne; w miarę rozwoju puli SQL z upływem czasu.
Następne kroki
Aby uzyskać więcej informacji na temat tworzenia tabel, zobacz artykuły w temacie Omówienie tabel.