MSSQL Server’da COMPRESS Index

SQL Server’da COMPRESS index terimi aslında page compression (sayfa sıkıştırma) özelliğini ifade eder. Bu, verileri depolarken disk alanından tasarruf etmek için kullanılan bir veri sıkıştırma yöntemidir.

Her sayfadaki her sütun için tekrarlanan ön ekleri (prefix) belirler. Bu ön ekleri bir sözlükte saklar ve yinelenen değerleri referanslarla değiştirir.

Sayfa genelinde tekrarlanan değerleri bir sözlükte depolar. Yinelenen değerleri sözlük referanslarıyla değiştirir.

Adı: Yunus YÜCEL, Şehir: Elazığ, Plaka: 23
Adı: Yunus YÜCEL, Şehir: Elazığ, Plaka: 23
Adı: Yunus YÜCEL, Şehir: Elazığ, Plaka: 23
Adı: Kadir YÜCEL, Şehir: Elazığ, Plaka: 23

Yukarıdaki ifadeler tablomuzda birden fazla dönebilir. İlgili tablo compress yapılırsa yapı aşağıdaki şekle dönüşmektedir. Burada aynı olan bölümü bir işaretleyici koyar. Sıkıştırılmış hali 1,1,1,2 şeklindedir. Referanslama şeklinde olmaktadır. Başka bir değişle kısayollar oluşturmaktadır.

[Yunus YÜCEL], Elazığ, 23→ [1]
[Kadir YÜCEL], Elazığ, 23→ [2]
[1], [1], [1], [2]  (3 kat daha az yer!)

Disk alanından %50-70’e varan alan tasarrufu sağlayabilir. Daha az disk I/O işlemi yapar. Daha fazla veriyi buffer pool’da saklayabilir. Bu avantajlarını yanından dezavantajlarıda bulunmaktadır. Verileri sıkıştırılması ve tekrardan çözülmesi için yoğun şekilde CPU harcanmasına sebebiyet verecektir. INSER\UPDATE\DELETE işlemlerinde performans düşüşüne sebep olacaktır. Rebuild ve reorganize işlemleri daha uzun sürmektedir.

Aşağıdaki komut ile sıkıştırılmış indexleri görmekteyiz.

SELECT 
    SCHEMA_NAME(t.schema_id) as SemaAdı,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    p.data_compression_desc AS CompressionType
FROM sys.partitions p
INNER JOIN sys.tables t ON p.object_id = t.object_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.data_compression > 0
ORDER BY t.name, i.name;

Aşağıdaki komut ile sıkıştırma öncesi ve sonrası tablo boyutunu görebiliriz.

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo',
    @object_name = 'LinkTreesLatest',
    @index_id = NULL,
    @partition_number = NULL,
    @data_compression = 'PAGE';

Tüm instance altındaki veritabanı üzerinde sorguyu çalıştırmak istiyorsak aşağıdaki komut kullanılmaktadır.

EXEC sp_MSforeachdb '
USE [?] 
SELECT 
  ''?'' as VeritabanıAdı,
    SCHEMA_NAME(t.schema_id) as SemaAdı,
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    p.data_compression_desc AS CompressionType
FROM sys.partitions p
INNER JOIN sys.tables t ON p.object_id = t.object_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.data_compression > 0
ORDER BY t.name, i.name;
'

Bu yapının ne zaman kullanılabilir. Büyük, read-intensive (okuma ağırlıklı) tablolarda kullanılabilir. Disk alanı kısıtlı ortamlarda kullanılabilir. Arşiv verileri oluşturmak için kullanılmaktadır. Raporlama işlemlerinde kullanılabilir.

Bu yapıdan kaçınılması gereken ortamlar: Büyük oranda yazma işlemleri olan OLTP ortamlarında kullanılmaması gerekmektedir. Cpu zaten yoğun kullanılıyorsa ekstradan bu yapının açılması çalışan sistemi daha da yavaşlatmaktadır. Küçük tablo ve index yapılarında kullanılması gereksizdir.

Bu şekilde page seviyesinde compression yapısı üretmektense aşağıdaki yapılar kullanılmaktadır. Kullanılan yapılar sayfamızın arama kısmındanda bulacağınız column store index yapısı oluşturmaktır. Bu işlem page bazında değil kolon seviyesindedir. Bu yapını dışında ikinci yapılacak olay row seviyesinde compression yapmaktadır. Aşağıdaki yapılar ile hem index oluşturup hem de compress yapıda oluşturabiliriz.

CREATE INDEX [IndexName] ON [dbo].[TableName] (Column1, Column2)
WITH (DATA_COMPRESSION = PAGE);

--Önceden oluşturulan index comppress yapıya geçirmek için komut kullanılır.

ALTER INDEX [IndexName] ON [dbo].[TableName] 
REBUILD WITH (DATA_COMPRESSION = PAGE);

------------------------

CREATE INDEX [IndexName] ON [dbo].[TableName] (Column1, Column2)
WITH (DATA_COMPRESSION = ROW);

--Önceden oluşturulan index comppress yapıya geçirmek için komut kullanılır.

ALTER INDEX [IndexName] ON [dbo].[TableName] 
REBUILD WITH (DATA_COMPRESSION = ROW);

-----------------------
--column store index oluşturmak için kullanılan komut:

CREATE COLUMNSTORE INDEX IndexName ON TableName (Column1, Column2);

ALTER INDEX [IndexName] ON [dbo].[TableName] 
REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE);

Yukarıda vermiş olduğumuz komutlarla AdventureWorks veritabanında oluşturabildiğini görmekteyiz.

Aşağıdaki komut page veya row seviyesinde olan sıkıştırmalar gerçekleştirilebilir. Column store index yapılarında aşağıdaki gibi bir değişiklik yapılabilir.

ALTER INDEX [IndexName1] ON [Person].[Address]
REBUILD WITH (DATA_COMPRESSION = ROW);

Eğer yukarıdaki yapıları oluşturduktan sonra sisteminizde CPU yükü arttıysa aşağıdaki komutlar devre dışı bırakılabilir.

-- Compression'ı geçici devre dışı bırakma
ALTER INDEX [IndexName] ON [dbo].[TableName] 
REBUILD WITH (DATA_COMPRESSION = NONE);

Yukarıdaki komut ile kapatıldıktan sonra tablomuzun fragmentation oranı yüksek seviyede artacağı için aşağıdaki komut ile index yeniden oluşturulabilir. Fragmentation küçük oranda artmışsa istatistikler güncellenebilir.

-- Index'i yeniden oluşturma
ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD;

-- Index istatistiklerini güncelleme
UPDATE STATISTICS [dbo].[TableName] [IndexName] WITH FULLSCAN;

Page compression, doğru kullanıldığında önemli avantajlar sağlayan güçlü bir özelliktir. Ancak her senaryo için uygun değildir. Karar vermeden önce mutlaka sistemin çalışma şekline verilerin değişip değişmediğine bakılması gerekmektedir.

Columnstore index ile compress index tamamen birbirinden farklıdır. Columnstore index performans ve optimizaston için kullanılırken. Compress index disk alanı tasarrufu için kullanılmaktadır. Columnstore index yapısında sütün bazlı oluşturulurken compress yapısında satır bazlı sıkıştırma işlemi yapılmaktadır. Columnstore da cpu maliyeti düşükken compress index de yüksektir. Colum store index raporlama ve aggregation sorgularında kullanılırken. Compress index yapısında disk alanı tasarrufu, backup restore sürelerini azaltmaktadır. Neden cpu anlamında bizlere fayda sağlar columnstore index sutün bazlı olduğu ilgisiz veri segmentleri okunmaz sadece ihtiyaç duyulan sütünlar okunmaktadır. 1000’er satırlık batchler halinde işlem yapmaktadır. Compress index yapısında her okuma yazmada sıkıştırılan veri açılır. Satır bazlı işlem yapıldığı için gereksiz olan kolanlarda okunmuş olmaktadır. Sıkıştırma ve sıkıştırılan veriyi açmak için memory üzerinde ekstradan bir yük oluşturmaktadır. Columnstore index yapısında cpu ne kadar düşer memory kullanımı segmentlerden dolayı artmaktadır. Compress yapıda ise CPU yüksek ama disk ve memory memory anlamında avantaj vardır.

İşlem	COLUMNSTORE CPU	COMPRESSION CPU
Data Read	Çok Düşük	Çok Yüksek
Data Write	Orta	Çok Yüksek
Aggregation	Çok Düşük	Çok Yüksek
Scan Operation	Çok Düşük	Çok Yüksek
Point Lookup	Çok Yüksek	Orta

Başka makalede görüşmek dileğiyle..

Kişinin, Müslüman kardeşini küçük görmesi kötülük olarak kendisine yeter.(Hadis)