Komprese tabulek a indexů je funkcionalita, která funguje v rámci SQL Server edic již delší dobu. Dostupná ve všech edicích včetně SQL server Express (zdarma) je od verze SQL Server 2016. Data compression umožňuje ušetřit poměrně výrazně storage – prostor na disku. To samozřejmě šetří podnikové zdroje – náklady na HW, služby jako backup a podobně. Vše má své pro a proti stejně jako komprese tabulek a indexů.

Kam je kompresi SQL tabulek vhodné nasadit?

V případě kompresovaných objektů je nevýhodou o něco větší nárok na CPU. Pokud přistupujeme formou dotazu nebo třeba nějaké DML operace do kompresovaného objektu, tak objekt musí být nejprve dekompresován, poté je výkonána požadovaná operace a nakonec je objekt znovu zkompresován. Z tohoto titulu je potřeba si rozmyslet do jakého prostředí se komprese hodí nebo nehodí.

OLTP systémy – komprese spíš NE

Tato funkce je dle mého názoru vysoce nevhodná pro OLTP systémy (transakční), ve kterých je vysoká frekvence přistupování do tabulek formou insertů/updatů a je potřeba, aby byly oprace provedeny bleskově. Takovým prostředím je třeba nějaká podniková CRM nebo webová aplikace postavená na .NET a podobně. V tomto případě objem dat je relativně malý a úspory podle mého názoru reálně nevzniknou – ušetříte sice třeba polovinu storage (což je v absolutním vyjádření třeba pár GB), ale za cenu určitého zpomalení celého systému. Pokud toto vše položíme na misky vah, tak převáží negativa.

Určitou výjimkou může být situace, kdy pro svou aplikaci používáte edici SQL Express, která má omezení na 10 GB storage a stojíte před rozhodnutím buďto koupit SQL Standard nebo nasadit kompresi tabulek a o něco snížit tak komfort uživatelů při práci. V tomto případě bych asi kompresi nasadil a otestoval jestli se dá s aplikací stále relativně pohodlně pracovat. V tomto scénáři úspory vzniknout mohou. Pořízení SQL Server Standard stojí cca 100 000 Kč (záleží na více faktorech a může to být více nebo méně), které ušetříte. Ocenit snížení výkonu aplikace si musíte sami odhadem.

Datové sklady, datamarty – komprese ANO

Naopak extrémně vhodné využití je pro datové sklady a datamarty, které se napočítávájí v určitých intervalech (typicky 1x denně v noci) a poté jsou nad objekty prováděny pouze queries (dotazy).U větších datových skladů jsou denně ukládány desítky/stovky GB dat a možnost úspory desítek procent storage je už v absolutním měřítku zajímavá. Úspora prostoru na disku/discích, kde je umístěn SQL Server je jen část benefitu. Pak tu máme samozřejmě zálohování, která bude trvat menší dobu atd. Nevýhodou může být, že pravděpodobně dojde k prodloužení nápočtu dat u Slowly changing dimenzí – insert/update operace budou trvat déle.

Typy komprese tabulek a indexů – Row level a page level komprese

V zásadě existují 2 způsoby jak datovou komprimaci provést – Row level a page level.

Row level komprese dat

Přináší menší efekt komprese, ale spotřebovává také méně CPU. Snižuje množství metadat, která jsou spojena s danými záznamy. Tento způsob má vliv na číselné datové typy, datumy a binární data. Na textové atributy tento způsob komprimace tabulek efekt nemá. Prázdné hodnoty a řetězce prázdných znaků nejsou ukládány. Nad danými datovými typy je ukládáno pouze bezbytné množství bajtů. Jestliže máme definován sloupec typu BIGINT, tak každé číslo u nezkompriované tabulky má velikost 8 bajtů. U zkomprimované tabulky může být toto číslo nižší v případě, že číslo lze uložit levněji (např jako INT 4 Bytes).

Page level komprese dat

Zjednodušeně se komprimují také textové řetězce a je aplikována logika metadat, kdy jsou podobná data nbeo jejich části nahrazovány referencemi a ukládají se pouze fragmenty dat a tyto reference. Tento způsob přínáší větší úspory, ale také vyšší vytěžování CPU. Nad objektem, kde provedeme page kompresi je současně provedena také row komprese. Nad záznamy jsou provedeny poměrně komplikované operace – podrobněji viz. dokumentace Microsoft, která mají za následek to, že je možné fyzicky uložit mnohem méně dat a generovat úspory z komprese tabulek v databázi.

Jak odhadnout úspory z komprese?

SQL Server má nativně nainstalovanou systémovou proceduru sp_estimate_data_compression_savings, která provede analýzu objektu a vrátí odhad storage savings.

Po nasazení komprese se nezmění velikost datového souboru databáze. Ten zůstane pořád stejný. Zvýší se však prostor, na který nejsou alokována data. Pokud bysme chtěli snížit velikost datového souboru o prostor, na který není nic alokováno ,tak bychom museli provést shrink databáze – s tímto opatrně, je to poměrně časově náročná operace a u databází o velkosti desítek GB trvá shrink několik hodin.

Mějme tabulku se zákazníky  z volně dostupné sample databáze Microsoft (AdventureworksDW2016CTP3). Tabulka se jmenuje DimCustomer a obsahuje spoustu textových řetězců, čísel a prýzdných hodnot. Tabulka nemá žádný index a je tedy uložena v neuspořádané struktuře heap = hromada. Ideální objekt na testování.

Jaké budou očekávané úspory z row-level a page-level komprese? To lze zjistit spuštěním zmiňované procedury do které zadáme schéma, objekt a typ komprese. V našem případě pustíme proceduru dvakrát – pro ROW a PAGE.

USE AdventureworksDW2016CTP3;
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'DimCustomer', NULL, NULL, 'ROW' ;
GO

USE AdventureworksDW2016CTP3;
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'DimCustomer', NULL, NULL, 'PAGE' ;
GO

Výsledky testu úspor z komprese tabulek

• Row-level komprese (červeně označeno) – očekávaná úspora storage je cca 44 % (7840-4408)/7840. V tabulce se nachází spousta čísel, datumů a prázdných hodnot, které by mohly být ukládány efektivněji.
• Page-level komprese (modře označeno) – očekávaná úspora je v tomto případě téměř 70 % storage. V tomto případě bychom těžili z row-level komprese + efektivnějšího uložení string řetězců, kterých je v tabulce spousta.

Jak zkompresovat tabulku nebo index?

1) Pokud se rozhodneme, že chceme kompresi nad nějakou existující tabulku nasadit třeba ROW-LEVEL kompresi, tak to můžeme provést následovně

ALTER TABLE [dbo].[DimCustomer]
REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=ROW);

2) Pro vytvoření prázdné tabulky kompresované přes řádky

CREATE TABLE [Test] (
[cislo] INT not null
)
WITH (DATA_COMPRESSION = ROW )

Jak dekompresovat tabulku?

Stejně jako jsme v předchozím bodu u stávající tabulky udělali kompresi, tak můžeme provést naopak dekompresi (DATA_COMPRESSION nastavíme na NONE)

ALTER TABLE [dbo].[DimCustomer]
REBUILD PARTITION=ALL WITH (DATA_COMPRESSION=NONE);