MSSQL Server Column Store Index

Bu makalede Mssql Server Column Store Index yapısını görmüş olacağız. Şimdiye kadar açıkladığımız index türlerinde index’ler satır bazlı tutulur. Bu index türünde ise kolon bazlı indexleme yapılır. Diğer index’lere göre depolama maliyeti düşüktür. Genelde verinin daha az yazılıp daha çok okunduğu sistemlerde ve karmaşık filtreleme ve gruplama işlemlerinin yapıldığı veri ambarı gibi büyük veri içeren uygulamalarda kullanılır. Nonclustered index’e göre 15 kata varan daha fazla sıkıştırma yapar ve sorgularda 10 kata kadar performans artışı sağlar. ColumnStore Index SQL Server 2012 ile birlikte hayatımıza girmiştir. Kolonlar sıkıştırılmış olarak tutulurlar ve bu şekilde de okunan veri miktarı azalır ve performans artışı sağlanır.

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

Columnstore Index, SQL Server’da büyük miktardaki veriler üzerinde analitik sorguların performansını artırmak için kullanılan bir dizin türüdür. OLAP (Online Analytical Processing) senaryoları ve veri ambarlarında (data warehouse) sıkça kullanılır. Diğer dizinlerden farklı olarak, verileri satır bazında değil, sütun bazında depolar. Sadece ihtiyaç duyulan kolonlar okunmaktadır.

Columnstore Index Sıkıştırma Mekanizması:

Value-Based Compression:  Benzer değerleri gruplayarak sıkıştırma işlemi yapılmaktadır.

Örnek: [10, 10, 10, 20, 20] → [10(3), 20(2)]

Dictionary Compression: Benzersiz değerler sözlüğü oluşturmaktadır.

Run-Length Encoding (RLE) Compression: Ardışık aynı değerleri sıkıştırma işlemi yapılmaktadır.

Örnek: [A, A, A, B, B, C, C, C, C] → [A:3, B:2, C:4]

Columnstore Index Nedir ve Avantajları Nelerdir?

Veriyi sütun bazında sıkıştırır, depolama alanını azaltır. Büyük veri kümelerinde analitik sorguların performansını artırır. Veri az okunduğu için diskten memory’e aktarılan veri de az olur ve bu şekilde memory de çok efektif bir şekilde kullanılır. ColumStoreIndex koyduğumuzda tablonun boyutu da ciddi anlamda küçülecektir. Sütun tabanlı depolama ile sorgular, paralel olarak çalıştırılabilir. Sadece sorguda ihtiyaç duyulan sütunları okuyarak I/O maliyetlerini azaltır.

Columnstore Index Çeşitleri
1. Clustered Columnstore Index:

Tüm tabloyu kapsar ve veri bu indeksle birlikte sütun bazında saklanır.
OLAP iş yükleri için idealdir.

2. Nonclustered Columnstore Index:
Tablo zaten satır tabanlı bir yapıdaysa, belirli sütunlarda kullanılabilir.
Hibrit iş yükleri (OLAP ve OLTP) için uygundur.

 Örneklerle Columnstore Index Kullanımı

 1. Clustered Columnstore Index Oluşturma
Bu senaryo, büyük miktarda analitik veriyle çalışırken kullanılır.

 Tablo Oluşturma:

CREATE TABLE Satislar (
SatisID INT PRIMARY KEY,
MusteriID INT,
UrunID INT,
SatisTarihi DATE,
Miktar INT,
Tutar DECIMAL(10,2)
);

 Clustered Columnstore Index Ekleme:

Gelen ekranda Index ismi verilir. Ekstradan paralelism ve hangi filegroup altında olacağı belirlenir. Tüm tabloyu kapsadığı için herhangi bir sütün seçimi yapmamış olduk.

Yapılan işlemin scriptini alıp clustered column store yapımızı oluşturuyoruz.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Satislar ON Satislar WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

Msg 35372, Level 16, State 3, Line 19

You cannot create more than one clustered index on table ‘Satislar’. Consider creating a new clustered index using ‘with (drop_existing = on)’ option.

Yukarıda sorguda hata almamızın sebebi sorgumuzu tablomuzda bir primary index key yapısının olmasıdır. İlgili primary index yapımızı slip tekrardan oluşturalım. Sorgumuzu tekrardan çalıştırdığımızda başarılı bir şekilde sonucumuz dönmüş oldu.

Eğer mevcut clustered Index’i Columnstore Index’e dönüştürmek istiyorsanız DROP_EXISTING = ON yapmamız yeterli olacaktır. Bu komut clustered indexs’i kaldırıp columnstore index oluşturmaktadır.
Bu komut, tabloyu sütun bazlı bir yapıya dönüştürür.
Not: Clustered Columnstore Index, tabloyu fiziksel olarak dönüştürdüğünden artık klasik satır tabanlı depolama yapılmaz.

 2. Nonclustered Columnstore Index Oluşturma
Bu senaryo, hem OLTP hem de OLAP işlemlerini desteklemek için kullanılır.

Gelen ekranda  Nonclustered Columnstore index yapımız için kolonlar seçilmektedir.

İlgili işlemin script’i alınıp yapımız oluşturulur.

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_Satislar] ON [dbo].[Satislar]
(
	[Miktar],
	[Tutar]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)

Bu komut, sadece Miktar ve Tutar sütunları için sütun tabanlı bir dizin oluşturur. Tablo satır bazlı bir yapıda kalmaya devam eder.

Not: Bir tabloda clustered columnstore index yapınız varsa nonclustered columnstore index oluşturmanıza izin vermez. Çünkü sql server tarafından desteklenmeyen bir özelliktir. Birinin silinmesi gerekmektedir.

3. Columnstore Index Kullanımını Test Etme
Columnstore Index’in performansını test etmek için bir sorgu yazabilirsiniz. Örneğin:

SELECT SatisTarihi, SUM(Tutar)
FROM Satislar
GROUP BY SatisTarihi
ORDER BY SatisTarihi;

Bu sorgu, Columnstore Index sayesinde daha hızlı çalışacaktır çünkü yalnızca gerekli sütunlar okunur.

 Columnstore Index’in Kullanım Alanları
 Veri ambarları ve büyük veri analitik sistemleri.
 Raporlama uygulamaları.
 Zaman serisi veriler üzerinde analiz.
 Büyük hacimli veri kümeleriyle yapılan hesaplama yoğun sorgular.

 Columnstore Index’in Limitasyonları
1. Daha Yavaş Güncelleme: OLTP (Online Transaction Processing) senaryolarında Clustered Columnstore Index ile güncelleme işlemleri daha yavaş olabilir.
2. Bazı Sınırlamalar: Birkaç SQL Server sürümünde Nonclustered Columnstore Index üzerinde tüm veri türleri desteklenmez.

Not: Partition ile beraber kullanılabilir, ama partition kolonunun columnstore index üzerinde tanımlı olması gerekir. Normal index’lerin 900 byte’ı geçmemesi gerekirdi. ColumnStore index’te böyle bir sınırlama yok.

Bir Örnek üzerinden ilgili yapımızı inceleyelim.

İlk olarak bir tablo oluşturup içerisini verilerle dolduruyoruz.

CREATE TABLE SalesData (
    SaleID INT NOT NULL,
    ProductID INT NOT NULL,
    SaleDate DATE NOT NULL,
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    TotalAmount AS (Quantity * Price)
);



DECLARE @sayac bigint;
SET @sayac=0
while(1=1)
BEGIN
INSERT INTO SalesData (SaleID,ProductID, SaleDate, Quantity, Price)
VALUES
(@sayac,@sayac, '2024-01-01',@sayac, 20.50),
(@sayac,@sayac, '2024-01-02',@sayac, 30.00),
(@sayac,@sayac, '2024-01-03',@sayac, 25.00),
(@sayac,@sayac, '2024-01-04',@sayac, 15.75),
(@sayac,@sayac, '2024-01-05',@sayac, 40.00);
SET @sayac=@sayac+1
END

Yukarıdaki döngü ile tablomuza 12 milyon kayıt atıyoruz. Aşağıdaki resimdeki stored procedure ile görebiliriz.

Column store index tanımlamadan önce tablomuza bir nonclustered index atalım. Daha sonra karşılaştırıp aralarındaki farkı görebiliriz.

CREATE NONCLUSTERED INDEX [IX_SalesData] ON [dbo].[SalesData]
(
 SaleID ,ProductID ,SaleDate ,Quantity ,Price ,TotalAmount 
)

İndex oluşturduktan sonra 6 GB’lık bir alan sadece index için ayrılmış oldu.

Tablomuzda nonclustered columnstore index  oluşturuyoruz.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_NonClusteredColumnStoreIndex] ON [dbo].[SalesData]
(
	[SaleID],
	[ProductID],
	[SaleDate],
	[Quantity],
	[Price]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0,maxdop=1)

Yukarıdaki columnstore index oluşturduktan sonra index size alanımıza tekrardan bakalım.

Mevcut index boyutunun  1.2 GB arttığını görmüş oluyoruz. Normal index yapımıza göre 5 kat daha sıkıştırarak alınmış oldu.

Şimdi sorgularımızla hangi indexs’in daha avantajlı olduğunu gözlemleyelim.

set statistics io,time on
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM SalesData WITH(Index=[IX_SalesData])
GROUP BY ProductID;

set statistics io,time on
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM SalesData WITH(Index=[IX_NonClusteredColumnStoreIndex])
GROUP BY ProductID;

Yukarıdaki iki sorgumuzu incelediğimizde ilk sorgumuz 81174 logical read yapmışken, column store index olan sorgumuz ise 14728 lob logical reads yapısından görülmektedir. Cpu oranlarındada 2-3 katlık bir fark görülmektedir.

Daha iyi bir performans elde etmek için tablo boyutumuzun büyük olması gerekmektedir.

Sorgumuzda forced index belirtmeyip sorgumuzu çalıştırdığımızda sql server hangi execution plan yapısı daha uygunsa onu kullanmaktadır.

COLUMNSTORE_ARCHIVE opsiyonu SQL Server 2014 ile beraber geldi. ColumnStore Index’leri arşivlemek istersek bu opsiyon ile arşivleme yaparken columnstore index’lerimizi sıkıştırabiliriz.

Aşağıdaki script yardımıyla bu işlemi gerçekleştirebiliriz.

ALTER INDEX [IX_NonClusteredColumnStoreIndex] ON SalesData 
REBUILD 
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE); 
 

Arşivlediğimiz columnstore index’leri tekrar eski haline getirmek için de aşağıdaki script’i kullanabilirsiniz.

ALTER INDEX [IX_NonClusteredColumnStoreIndex] ON SalesData 
REBUILD 
WITH (DATA_COMPRESSION = COLUMNSTORE);

Tüm instance altında bulunan veritabanlarında bu index yapısının aktif olup olmadığını anlamak için aşağıdaki komut kullanılmaktadır. Where koşulunda tablo bazlıda filtrelemede eklenebilir.

exec sp_MSforeachdb'
use [?];
SELECT 
''?'' as databasename,
    t.name as tabloname,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint
FROM 
    sys.indexes i
INNER JOIN 
    sys.tables t ON i.object_id = t.object_id
WHERE 
	i.type_desc LIKE ''%COLUMNSTORE%'';'

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

Bu örneklerle, Columnstore Index‘in SQL Server’daki işlevini ve kullanımını anlamış olmalısınız. Başka bir makalede görüşmek dileğiyle..

Dünya hayatı yalnızca bir oyun ve bir oyalanmadan başkası değildir. Korkup-sakınmakta olanlar için ahiret yurdu gerçekten daha hayırlıdır. Yine de akıl erdirmeyecek misiniz? En’am Suresi, 32. Ayet