Kompresja bazy danych to rzecz jasna żadna nowość w środowisku bazodanowym Microsoftu - już wersja 2008 w pełni nam ją udostępniła. Czasami, przy większych bazach danych, faktycznie okazywała się być użyteczna: zapewniała oszczędność miejsca i lepsze wykorzystanie pamięci, kosztem trochę wiekszego obciążenia procesora (jeżeli włączymy kompresję stron), ale powiem szczerze, że zbyt często z niej nie korzystałem. Zasadniczą wadą kompresji jest wymóg posiadania edycji Enterprise SQL Servera, która jak wiadomo do najtańszych nie należy. Przejdźmy jednak do meritum…. ostatnio miałem okazję uczestniczyć w migracji serwera bazy danych, na którym działał system SAP i postanowiłem wspomnieć w kilku słowach właśnie o kompresji, którą zdecydowaliśmy się włączyć (wcześniej system działał na SQL Server 2005). Wszystkie znaki na niebie i ziemi wskazywały, że to jest właśnie rodzaj bazy danych, przy której ta funkcjonalność powinnna pokazać pazur. Wszystkie dokumenty SAP, które dotykały tego tematu zachwalały korzyści kompresji, a co więcej od jakiegoś czasu wszystkie nowe instalacje SAP domyślnie ją wykorzystują i to w wersji najsilniejszej, czyli Page. Jeżeli ktoś miał już wcześniej do czynienia z bazą SAP i trochę się jej przyglądał to wie z pewnością, że są tam wykorzystywane bodajże tylko 3 lub 4 typy danych, a większość kolumn w tabelach to typ danych nchar o stałej długości, do tego bardzo dużo danych się powtarza, a wiele kolumn pozostaje pustych (ale nadal zajmują miejsce) itd.
Oczywiście jeżeli chcemy dokładniej określić oszczędność miejsca na dysku z pomocą może nam przyjść procedura sp_estimate_data_compression_savings (ocenia oszczędności na pojedynczym obiekcie w bazie danych), albo jeszcze lepiej skrypt opublikowany przez Paula Nielsena (działa na całej bazie danych). W każdym razie jak się okazało, przeczucia i dokumentacja nas nie zawiodły. Rozmiar bazy po kompresji wierszy spadł o połowę, a po kompresji stron ponownie skurczył się o 50%. Znacznie wzrosła też wydajność całego środowiska (więcej stron można upchać w pamięci), np. programy kontrolingowe przyspieszyły ponad dwukrotnie – to co robiło się przez 2.5h, teraz robi się przez godzinę. Automatycznie spadł także rozmiar kopii zapasowej, co pozwola oszczędzać czas w ramach okna backupowego oraz miejsce na macierzy, a w przypadku konieczności odtworzenia bazy danych nastąpi to dużo szybciej. Generalnie same plusy . Poniżej szczegółowe dane:
Compression level | Database size | Backup without compression | Backup with compression |
No compression | 420 GB | 420 GB | 58 GB |
Row | 196 GB | 196 GB | 38 GB |
Page | 93 GB | 93 GB | 33 GB |
Włączenie kompresji wierszy ma właściwie tylko zbawienne skutki, gdyź nie wiąże się z dodatkową utylizacją procesora; kompresja typu page już trochę z tego procesora musi korzystać ale z tego co zdążyłem zauważyć wzrost jest stosunkowo niewielki.
Poniżej wykres prezentujący rozmiar bazy danych. Chyba nie muszę nikomu tłumaczyć w którym momencie została włączona kompresja wierszy, i w którym dodatkowo kompresja stron
Pamiętajcie jeszcze o jednym – backup skompresowanej bazy jest możliwy do odzyskania tylko w edycji Enterprise SQL Servera.
Na koniec, gdybyście chcieli sprawdzić jakie tabele są skompresowane, zamieszczam poniżej krótkie zapytanie, które może być w takiej sytuacji pomocne.
SELECT SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] ,[rows] ,[data_compression_desc] ,[index_id] as [IndexID_on_Table] FROM sys.partitions INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id WHERE data_compression > 0 AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' ORDER BY SchemaName, ObjectName
Ten post dostępny jest także w języku: angielski