SQL Server – wstawianie danych a kompresja stron na stertach

Już jakiś czas nie pisałem na niniejszym blogu niczego na temat bazy danych SQL Server dlatego postanowiłem to zmienić i opisać dosyć ciekawy mechanizm. Chodzi mianowicie o kompresję, tak tę tradycyjną kompresję stron, która jest powszechnie znana ale charakteryzuje się wieloma właściwościami, które mogą nas nieco zaskoczyć. Jedną z takich właściwości jest “współpraca” kompresji i tabel bez indeksów czyli stert (ang. heap). Współpraca ta na pierwszy rzut oka wygląda dokładnie tak jak byśmy się tego spodziewali, ale ciekawe efekty pojawiają się wtedy, gdy chcemy do takiej tabeli wstawić dane. Właśnie temu zagadnieniu chciałbym poświęcić niniejszy wpis do lektury, którego serdecznie zapraszam.

Na samym początku powiedzmy sobie po co nam sterta, a już tym bardziej skompresowana sterta? Jest to całkiem przydatna struktura dla wszelkiego rodzaju obszarów przejściowych gdzie tymczasowo trzymamy dane w procesie ładującym. Sterty są użyteczne w dużych przerzutach danych dlatego, że przede wszystkim bardzo łatwo osiągnąć na nich minimalne wykorzystanie dziennika transakcyjnego co w wielu przypadkach jest kluczowe aby osiągnąć zakładany poziom wydajności. Dla mnie osobiście w takim scenariuszu bardziej kuszącą propozycją jest wykorzystanie indeksów kolumnowych lub non-durable tables z In-memory OLTP ale sterty nadal znajdują zastosowanie w wielu projektach. Tym bardziej ciekawy może okazać się temat kompresji tychże struktur pozwalający zwiększyć wydajność zmniejszając rozmiar danych jaki ma być pobrany ze struktur dyskowych. O samej kompresji już pisałem w ramach niniejszego bloga tutaj oraz tutaj tak więc jeśli ktoś jest zainteresowany odsyłam do tych właśnie artykułów.

Przejdźmy zatem do meritum. Zaczniemy klasycznie, a mianowicie przygotujemy sobie zbiór testowy oparty o AdventureWorksDW, a konkretnie o tabelę FactResellerSales. Stwórzmy sobie kopię tejże tabeli tylko bez żadnego znajdującego się w pierwowzorze indeksu – skrypt wygląda następująco:

CREATE TABLE [dbo].[FactResellerSalesHeap](
	[ProductKey] [int] NOT NULL,
	[OrderDateKey] [int] NOT NULL,
	[DueDateKey] [int] NOT NULL,
	[ShipDateKey] [int] NOT NULL,
	[ResellerKey] [int] NOT NULL,
	[EmployeeKey] [int] NOT NULL,
	[PromotionKey] [int] NOT NULL,
	[CurrencyKey] [int] NOT NULL,
	[SalesTerritoryKey] [int] NOT NULL,
	[SalesOrderNumber] [nvarchar](20) NOT NULL,
	[SalesOrderLineNumber] [tinyint] NOT NULL,
	[RevisionNumber] [tinyint] NULL,
	[OrderQuantity] [smallint] NULL,
	[UnitPrice] [money] NULL,
	[ExtendedAmount] [money] NULL,
	[UnitPriceDiscountPct] [float] NULL,
	[DiscountAmount] [float] NULL,
	[ProductStandardCost] [money] NULL,
	[TotalProductCost] [money] NULL,
	[SalesAmount] [money] NULL,
	[TaxAmt] [money] NULL,
	[Freight] [money] NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[CustomerPONumber] [nvarchar](25) NULL,
	[OrderDate] [datetime] NULL,
	[DueDate] [datetime] NULL,
	[ShipDate] [datetime] NULL,
) ON [PRIMARY]

Gdy mamy naszą upragnioną stertę, spróbujmy ją uzupełnić danymi. Powszechnie wiadomo, że baza AdventureWorksDW nie jest jakimś kolosem jednak na potrzeby niniejszej demonstracji w zupełności wystarczy. Wstawimy sobie zawartość wybranej tabeli do naszej sterty – całą operację wykonamy dziesięciokrotnie.

INSERT INTO [dbo].[FactResellerSalesHeap]
SELECT * FROM [dbo].[FactResellerSales]
GO 10

Oryginalna tabela ma nieco powyżej 60 tysięcy wierszy tak więc powinniśmy otrzymać nieco powyżej 600k – na ten moment to wystarczy. Mając już stertę możemy estymować z jaką oszczędnością miejsca mamy do czynienia. Posłuży nam do tego procedura sp_estimate_data_compression_savings (estymujemy oczywiście kompresję stron):

EXEC sp_estimate_data_compression_savings 'dbo', 'FactResellerSalesHeap', NULL, NULL, 'PAGE' ;  
GO

Rezultat mimo, że nie jest bardzo precyzyjny pozwala nam ocenić, że z kompresją stron możemy zaoszczędzić całkiem sporo miejsca:

Możemy powiedzieć, że mamy 3-4 krotnie mniej zajętego miejsca co jest naprawdę dobrym wynikiem. W porządku, taki rezultat nas satysfakcjonuje włączmy zatem kompresję dla tej tabeli używając składni ALTER TABLE… REBUILD:

ALTER TABLE [dbo].[FactResellerSalesHeap]
REBUILD WITH (DATA_COMPRESSION=PAGE)

Po sprawdzeniu po raz kolejny szacunków widzimy, że wszystko przebiegło zgodnie z planem:

Wszystko zgodnie z przewidywaniami. W taki razie spróbujmy teraz wstawić po raz kolejny wiersze z oryginalnej tabeli, a następnie zbadajmy miejsce używając estymacji (pominę w tym miejscu skrypt ponieważ nie różni się on absolutnie niczym w stosunku do przedstawionych powyżej). Rezultat jest zastanawiający:

Wygląda na to, że wstawione dane nie zostały skompresowane, a jeśli już to z całą pewnością nie w całości! Jak to możliwe? Tak proszę Państwa – dane wstawiane “w standardowy sposób” do skompresowanej sterty nie są kompresowane kompresją stron w locie! Związane jest to z odmiennym w stosunku do tabel klastrowanych sposobem rezerwacji miejsca dla stert. No dobrze ale czy możemy coś z tym zrobić? Oczywiście, że tak! Po pierwsze możemy wykonać użytą wcześniej komendę ALTER TABLE… REBUILD. Sposób ten jednakże z całą pewnością nie jest idealny gdyż przebudowywanie za każdym razem całej tabeli może być nieefektywne.

Inny sposobem aby otrzymać pożądany efekt może być hint blokujący całą tabelę czyli TABLOCK. Aby przetestować jego działanie najpierw istniejące dane skompresujmy:

ALTER TABLE [dbo].[FactResellerSalesHeap]
REBUILD WITH (DATA_COMPRESSION=PAGE)

EXEC sp_estimate_data_compression_savings 'dbo', 'FactResellerSalesHeap', NULL, NULL, 'PAGE' ;  
GO

Mamy czystą kartę tak więc wykonajmy INSERT z TABLOCK:

INSERT INTO [dbo].[FactResellerSalesHeap] WITH (TABLOCK)
SELECT * FROM [dbo].[FactResellerSales]
go 10

Oprócz tego, że operacja jest minimalnie logowana możemy zauważyć, że dane zostały skompresowane w locie:

Łatwo szybko i przyjemnie. Trzecim sposobem na osiągnięcie efektu kompresji na stercie jest BULK INSERT dostępny z poziomu np. interfejsu programistycznego, narzędzia bcp czy chociażby Integration Services. Pozwoliłem sobie wykorzystać SSIS’a i stworzyłem prosty przepływ, który prezentuje się następująco:

Source to nic innego jak dziesięć komend SELECT * FROM tabela połączonych UNION ALL. Trochę ciekawsza jest konfiguracja elementu Destination:

Jako Data access mode wybrałem Table or view – fast load oraz zaznaczyłem opcję dodatkową o nazwie Tablock.

Przed eksperymentem wyczyściłem tabelę poleceniem TRUNCATE TABLE. Po uruchomieniu tego przepływu możemy wyłapać komendę wysyłaną przez SSISa ( w dowolny sposób ja wykorzystałem sp_whoisactive) i wygląda ona następująco:

insert bulk 
[dbo].[FactResellerSalesHeap]
(	[ProductKey] int,[OrderDateKey] int,[DueDateKey] int,[ShipDateKey] int,[ResellerKey] int,[EmployeeKey] int,[PromotionKey] int,
[CurrencyKey] int,[SalesTerritoryKey] int,[SalesOrderNumber] nvarchar(20) collate SQL_Latin1_General_CP1_CI_AS,[SalesOrderLineNumber] tinyint,
[RevisionNumber] tinyint,
[OrderQuantity] smallint,[UnitPrice] money,[ExtendedAmount] money,[UnitPriceDiscountPct] float,[DiscountAmount] float,[ProductStandardCost] money,
[TotalProductCost] money,[SalesAmount] money,[TaxAmt] money,[Freight] money,[CarrierTrackingNumber] nvarchar(25) collate SQL_Latin1_General_CP1_CI_AS,
[CustomerPONumber] nvarchar(25) collate SQL_Latin1_General_CP1_CI_AS,[OrderDate] datetime,[DueDate] datetime,[ShipDate] datetime)
with(TABLOCK)

Po sprawdzeniu kompresji przedstawia się to następująco:

Tak więc wygląda na to, że również ta metoda działa. Co ciekawe wykonanie pakietu bez TABLOCKa powoduje, że dane nie są kompresowane co jest nieco zaskakujące ponieważ wszelkie wzmianki w dokumentacji mówią, że wystarczy polecenie BULK INSERT.

Kilka dodatkowych faktów na temat kompresji na stertach:

• Jeśli zmienimy ustawienia kompresji na stercie to wszystkie indeksy nieklastrowane muszą zostać przebudowane
• Stworzenie indeksu klastrowanego na stercie spowoduje, że indeks ten odziedziczy ustawienia kompresji (chyba, że w zapytaniu bezpośrednio wskażemy inaczej)
• Operacja przebudowy online sterty z kompresją wykorzystuje tylko jeden wątek

Na sam koniec mała ciekawostka, stworzyłem drugą stertę bez ustawionej kompresji po czym włączyłem szacunek zysków z kompresji ROW i taki oto wynik ukazał się moim oczom:

Te same dane bez żadnej kompresji zajmowały szacunkowo 133440KB, a po kompresji ROW 76344KB. Ta liczba nie dawała mi spokoju i wyczyściłem naszą testową tabelę i wstawiłem do niej dane używając INSERT SELECT bez TABLOCK – czyli kompresja PAGE nie powinna zostać wykonana i otrzymałem taki rezultat:

Bardzo zbliżony rozmiar danych: kompresja  ROW:76344KB, wstawianie danych do sterty z kompresją PAGE komendą INSERT SELECT: 76248KB. Wygląda na to, że kompresja wierszy została zaimplementowana na danych wstawianych do sterty z włączoną kompresją PAGE mimo, że nie użyliśmy TABLOCK. Próżno szukać tej informacji w dokumentacji czy też w internecie – jedyne na co się natknąłem to krótka notka z kwietnia 2008 (link) – dosyć stara informacja ale jak widać nadal aktualna (korzystałem z Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)).

To by było na tyle jeśli chodzi o to zagadnienie. Mam nadzieję, że tematyka okazała się dla Was równie ciekawa co dla mnie. Pozdrawiam.