Partycjonowanie tabel a wydajność zapytań w SQL Server

Partycjonowanie jest szeroko znaną techniką użyteczną w bardzo wielu aspektach pracy związanych z tabelami i indeksami w SQL Server. Zanim zostanę posądzony o herezje to chciałem zaznaczyć, że techniki tej nie należy traktować jako stricte funkcjonalności poprawiania wydajności, jednakże sam sposób jej działania może pozytywnie wpłynąć również na przetwarzanie naszych zapytań odczytujących oraz ładujących. Z partycjami związany jest szereg różnych mechanizmów takich jak partition switching czy chociażby partition elimination, które mogą być bardzo pomocne w codziennej pracy. W ramach niniejszego artykułu przedstawie kilka ogólnych właściwości tabel partycjonowanych oraz powiemy sobie co nieco o eliminacji partycji.

Całość tematu będzie najlepiej zrozumieć na konkretnym przykładzie, dlatego też stwórzmy sobie bazę danych na której będziemy działać. Poniższy skrypt stworzy bazę Partitioning oraz dwie tabele będące kopią Fact.Sale z WideWorldImporters – jedną z nich w dalszej części będziemy partycjonować:

USE master
GO

DROP DATABASE IF EXISTS Partitioning
GO

CREATE DATABASE Partitioning
GO

USE Partitioning
GO

SELECT *
INTO dbo.FactSalePartitioned
FROM WideWorldImportersDW.Fact.Sale;
GO

SELECT *
INTO dbo.FactSale
FROM WideWorldImportersDW.Fact.Sale;
GO

W momencie gdy mamy już obie tabele to możemy zabrać się za partycjonowanie jednej z nich. Pierwszym krokiem będzie stworzenie funkcji partycjonującej, jako klucz partycjonujący wybrałem pojedynczy rok:

CREATE PARTITION FUNCTION udf_partitionByYear(date) 
AS RANGE RIGHT 
FOR VALUES(
     '20130101'
    ,'20140101'
    ,'20150101'
    ,'20160101');
GO

Następnie przejdźmy do tworzenia schematu partycjonującego, który przypisze wszystkie partycje do głównej grupy plików (oczywiście w rzeczywistości jeśli mamy możliwość umieszczenia partycji w osobnych grupach plików na osobnych dyskach tym lepiej dla nas):

CREATE PARTITION SCHEME ups_partionByYear 
    AS PARTITION udf_partitionByYear
    ALL TO ([PRIMARY])
GO

Mamy już do naszej dyspozycji wszystkie składowe potrzebne do partycjonowania, stwórzmy sobie teraz indeksy klastrowane na naszych tabelach. Tak jak wspomniałem wcześniej jeden będzie używał partycjonowania drugi nie. Przy partycjonowaniu musimy zwrócić uwagę na następujące aspekty:

• jeśli tabela zawiera nieunikalny indeks nieklastrowany to jeżeli klucz partycjonowania nie znajduje się w kluczu indeksu to zostanie dodany do poziomu liści indeksu
• jeśli tabela zawiera nieunikalny indeks klastrowany to możemy dodać klucz partycjonowania do klucza indeksu lub SQL Server zrobi to za nas
• jeśli tabela zawieraj unikalny indeks nieklastrowany lub klastrowany(my będziemy partycjonować po kolumnie Invoice Date Key)  klucz partycjonowania musi również być częścią klucza indeksu dlatego też umieściłem go w odpowiedniej sekcji:

CREATE UNIQUE CLUSTERED INDEX CIX_FactSales
    on dbo.FactSale ([Invoice Date Key],[Sale key])
GO

CREATE UNIQUE CLUSTERED INDEX CIX_FactSales
 on dbo.FactSalePartitioned ([Invoice Date Key],[Sale key])
ON ups_partionByYear ([Invoice Date Key])
GO

Już w tym miejscu powinniśmy zwrócić uwagę na to, że klucz partycjonujący jest również częścią klucza indeksu klastrowanego, zależność ta powoduje, że klucz ten staje się również częścią każdego indeksu nieklastrowanego powiększając przy tym jego rozmiar. Powiększony indeks będzie powodował większe IO w porównaniu do indeksu na tabeli niepartycjonowanej – warto pamiętać o tej zależności bo może ona mieć duży wpływ na wydajność, szczególnie przy większych tabelach. W tym przypadku znów znajduje zastosowanie zasada mówiąca o tym, żeby wybrać jak najmniejszy typ danych, tym razem jako klucz partycji.

Odpytajmy naszą partycjonowaną tabelę i wyświetlmy jej plan zapytania:

SELECT SUM(Quantity) FROM dbo.FactSalePartitioned
WHERE [Invoice Date Key] BETWEEN '20160101' AND '20161231'

Według naszych założeń wszystkie dane znajdują się w pojedynczej partycji, tak więc pozostałe partycje powinny zostać wyeliminowane. Aby sprawdzić czy wszystko przebiegło zgodnie z oczekiwaniami spójrzmy na plan zapytania:

Aby dowiedzieć się czegoś na temat omawianego mechanizmu, musimy spojrzeć we właściwości operatora Index Seek (lub Index Scan w innym przypadku). Pierwszą istotną właściwością tego operatora, która nas w tym przypadku interesuje jest Partitioned, która daje nam informację o tym czy  tabela z której korzystamy jest partycjonowana czy też nie:

Równie o ile nie bardziej interesujące mogą wydawać się właściwości Actual Partition Count oraz Actual Partition Accessed:

Actual Partition Count oznacza ile partycji zostało odczytanych w ramach zapytania. Actual Partitions Accessed może być nieco mylące i oznacza numer(y) partycji, które zostały odczytane. W przypadku odczytania jednej partycji wygląda to jak powyżej, jak byśmy zmienili zapytanie w taki sposób aby odczytane zostało więcej partycji:

SELECT SUM(Quantity) 
FROM dbo.FactSalePartitioned
WHERE [Invoice Date Key] BETWEEN '20150101' AND '20161231'

to wyświetlana wartość wygląda inaczej. Na tym przykładzie nieco lepiej widać, że odczytane zostały dwie partycje o numerach 4 i 5. Kropki pomiędzy 4 i 5 oznaczają nic innego jak “od do”. Zmodyfikujmy jeszcze nasze zapytanie tak aby odpytywało trzy partycje, które dodatkowo nie są ulokowane “obok” siebie:

SELECT SUM(Quantity) 
FROM dbo.FactSalePartitioned
WHERE 
 [Invoice Date Key] BETWEEN '20120101' AND '20121231'
OR [Invoice Date Key] BETWEEN '20140101' AND '20141231'
OR [Invoice Date Key] BETWEEN '20160101' AND '20161231'

Actual Partitions Accessed wylistuje po przecinku wszystkie numery partycji, które zostały odczytane:

W porządku wiemy teraz jak sprawdzić czy partycje są eliminowane w taki sposób jakbyśmy tego chcieli. Porównajmy teraz wydajność zapytań odpytujących tabele o tej samej strukturze z partycjonowaniem i bez, podejrzymy sobie również statystki IO:

SET STATISTICS IO ON

SELECT SUM(Quantity) FROM dbo.FactSalePartitioned
WHERE [Invoice Date Key] BETWEEN '20160101' AND '20161231'

SELECT SUM(Quantity) FROM dbo.FactSale
WHERE [Invoice Date Key] BETWEEN '20160101' AND '20161231'

Po samych tylko estymatach widzimy drobną przewagę tabeli partycjonowanej jednakże nie jest to oszałamiająca różnica. Jeśli chodzi o statystyki IO to również tam większych różnic nie ma:

Dlatego tak się dzieje? “Winny” jest indeks klastrowany, którego kluczem jest również klucz partycji. Przeszukiwanie indeksu po strukturach b-drzewa jest operacją na tyle efektywną w tym przypadku, że nie ma potrzeby przeszukiwania całej tabeli i większego wzrostu z partycjonowania nie zobaczymy. Tak jak wspomniałem już wcześniej do klucza indeksu unikalnego musimy wrzucić klucz partycjonowania, nie jest wymagane to aby ten klucz był na pierwszym miejscu i zazwyczaj nie będziemy chcieli go tam umieścić, tak więc powyższy przypadek jest raczej wyjątkiem niż regułą.

Klucz partycjonujący zazwyczaj nie jest tak selektywny jak powinien być klucz indeksu więc jeśli już znajdzie się w kluczu indeksu to zapewne na mniej istotnym miejscu. Zmieńmy kolejność kolumn w kluczu i sprawdźmy jak wtedy będą przedstawiać się wyniki:

CREATE UNIQUE CLUSTERED INDEX CIX_FactSales
    on dbo.FactSalePartitioned ([Sale key],[Invoice Date Key])
WITH (DROP_EXISTING=ON)
ON ups_partionByYear  ([Invoice Date Key])
GO

CREATE UNIQUE CLUSTERED INDEX CIX_FactSales
    on dbo.FactSale ([Sale key],[Invoice Date Key])
WITH (DROP_EXISTING=ON)
GO

Po wykonaniu dokładnie tego samego zapytania testującego otrzymujemy już dużo większe różnice:

Dostaliśmy dosyć fajny wzrost wydajności w porównaniu do zapytania, które nie korzystało z partycjonowanej tabeli. Przykład ten jawnie pokazuje, jak ważna jest kolejność kolumn w kluczu indeksu. Co ciekawe eliminacja partycji będzie prawidłowo działała nawet w sytuacji gdy nasz plan zostanie umieszczony w cache i będzie używany wielokrotnie. Dzieje się tak ze względu na to, że w planie estymowanym (czyli tym w cache) nie ma informacji o tym, które partycje zostały użyte. Informacje te możemy uzyskać patrząc na to ile logicznych odczytów nasze zapytanie wygenerowało – dane te są dostępne w widoku jak sys.dm_exec_query_stats.

Na powyższym planie zapytania możemy również dostrzec informację o brakującym indeksie, ale tylko w przypadku tabeli bez partycji. Dlaczego tak się stało? Ponieważ nasz klucz partycjonujący pokrywał się z tym co umieściliśmy w sekcji WHERE naszego zapytania i SQL Server mógł po prostu “wyszukiwać” po partycjach, co zobrazujemy sobie poniżej. To, że nie otrzymaliśmy ostrzeżenia “missing index” może być nieco mylące i warto pamiętać o tym podczas analizy indeksów na naszej bazie danych.

Oczywiście możemy partycjonować nie tylko tabele klastrowane, ale również widoki zmaterializowane lub nawet sterty. Aby partycjonować stertę nie możemy użyć składni SELECT INTO ze względu na brak możliwości zdefiniowania sekcji ON wskazującej  funkcję partycjonującą  lub grupę plików. Póki co nie doczekaliśmy się składni CTAS czyli CREATE TABLE AS SELECT, którą mamy dostępną w Azure SQL Data Warehouse, jedyne co nam pozostaje w takim przypadku to stworzenie tabeli przed wstawieniem danych:

CREATE TABLE [dbo].[FactSaleHeap](
	[Sale Key] [bigint] NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Bill To Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Invoice Date Key] [date] NOT NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[WWI Invoice ID] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Profit] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Total Dry Items] [int] NOT NULL,
	[Total Chiller Items] [int] NOT NULL,
	[Lineage Key] [int] NOT NULL
)
ON ups_partionByYear  ([Invoice Date Key])

GO
INSERT INTO [dbo].[FactSaleHeap]
SELECT * FROM WideWorldImportersDW.FAct.Sale s

Przy odpytaniu naszej tabeli zapytaniem testowym otrzymamy informację, że eliminacja partycji zadziałała w sposób prawidłowy. Poniżej porównanie  operatorów Table Scan dla sterty partycjonowanej:

oraz dla sterty bez partycji:

W obu przypadkach mamy predykaty rezydualne więc filtrowanie zostało przeniesione na operator skanujący. Ale to co jest ciekawe to fakt, że w przypadku tabeli z partycjami warunek partycjonujący wystąpił jako Seek Predicate, a więc zastąpił nam niejako indeks. Jest to bardzo ważna informacja szczególnie w przypadku procesów ETL gdzie dzięki temu zjawisku w połączeniu z minimalnym logowaniem (które dosyć łatwo uzyskać na tabeli bez indeksów) czy partition switching możemy osiągnąć pożądaną wydajność. Dodatkowo możemy tak dobrać klucz partycjonowania aby bez dodatkowych struktur indeksowych, które są po prostu kopią danych uzyskać stosunkowo wydajne operacje pobierania większej ilości danych.

Z drugiej strony z partycjonowaniem wiąże się wiele wad. Wspominałem już o tym, że  tworząc partycje zwiększamy rozmiar wszystkich indeksów na tabeli, co nie jest jedyną wadą. Możemy mieć również problemy z unikalnością tabel ponieważ skoro klucz partycji ma być częścią indeksu to w przypadku indeksów unikalnych otrzymamy unikalność na poziomie partycji, a nie na poziomie całej tabeli (a jak wiemy unikalne ograniczenia również pełnią rolę w doborze odpowiedniego planu zapytania). Istnieje również kilka wzorców gdzie partycjonowane tabele nie wypadają najlepiej, a tabele bez partycji radzą sobie świetnie. Pokażmy sobie to na przykładzie – przypuśćmy, że chcemy wybrać maksymalny Sale Key w naszych tabelach:

SELECT MAX([Sale Key])
 FROM dbo.FactSalePartitioned


SELECT MAX([Sale Key])
 FROM dbo.FactSale

Wcześniej stworzyliśmy indeks, którego wiodącą kolumną była [Sale Key] posortowana rosnąco. Czego powinniśmy się spodziewać? Powinniśmy zobaczyć tzw. BACKWARD SCAN czyli skanowanie indeksu od tyłu i zgodnie z mechanizmem Row Goal cała operacja powinna się zakończyć po pobraniu jednego wiersza. Spójrzmy na plan zapytania tabeli niepartycjonowanej:

Z operatora Clustered Index Scan pobrany został jeden wiersz a we właściwościach zobaczymy, że kierunek skanowania był zgodny z tym co zakładaliśmy:

Jak natomiast wygląda plan dla tabeli partycjonowanej:

Aby wyciągnąć wartość maksymalną musieliśmy pobrać wszystkie wartości! Podobna sytuacja ma miejsce przy agregacji MIN czy chociażby w przypadku użycia TOP. To nie są odosobnione przypadki ponieważ możemy napotkać na inne problemy, których troubleshooting jest nieco bardziej skomplikowany w porównaniu do tabel niepartycjonowanych. Bardzo dobrze podsumował sytuację  jeden z inżynierów Microsoftu, który parafrazując  powiedział, że “maksymalnie co możemy osiągnąć przy partycjonowaniu to nie stracić na wydajności. Wszelkie usprawnienia są jedynie efektem ubocznym i tak powinniśmy je traktować” – nic dodać nic ująć (link).

Myślę, że znajomość opisanych mechanizmów jest dosyć istotna ponieważ partycje mimo, że nie są przeznaczone do optymalizacji zapytań to mogą takim narzędziem się stać jeśli odpowiednio się zaplanuje poszczególne ładowania. Jeżeli chcecie ulepszać swoje zapytania korzystajcie przede wszystkim z indeksów, o partycjach pamiętajcie w przypadku gdy istnieje potrzeba wydajnego ładowania większej ilości danych, przy odpytywaniu stert, przy braku dostatecznie selektywnego klucz filtracji oraz w momencie gdy nie możemy tworzyć indeksów ze względu na oszczędności miejsca na dysku. Partycjonowanie jest na tyle szerokim tematem, że z całą pewnością napiszę jeszcze kilka artykułów na ten temat.