Kompresja stron i wierszy a wstawianie i odczytywanie danych w SQL Server

Kompresja w SQL Server istnieje już bardzo długo pod różnymi mechanizmami i funkcjonalnościami. Pierwsze co nam przychodzi na myśl jak mówimy o tym temacie to kompresja stron i wierszy, w dalszym planie pojawia się kompresja słownikowa dostępną w indeksach kolumnowych oraz kompresja funkcjami TSQL takimi jak COMPRESS. Oczywiście jeśli byśmy głębiej poszukali moglibyśmy dorzucić tutaj jeszcze kilka dodatkowych nazw. W powszechnie panującym przekonaniu mówi się, że kompresja jest czymś dobrym ponieważ zmniejsza miejsce na dysku potrzebne do przechowania danych, a co za tym idzie odczyty z dysku są szybsze, a to one są bardzo często wąskim gardłem naszej bazy danych. Jednakże wszystko ma swoje wady i zalety – nie inaczej jest w przypadku kompresji. Myśląc o tym mechanizmie obok zalet warto pamiętać o tym, że przede wszystkim zwiększa ona zużycie CPU oraz może mieć negatywny wpływ na czas wstawiania danych do tabeli. Ze względu na fakt, iż chcę do tematu podejść czysto praktycznie to przetestuję jaki wpływ ma kompresja stron i wierszy na odczyty i zapisy do tabeli – zapraszam do lektury!

Nasza testowa baza danych będzie miała prealokowane 1GB miejsca i dziennik transakcyjny o wielkości 260MB. Dodatkowo baza będzie w trybie SIMPLE tak abyśmy mogli użyć minimalnie logowanych operacji:

USE master
GO

CREATE DATABASE [CompressionTestDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'CompressionTestDb', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CompressionTestDb.mdf' , 
SIZE = 1048576KB , 
FILEGROWTH = 65536KB )
 LOG ON 
( 
NAME = N'CompressionTestDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CompressionTestDb_log.ldf' , 
SIZE = 262144KB , 
FILEGROWTH = 65536KB )
GO
ALTER DATABASE [CompressionTestDb] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Stworzymy sobie trzy tabelę na podstawie Fact.Sale znajdującej się w WideWorldImportersDW:

USE [CompressionTestDb]
GO
SELECT TOP 0 * INTO dbo.RowCompression FROM WideWorldImportersDW.Fact.Sale
GO
SELECT TOP 0 * INTO dbo.PageCompression FROM WideWorldImportersDW.Fact.Sale
GO
SELECT TOP 0 * INTO dbo.NoCompression FROM WideWorldImportersDW.Fact.Sale

Każda z tabel będzie stertą i w zamierzeniu każdą z nich poddamy innemu stopniu kompresji: od kompresji stron Stron, aż po brak kompresji:

USE [CompressionTestDb]
ALTER TABLE [dbo].[PageCompression] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = PAGE
)
GO
ALTER TABLE [dbo].[RowCompression] REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = ROW
)

Mając gotowe obiekty możemy rozpocząć nasz test, wstawimy dane poleceniem INSERT.. SELECT. Oczywiście przed każdą operacją wyczyścimy buffer pool z danych, które mogą się tam znajdować oraz wyczyścimy cache proceduralny aby uzyskać bardziej wiarygodne rezultaty. Jako wskaźnik wydajności posłuży nam przede wszystkim czas potrzebny na wykonanie konkretnej operacji. Informacyjnie dorzucimy również ilość stron odczytanych stron z dysku. Aby nie mieć wątpliwości co do równoległego przetwarzania każda operacja będzie wykonywana z MAXDOP 1 co potencjalnie wydłuży nam czas przetwarzania ale jednocześnie uprości otrzymany wynik. Tak więc zaczynajmy, na pierwszy ogień wrzucimy tabelę nie poddaną kompresji (dorzuciliśmy wskazówkę TABLOCK aby operacja wstawiania była minimalnie logowana):

INSERT INTO dbo.NoCompression WITH (TABLOCK)
(
	   [Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
)
SELECT 
[Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
 FROM WideWorldImportersDW.Fact.Sale
OPTION(MAXDOP 1)

Otrzymane rezultaty:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 29 ms.
Table 'Sale'. Scan count 1, logical reads 0, physical reads 0, 
read-ahead reads 0, lob logical reads 1770, 
lob physical reads 129, 
lob read-ahead reads 2013.
Table 'Sale'. Segment reads 7, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 1391 ms,  elapsed time = 1904 ms.

Mamy same odczyty LOB ponieważ tabela źródłowa ma założony Clustered Columnstore Index – interpretacją zajmiemy się później. Wykonajmy te same działania dla tabeli z kompresją wierszy oraz dla tabeli z kompresją stron:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SET IDENTITY_INSERT dbo.NoCompression OFF
GO

SET IDENTITY_INSERT dbo.RowCompression ON
GO

INSERT INTO dbo.RowCompression WITH (TABLOCK)
(
	   [Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
)
SELECT 
[Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
 FROM WideWorldImportersDW.Fact.Sale
 OPTION(MAXDOP 1)

Rezultat wstawienie danych z kompresją wierszy:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 34 ms.
Table 'Sale'. Scan count 1, logical reads 0, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 1770, 
lob physical reads 129, 
lob read-ahead reads 2013.
Table 'Sale'. Segment reads 7, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 2125 ms,  elapsed time = 2533 ms.

Test na tabeli z kompresją stron:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SET IDENTITY_INSERT dbo.RowCompression OFF
GO

SET IDENTITY_INSERT dbo.PageCompression ON
GO

INSERT INTO dbo.PageCompression WITH (TABLOCK)
(
	   [Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
)
SELECT 
[Sale Key]
      ,[City Key]
      ,[Customer Key]
      ,[Bill To Customer Key]
      ,[Stock Item Key]
      ,[Invoice Date Key]
      ,[Delivery Date Key]
      ,[Salesperson Key]
      ,[WWI Invoice ID]
      ,[Description]
      ,[Package]
      ,[Quantity]
      ,[Unit Price]
      ,[Tax Rate]
      ,[Total Excluding Tax]
      ,[Tax Amount]
      ,[Profit]
      ,[Total Including Tax]
      ,[Total Dry Items]
      ,[Total Chiller Items]
      ,[Lineage Key]
 FROM WideWorldImportersDW.Fact.Sale
 OPTION(MAXDOP 1)

Rezultat testu wstawiania danych do tabeli z kompresją stron:

SQL Server parse and compile time: 
   CPU time = 16 ms, elapsed time = 18 ms.
Table 'Sale'. Scan count 1, 
logical reads 0, 
physical reads 0, 
read-ahead reads 0, 
lob logical reads 1770, 
lob physical reads 129, 
lob read-ahead reads 2013.
Table 'Sale'. Segment reads 7, segment skipped 0.

 SQL Server Execution Times:
   CPU time = 9016 ms,  elapsed time = 9607 ms.

Przedstawmy teraz wyniki naszego testu. Pierwszą rzeczą na jaką warto zwrócić uwagę jest ilość odczytów w każdym z testów – były one we wszystkich trzech przypadkach dokładnie takie same tj:

• lob logical reads:1770
• lob physical reads: 129
• lob read-ahead reads: 2013

Dzięki tej informacji możemy założyć, że odczyty z tabeli źródłowej nie wpłynęły na nasze testy i za każdym razem mieliśmy takie same warunki. Spójrzmy teraz na czasy wstawienia danych bo chyba ta informacja jest dla nas najbardziej interesująca:

To co otrzymaliśmy raczej nie jest zaskakujące – wstawienie danych do tabeli ze zdefiniowaną kompresją trwa dłużej, im wyższy stopień kompresji tym trwa to dłużej. Większość czasu jaki upływa związany jest z tym ile czasu procesor spędza na kompresowaniu wartości. Tutaj ciekawy może wydawać się pytanie czy wstawienie danych i późniejsza kompresja może zmienić sytuację: sprawdźmy to!  Wstawię dane do kolejnej tabeli, która będzie stertą by zaraz po wstawieniu całość poddać kompresji stron. W tym miejscu aby mieć pewność, że wszystkie operacje nie będą wykonywane równolegle ustawimy konfigurację bazy na MAXDOP 1 (ponieważ nie możemy użyć hinta przy ALTER TABLE):

USE [CompressionTestDb]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 1;
GO

Wynik tej części testu również nie jest dla nas zaskoczeniem ponieważ podejście z wstawianiem danych i późniejszą kompresją zajmuje jeszcze więcej czasu:

W takim razie możemy założyć na podstawie powyższych testów, że kompresja znacznie wydłuża czas potrzebny na wstawienie danych. Warto tutaj również zaznaczyć, że test powtórzyłem kilkukrotnie aby mieć pewność co do otrzymanych rezultatów. Oczywiście w praktyce jedne tabele kompresują się lepiej inne gorzej co również może mieć wpływ na czas wstawienie i odczytania danych jednakże ogólny trend jest zgodny z tym co przedstawiał powyższy test.

Skoro nasze tabele są już załadowane to sprawdźmy jak dużo miejsca zajmują – dowiemy się tego wykonując zapytanie na widokach systemowych, które dostarczą nam odpowiednich danych:

SELECT 
    s.Name+'.'+tbl.Name AS TableName,
    par.rows AS RowCounts,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS DECIMAL(36, 2)) AS TotalSpaceMB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS DECIMAL(36, 2)) AS UsedSpaceMB, 
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS DECIMAL(36, 2)) AS UnusedSpaceMB
FROM sys.tables tbl
INNER JOIN sys.indexes idx 
	ON tbl.OBJECT_ID = idx.object_id
INNER JOIN sys.partitions par 
	ON idx.object_id = par.OBJECT_ID AND idx.index_id = par.index_id
INNER JOIN sys.allocation_units a 
	ON par.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s 
	ON tbl.schema_id = s.schema_id
GROUP BY 
    tbl.Name, s.Name, par.Rows
ORDER BY 
    tbl.Name

Jak możecie zauważyć różnica w rozmiarze pomiędzy tabelą bez kompresji, a tabelą z kompresją wierszy jest dwukrotna, a w porównaniu z kompresją stron aż czterokrotna! Rozmiar danych w tabeli(partycji) w konkretnej konfiguracji możemy sprawdzić używając procedury sp_estimate_data_compression_savings:

sp_estimate_data_compression_savings 
	'dbo', --nazwa schematu
	'RowCompression', --nazwa tabeli
	NULL, --indeks
	NULL, --partycja
	'PAGE' --typ kompresji

Dzięki tej procedurze możemy określić jak bardzo efektywny będzie dany mechanizm kompresji. Oczywiście pamiętajmy, że otrzymany rezultat bazuje tylko na próbce danych i nie będzie w 100% dokładny ale mimo wszystko warto zawsze sprawdzić potencjalne zyski.

Przejdźmy dalej i sprawdźmy jaki wpływ mają użyte algorytmy kompresji na wydajność zapytań – pierwszym zapytaniem jest prosta suma na wszystkich wierszach:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SELECT sum(Quantity) AS SumOfQty
FROM <<nazwa tabeli>>
GO

W rezultacie otrzymaliśmy następujące wyniki:

Kompresja stron była znacznie szybsza od konkurentów (chociaż kompresja wierszy nie jest konkurentem bo jak wiadomo kompresja stron zawiera w sobie kompresję wierszy)- ciekawe jest to, że kompresja wierszy nie dała jakiegoś dużego skoku wydajnościowego. Spróbujmy przetestować nasze tabele w jeszcze jednym trochę bardziej rozbudowanym zapytaniu:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
select [City Key],SUM(Quantity) AS SumOfQty
FROM <<table name>>
where [Invoice Date Key] BETWEEN '20131001' AND '20131030'
group by [City Key]
Order by [City Key]

Rezultat testu:

Rezultaty są już bardziej klarowne i widać wzrost wydajności przy odczytywaniu tabel skompresowanych w porównaniu do tabeli nie poddanej kompresji.

Oczywiście można kontynuować testy na wielu różnych zapytaniach ale ogólnie rzecz biorąc należy wysnuć wnioski, że kompresja ma pozytywny wpływ na odczyty natomiast wydłuża czas wstawiania danych. Co prawda w naszym teście wstawialiśmy jedną operację ponad 200 tysięcy jednakże podobne rezultaty otrzymamy wstawiając mniej wierszy wieloma odrębnymi sesjami. Jeżeli znacie swój workload to możecie sami sobie odpowiedzieć na pytanie czy kompresja będzie dla Was dobra czy też nie. W systemach OLTP gdzie operacje wstawiania danych nie są dominujące i kładziemy nacisk na odczyty to kompresja może dać nam wiele o ile czas potrzebny na dekompresje nie trwa zbyt długo – wszystko musimy testować. Pamiętajmy jednak o tym, że jeżeli mamy problemy z przeciążonym procesorem i dorzucimy mu jeszcze kompresję to możemy wpaść w poważne problemy z wydajnością całej bazy. W przypadku hurtowni danych obecnie prawie zawsze lepszym wyborem dla tabel faktów są indeksy kolumnowe, które posiadają swoje własne mechanizmy kompresji. Warto jednak rozważyć kompresję tabel wymiarów, szczególnie tych większych. Kwestią otwartą pozostaje kompresja tabel z obszaru przejściowego potrzebnych w czasie zasilania hurtowni – tam musimy wyważyć czy zysk przy odczytach równoważy stratę przy zapisie. Mam nadzieję, że ten wpis będzie dla Was przydatny i pokaże wpływ tradycyjnych metod kompresji na operacje wstawiania i odczytu danych.