SQL Server 2016 – COMPRESS DECOMPRESS

Kontynuujemy naszą serię w której poznajemy najnowszą odsłonę silnika bazodanowego SQL Server 2016. Tym razem przyjrzymy się nowym funkcjom kompresującym i dekompresującym – zapraszam!

Już od dawna SQL Server był wyposażony w mechanizmy kompresujące – na poziomie tabeli czy też indeksu mogliśmy zdecydować o tym czy dany obiekt miał zostać “upakowany” tak aby zajmował mniej miejsca. Do wyboru mieliśmy kompresję PAGE oraz ROW. Tak skompresowane wiersze nie tylko fizycznie zajmowały mniej miejsca, ale również zmniejszały wąskie gardło przy ich pobieraniu jakim bez wątpienia był dysk (I/O). Obok tych tradycyjnych mechanizmów w roku 2012 pojawił się nowy mechanizm niosący nowe rodzaje wydajnej kompresji – chodzi mianowicie o indeks kolumnowy. Kompresja składowania kolumnowego pozwalała zaoszczędzić nawet do 90% przestrzeni dyskowej. Co jednak gdy nie chcemy kompresować całej tabeli, a jedynie jej wyznaczone kolumny? Co jeżeli nie mamy wersji Enterprise serwera bazodanowego, która jako jedyna ma wbudowane wcześniej mechanizmy kompresji? Przed erą SQL Server 2016 musieliśmy użyć odpowiednich bibliotek w językach programowania takich jak np. C# i kompresować określoną wartość i wstawiać ją do tabeli. W SQL Server 2016 nie musimy już używać zewnętrznych mechanizmów aby tego dokonać – wystarczy, że użyjemy funkcji COMPRESS oraz DECOMPRESS. COMPRESS tak jak nazwa wskazuje kompresuje dany ciąg znaków używając algorytmu GZIP. DECOMPRESS z kolei taki ciąg znaków bezstratnie dekompresuje. Nie wchodząc w sam algorytm kompresji przyjrzyjmy się jak nasze funkcje działają. Na samym początku skompresujmy testowy ciągu znakowy:

SELECT COMPRESS('aaaa') as String

Jak można zauważyć implementacja kompresji danych jest bardzo prosta – w tym miejscu może pojawiać się pytanie jaki typ danych został zwrócony? Odpowiedź jest dosyć łatwa do przewidzenia – omawiane funkcje zwracają typ varbinary(max).

Skompresować możemy wszystkie wspierane typy tekstowe i binarne tj.:

• nvarchar(n),
• nvarchar(max),
• varchar(n),
• varchar(max),
• varbinary(n),
• varbinary(max),
• char(n),
• nchar(n),
• binary(n)

Sprawdźmy jak nasze funkcje sprawdzają się w akcji – najpierw stwórzmy przykładową bazę danych oraz dwie tabele, które posłużą nam w przykładzie.

CREATE DATABASE GZIP
GO

CREATE TABLE dbo.NoGzipCompressed
(
NoGzipColumn varbinary(max)
)
GO

CREATE TABLE dbo.GzipCompressed
(
GzipColumn varbinary(max)
)
GO

Następnie wstawmy do obu tabel dane – po 10 tysięcy tych samych wierszy – za pierwszym razem skompresowanych potem na ich podstawie 10 tysięcy wierszy nieskompresowanych.

INSERT INTO GzipCompressed
VALUES
(COMPRESS(CAST(REPLICATE(NEWID(),600000000) AS VARBINARY(MAX))))
GO 10000

INSERT INTO NoGzipCompressed
SELECT
	DECOMPRESS(GzipColumn)
FROM
	GzipCompressed
GO

Teraz wystarczy, że porównamy rozmiar obu tabel – na potrzeby testów użyjemy wbudowanej procedury sp_spaceused:

sp_spaceused 'dbo.GzipCompressed'
GO
sp_spaceused 'dbo.NoGzipCompressed'
GO

Jak widać na powyższym zrzucie ekranowym dane zostały bardzo mocno skompresowane z około 80MB do 1MB! Ogromna oszczędność miejsca jednakże wiąże się to z bardzo dużym użyciem procesora w momencie dekodowania wartości – na poniższym zrzucie możecie zauważyć wskaźnik monitora wydajności (perfmon) podczas dekompresji naszych 10 tysięcy wierszy na mojej lokalnej instancji SQL Server 2016. Przy okazji dodam, że na serwerze nie było żadnej aktywności oprócz wspomnianej dekompresji.

Mimo wszystko kompresja danych binarny i długich tekstów daje bardzo dużo benefitów jak chociażby oszczędność miejsca. Jedynym ograniczeniem na jakie możemy natrafić  jest brak możliwości stworzenia indeksu na takiej kolumnie – jednakże jest to ograniczenie całkowicie do zaakceptowania.

Sprawdźmy jak wygląda sprawa z odczytami z dysku – uruchamiając testowe zapytania

DBCC DROPCLEANBUFFERS
GO

select * from GzipCompressed
GO
select * from NoGzipCompressed
GO

Table 'GzipCompressed'. Scan count 1, logical reads 142, physical reads 1, read-ahead reads 140, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'NoGzipCompressed'. Scan count 1, logical reads 10000, physical reads 0, read-ahead reads 5704, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Ilość stron na których przechowywane są dane zmniejszyła się drastycznie. Jak to się ma w przypadku czasów dostępu do właściwych danych:

select DECOMPRESS(GzipColumn) from GzipCompressed
GO
select * from NoGzipCompressed
GO

(10000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 1406 ms,  elapsed time = 2007 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(10000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 109 ms,  elapsed time = 691 ms.

W tym konkretnym przypadku dane nieskompresowane zostały pobrane i wyświetlone szybciej jednakże w bardzo wielu przypadkach będzie dokładnie odwrotne – wszystko zależy od dostępnych zasobów sprzętowych, samych danych i obciążenia na serwerze.

Od najnowszej wersji SQL Server kompresja danych algorytmem gzip jest jeszcze prostsza.Mnie szczególnie ta opcja cieszy gdyż dzięki niej będzie możliwa kompresja zdjęć z którymi mam do czynienia w ramach słowników pracowników, które zajmują dosyć znaczną ilość miejsca w bazie danych. Mam nadzieję, że również wy znajdziecie rozwiązanie swoich problemów z wykorzystaniem omawianych funkcji.