Nézzünk konkrét példákat, mekkora nyereséget érhetünk el a tömörítéssel (elmélet ez előző részben).
Nézzük meg egy tábla méretét, majd lássuk, mi lesz belőle row és page compression után (legalábbis mit jósolnak)?
exec sp_spaceused 'Production.TransactionHistoryArchive' exec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'row' exec sp_estimate_data_compression_savings 'Production', 'TransactionHistoryArchive', NULL, NULL, 'page'
name rows reserved data index_size unused ------------------------------ ----------- ------------------ ------------------ ------------------ ------------------ TransactionHistoryArchive 89253 8224 KB 4992 KB 2776 KB 456 KB object_name schema_name index_id partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com ------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ TransactionHistoryArchive Production 1 1 5136 3240 5008 3160 TransactionHistoryArchive Production 2 1 1144 968 1024 872 TransactionHistoryArchive Production 3 1 1488 1240 1728 1448 object_name schema_name index_id partition_number size_with_current_compression_ size_with_requested_compressio sample_size_with_current_compr sample_size_with_requested_com ------------------------------ ------------------------------ ----------- ---------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ TransactionHistoryArchive Production 1 1 5136 1680 5008 1640 TransactionHistoryArchive Production 2 1 1144 816 1024 736 TransactionHistoryArchive Production 3 1 1488 1144 1728 1336
Mit látunk? Az alapban 5.1 megás adatból és rajta levő 2.7 megányi indexből row compressionnel 3.2 és 2.2 mega lesz. Az adat kb. a felére megy össze, ami nem rossz, hisz row compressionről van szó, ami nagyon gyors. Az indexet nem tudta úgy összenyomni, valószínűleg az indexekben levő int adatok jelentős része 2 byte hosszan tárolható csak el, így csak felére nyomható össze.
Lap szintű tömörítésnél az 5.1 megás adatok csak 1.7 megát foglalnak el, azaz 3x tömörítést kapunk. Az indexek mérete 2.7-ről 1.95 megára esik vissza, nem sokkal kisebbre, mint csak sor tömörítéssel. Szóval indexnél esetünkben nem sokat ért egyik módszer sem, az adatok jellege miatt. Én lehet, hogy indexnél sor, adatnál pedig lap szintű tömörítést használnék.
Az adatok tényleges tömörítése lap szinten:
alter table Production.TransactionHistoryArchive rebuild with (data_compression = page);
Nézzük meg, mit nyerünk? Tudni kell, attól, hogy bekapcsoljuk valamely tömörítést, még dönthet úgy a szerver, hogy bizonyos lapokat nem tömörít, mert esetleg nagyobb lenne az eredmény, mint a forrás (a zip, rar, stb. programok is így tesznek). Nézzük meg a példánkban mi a helyzet?
select
index_id,
index_level,
index_type_desc,
page_count,
compressed_page_count,
(select top 1 name from sys.indexes si where si.object_id = s.object_id and si.index_id = s.index_id) IndexName
from
sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), object_id('Production.TransactionHistoryArchive'), NULL, NULL, 'DETAILED') s
order by s.index_id, s.index_level desc
index_id index_level index_type_desc page_count compressed_page_count IndexName ----------- ----------- -------------------------------------------------- -------------------- --------------------- -------------------------------------------------- 1 1 CLUSTERED INDEX 1 0 PK_TransactionHistoryArchive_TransactionID 1 0 CLUSTERED INDEX 203 203 PK_TransactionHistoryArchive_TransactionID 2 1 NONCLUSTERED INDEX 1 0 IX_TransactionHistoryArchive_ProductID 2 0 NONCLUSTERED INDEX 125 0 IX_TransactionHistoryArchive_ProductID 3 1 NONCLUSTERED INDEX 1 0 IX_TransactionHistoryArchive_ReferenceOrderID_Refe 3 0 NONCLUSTERED INDEX 168 0 IX_TransactionHistoryArchive_ReferenceOrderID_Refe
Látható, hogy a clustered index 1. szintje, az index gyökér lapja nem page tömörített (korábban írtam ,miért), de a 203 levélszintű lap, azaz az adatlapok mind tömörítettek.
Látható az is, hogy az indexek egyáltalán nincsenek tömörítve, az alter table clu index esetén csak arra vonatkozik, az nc indexekre nem. Ha azokat is tömöríteni akarjuk, alter index kell:
alter index IX_TransactionHistoryArchive_ProductID on Production.TransactionHistoryArchive rebuild with (data_compression = page);
Ennek hatására a korábbi lekérdezés kimenete:
2 1 NONCLUSTERED INDEX 1 0 IX_TransactionHistoryArchive_ProductID 2 0 NONCLUSTERED INDEX 90 89 IX_TransactionHistoryArchive_ProductID
Szinte minden indexlap page compressed lett, csak 1 maradt row (a gyökéren kívül).
Hasonlítsuk össze a teljes tábla kiolvasásának IO költségét a page tömörítéssel és simán:
set statistics io on select * from Production.TransactionHistoryArchive alter table Production.TransactionHistoryArchive rebuild with (data_compression = none); select * from Production.TransactionHistoryArchive set statistics io off
(89253 row(s) affected) Table 'TransactionHistoryArchive'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (89253 row(s) affected) Table 'TransactionHistoryArchive'. Scan count 1, logical reads 622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Harmadára esik vissza az IO, a háromszoros tömörítés miatt.
A módosítások költségére gyakorolt káros hatást HF-ként feladom a kedves olvasóknak. :)
Összegezve, a tömörítés egy igen kellemes szolgáltatás, amely hatalmas táblák esetén jelentősen csökkentheti az IO költséget, így ha az a szűk keresztmetszet, akkor nem csak sprórol a vinyóval, de még gyorsít is.
Hangsúlyozom viszont, hogy OLTP jellegű tábláknál sokat ronthat, pont ezért szándékosan nincs olyan sp a szerverben, ami minden táblára és indexre bekapcsolná a tömörítést, mert sok ember majom módjára zipelne. :) Sajnos persze már van fenn az interneten ilyesmi sp, nem is linkelem be.
És végül egy kellemetlen hír: a fícsör csak az enterprise verzióban lesz benne. Valahogy motiválni kellett az eladásokat, ez eléggé erős érv a űbergalaktikus verzió mellett.
Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.