SQL Server’da Columnstore Index Arşivlenmiş veriler, büyük veri ambarları ve analitik sorgular için performansı artıran bir index türüdür. Geleneksel satır tabanlı Index yerine, verileri sütunlar halinde depolar ve sorgu performansını önemli ölçüde artırır.
- 1.Depolama Yapısı
- 2. Sıkıştırma
- 3. Veri Erişimi
- 4. Batch Mode Processing
- 5. Delta Store
- 6. Veri Güncelleme ve Silme
- 7. Rowgroup
- SQL Server Arkaplanında gerçekleşen işlemleri ve çalışma mantığını inceleyelim.
- 1.Tuple Mover
- 2.Index Rebuild ve Reorganize
- 3.Garbage Collection
- 4.Compression
- 5.Delta Store Management
- 6.Segment Merging
- Columnstore Index’in Bellek Kullanımı
- 1.Segment Bellek Kullanımı
- 2.Batch Mode Processing’in Özellikleri
- ColumnStore Index’in Başlıca Dezavantajları
- 1.Güncelleme ve Silme İşlemlerinin Karmaşıklığı
- 2.Satır Tabanlı İşlemler İçin Uygun Olmaması
- 3.Sınırlamalar ve Uyumsuzluklar
- Sürümlere göre ColumnStore Index özellikleri
- SQL Server 2012’de Columnstore Index’in Temel Özellikleri
- SQL Server 2014’de Columnstore Index’in Temel Özellikleri
- SQL Server 2016’de Columnstore Index’in Temel Özellikleri
- Columnstore index aşağıdakilerle birlikte kullanılamaz.
MSSQL Server ‘da ColumnStore İndex ‘in Avantajları, Kullanım alanları , Sürümlere göre farkları , Performansa etkisi ve en önemlisi Data boyutunuzdaki Compress(Sıkıştırma) özelliğini nasıl kullandığını anlatmaya çalışacağım.
İlk olarak SQL Server 2012‘de tanıtılan Columnstore Index, sütun bazında depolanan İndex’in oluşturulmasına olanak tanıyan ve Memory kullanan bir yapıdır. Sorgu performansını mükemmel seviyeye taşıyabilecek bir index türüdür.
Bir columnstore index, verileri farklı bir index Pagelerde depolar ve verileri büyük ölçüde sıkıştırır. Ayrıca, işleme hızını büyük ölçüde artıran ve CPU kullanımını azaltan yeni bir Batch Mode Processing modu sunar. Yeni depolama türü, sıkıştırılmış veriler ve batch mode processing işleminin birleşimi, SQL Server’ın daha az veri okumasını ve sorgu performansını büyük ölçüde iyileştirmesini sağlar.
Peki bu yeni index geleneksel index’den nasıl farklıdır? 2012’den önceki tüm sürümlerde, SQL Server hem Clustered hem de Non Clustered İndex dahil olmak üzere tüm indexler için B-tree yapısı kullanır.
B-tree leaf Level seviyesinde, SQL Server indexlenmiş verileri satır bazında depolar. Yani tek bir index sayfası bir veya daha fazla satır için tüm sütunlardaki tüm verileri depolar.
Örnek verecek olursak bir Tablo içinde üç sütunlu (C1, C2 ve C3) bir index oluşturduysak, index’simizin Page’lerdeki mantıksal depolaması aşağıdaki gibi olurdu.
Burada basit bir sorgu çalıştırmalım.
Select C1 from table
scripti çağırdığımız zaman doğal olarak C1 kolonunun bulunduğu 3 page’e bakması gerekecektir. bu verilerin mantıksal depolaması aşağıdaki gibi görünecektir.
Şimdi aynı veriler üzerinde bir columnstore indeksi oluşturursak, bu verilerin mantıksal depolaması aşağıdaki gibi görünecektir.
Aynı sorguyu tekrar çalıştırdığımız zaman ise mantıksal görünümü aşağıdaki gibi olacaktır.
Select C1 from table
Burada gördüğünüz gibi 1 Page üzerinden sorguyu yanıtlayacaktı, bu durum B-tree yapısına sahip indexten 3 kat performans oranına sahip olduğu anlaşılacaktır. Şimdi 1 G/Ç ile 3 G/Ç arasında pek bir fark yok, ancak bu özelliğin milyonlarca hatta milyarlarca satır içeren tablolara sahip veri ambarlarını hedef aldığını unutmayın. Bu kadar büyük tablolarda ColumnStore index’de kullandığınızda, G/Ç’deki performans kazanımları çok daha belirgin hale gelecektir.
İkinci avantajı ise Compress, index içinde depolanan verilerin yoğun şekilde sıkıştırılmış olmasıdır. Bu sıkıştırma algoritması satır veya sayfa sıkıştırmasından veya yedekleme sıkıştırmasından farklıdır. Genellikle çok sayıda tekrarlayan değere sahip karakter veya sayısal veriler için en iyi şekilde çalışır. Verilerin sıkıştırılmış olması, SQL Server’ın tüm verileri döndürmek için daha az page okuması gerektiği anlamına gelir.
Performansı iyileştirmeye yardımcı olmasının üçüncü yolu batch mode processing. Bu mode CPU’nun aynı anda yaklaşık 1000 satırı işlemesine olanak tanır. Bu, CPU’nun bir seferde bir satır yerine bir veri grubunu işlemesi için daha verimlidir.
Sonuç, daha hızlı sorgu işleme ve daha düşük CPU kullanımıdır.
Peki ColumnStore İndex nasıl çalışır? Adım adım inceleyelim.
1.Depolama Yapısı
Columnstore Index, verileri satırlar yerine sütunlar halinde depolar. Bu, benzer türdeki verilerin birbirine yakın tutulmasını sağlar ve sıkıştırma oranını artırır. Veriler iki ana bileşende depolanır:
- Segmentler: Her sütun verisi, 1 milyon satır içeren segmentlere ayrılır. Bu segmentler, sıkıştırılmış verileri içerir.
- İndex: Her segmentin başında, verilerin hızlı erişimi için bir dizi index bulunur.
2. Sıkıştırma
Columnstore Index, verileri sıkıştırmak için gelişmiş algoritmalar kullanır. Bu, disk alanını azaltır ve bellek kullanımını optimize eder. Sıkıştırma, CPU maliyetini artırsa da, disk G/Ç operasyonlarını önemli ölçüde azaltır ve sorgu performansını artırır.
3. Veri Erişimi
Columnstore Index, yalnızca sorgulanan sütunları okuduğundan, gereksiz verilerin okunmasını engeller ve G/Ç maliyetlerini düşürür. Bu, özellikle geniş tablolarda büyük veri setleriyle çalışırken önemli bir performans artışı sağlar.
4. Batch Mode Processing
SQL Server, Columnstore Index kullanarak Batch Mode Processing (Yığın Modu İşleme) adı verilen bir işleme modu kullanır. Bu mod, büyük veri setlerini işlerken CPU verimliliğini artırır. Veriler, yığınlar halinde işlenir ve CPU üzerindeki işlem maliyetleri azaltılır.
5. Delta Store
Yeni veriler, başlangıçta delta store (delta deposu) adı verilen geçici bir alanda depolanır. Delta store, satır tabanlı bir yapıdır ve veriler burada saklanırken daha sonra Columnstore Index’e taşınır. Bu süreç, “Tuple Mover” adı verilen bir arka plan işlemiyle yönetilir.
6. Veri Güncelleme ve Silme
Columnstore Index, doğrudan veri güncelleme ve silme işlemlerini desteklemez. Bu tür işlemler, delta store’da tutulur ve arka plan işlemleriyle yönetilir. Bu süreç, işlem performansını ve veri bütünlüğünü korur.
7. Rowgroup
Columnstore Index, verileri rowgroup (satır grubu) adı verilen mantıksal birimlerde saklar. Her rowgroup, yaklaşık 1 milyon satır içerir ve veriler bu gruplarda sıkıştırılarak saklanır. Rowgroup’lar, sorgular sırasında hızlı erişim sağlar.
SQL Server Arkaplanında gerçekleşen işlemleri ve çalışma mantığını inceleyelim.
1.Tuple Mover
- Tuple Mover, delta store’da (delta deposu) depolanan yeni ve güncellenmiş verileri ana Columnstore Index’e taşır.
- Tuple Mover, delta store’daki verileri belirli aralıklarla kontrol eder. Bir delta store segmenti dolduğunda veya belirli bir zaman aralığı geçtiğinde, bu veriler sıkıştırılır ve Columnstore Index’in bir parçası haline getirilir.
- Delta store’daki verilerin düzenli olarak ana Columnstore Index’e taşınması, verilerin sıkıştırılmasını ve performansın optimize edilmesini sağlar.
2.Index Rebuild ve Reorganize
- Columnstore Index’in yeniden oluşturulması işlemidir. Bu işlem, tüm İndex yeniden oluşturur ve verileri yeniden sıkıştırır.
- Tam Rebuild: Tüm verileri yeniden düzenler ve sıkıştırır.
- Online Rebuild: Sistemin kullanılabilirliğini sağlamak için indeksin yeniden oluşturulması işlemi sırasında tabloya erişime izin verir.
3.Garbage Collection
- Garbage Collection, Columnstore Index’te silinmiş veya güncellenmiş verilerin kalıntılarını temizler.
- Sistem, düzenli aralıklarla İndex tarar ve kullanılmayan verileri temizler. Bu işlem, delta store ve Columnstore Index’teki gereksiz verileri kaldırarak depolama alanını optimize eder.
- Disk alanının verimli kullanılmasını sağlar ve İndex performansını artırır.
4.Compression
- Verilerin sıkıştırılması işlemi, Columnstore Index’in performansını optimize eder.
- Tuple Mover ve diğer arka plan işlemleri sırasında veriler sıkıştırılır. Sıkıştırma işlemi, verilerin disk üzerinde daha az yer kaplamasını ve bellek kullanımını azaltmasını sağlar.
- Disk ve bellek kullanımını azaltır, sorgu performansını artırır.
5.Delta Store Management
- Delta store’da depolanan verilerin yönetimi ve ana Columnstore Index’e taşınması işlemi.
- Delta store, yeni eklenen ve güncellenen verilerin geçici olarak depolandığı bir alandır. Veriler, belirli bir eşik değeri aştığında Tuple Mover tarafından ana Columnstore Index’e taşınır.
- Yeni ve güncellenen verilerin hızlı bir şekilde işlenmesini sağlar ve ana İndex performansını korur.
6.Segment Merging
- Parçalanmış segmentlerin birleştirilmesi işlemi, performansı artırır.
- Columnstore Index’teki küçük veya parçalanmış segmentler, daha büyük ve sıkıştırılmış segmentler halinde birleştirilir.
- Daha büyük segmentler, sorgu performansını artırır ve disk I/O işlemlerini azaltır.
Bu arka plan işlemleri, Columnstore Index’in performansını ve verimliliğini optimize etmek için önemlidir.
Columnstore Index’in Bellek Kullanımı
1.Segment Bellek Kullanımı
- Columnstore Index’teki veriler segmentler halinde saklanır. Her segment yaklaşık 1 milyon satır içerir ve sıkıştırılmış biçimde saklanır. Sorgular sırasında, gerekli segmentler belleğe yüklenir.
- Segmentlerin bellek gereksinimi, sıkıştırma oranına, verinin türüne ve segment boyutuna bağlıdır. Büyük segmentler veya sıkıştırılmamış veriler daha fazla bellek tüketebilir.
2.Batch Mode Processing’in Özellikleri
- Yığınlar Halinde İşleme (Batches)
- Veriler, belirli bir boyutta yığınlar halinde işlenir. Genellikle her yığın yaklaşık 900 satır içerir.
- Yığınlar, CPU üzerinde toplu olarak işlenir, bu da CPU saat döngülerinin daha verimli kullanılmasını sağlar.
- Verimlilik ve Performans Artışı
- CPU Kullanımı: Batch Mode Processing, her bir yığın için işlem yaparken CPU kullanımını optimize eder. Bu, CPU üzerinde aynı anda daha fazla işlemin yürütülmesini sağlar.
- Bellek Kullanımı: Yığınlar halinde işleme, bellekteki veri hareketlerini azaltır ve verilerin daha verimli bir şekilde işlenmesini sağlar.
Batch Mode Processing, Verilerin yığınlar halinde işlenmesi, CPU ve bellek kullanımını optimize eder ve büyük veri setleri üzerinde yüksek performans sağlar. Ancak, küçük veri setleri ve bazı uyumluluk sorunları göz önünde bulundurulmalıdır.
ColumnStore Index’in Başlıca Dezavantajları
1.Güncelleme ve Silme İşlemlerinin Karmaşıklığı
- Columnstore Index’te güncelleme ve silme işlemleri, delta store’a yazıldığı için yavaş olabilir. Verilerin sıkıştırılması ve ana Columnstore Index’e taşınması zaman alabilir.
- Sık güncellemeler ve silmeler, delta store’un hızlı dolmasına ve Tuple Mover’ın daha sık çalışmasına neden olabilir. Bu da performans kaybına yol açabilir.
2.Satır Tabanlı İşlemler İçin Uygun Olmaması
- Columnstore Index, sütun tabanlı veri erişimi için optimize edilmiştir. Satır tabanlı işlemler (örn. belirli bir satırın hızlıca alınması) için uygun değildir ve bu tür işlemler yavaş olabilir.
- Satır tabanlı işlemler ve transaction gereksinimleri olan uygulamalarda performans düşüşü yaşanabilir.
3.Sınırlamalar ve Uyumsuzluklar
- Columnstore Index, özellikle büyük veri ambarları ve analitik sorgular için tasarlanmıştır. OLTP (Online Transaction Processing) sistemleri için uygun değildir.
- Bütün veri türleri veya SQL Server’ın tüm sürümleri Columnstore Index’i desteklemeyebilir. Ayrıca, belirli özellikler ve işlevler Columnstore Index ile uyumlu olmayabilir.
Sürümlere göre ColumnStore Index özellikleri
SQL Server 2012’de Columnstore Index’in Temel Özellikleri
- Sadece nonclustered columnstore index oluşturulabiliyor. Clustered columnstore index sql server 2012’de yok.
- 1024 kolondan fazlasına oluşturulamıyor ama kimsenin bu kadar kolon üzerinde columnstore index oluşturacağını sanmıyorum.
- Unique olarak oluşturulamıyor.
- View ya da indexed view’de oluşturulamıyor.
- Tabloda primary key tanımlayabilirsiniz ama columnstore index aynı zamanda PrimaryKey ve ForeingKey olamaz.
- ALTER INDEX yapılamaz. ALTER yapmak istediğinizde DROP-CREATE yapmanız gerekir.
- INCLUDE olarak kolon eklenemez.
- Kolonlar asc ya da dsc olarak sıralanamazlar. Columstore index sıkıştırma algoritmasına göre sıralanır.
- FileStream özelliği içeremez.
- Index seek desteklemez. Bu yüzden küçük veri kümeleri çekilecekse sql server columnstore index kullanmayı tercih etmez. Yukarda örneğini yapmıştık.
- En önemlisi sql server 2012’de nonclustered columstore index tanımladıysanız bu tablo update edilemez.
SQL Server 2014’de Columnstore Index’in Temel Özellikleri
- update edilebilir clustered columstore index hayatımıza girdi.
- Tablomuza clustered columnstore index oluşturduğumuzda başka hiçbir index koyamıyoruz.
- Silme işlemleri sadece silindi olarak işaretlenerek daha sonra arka planda silinirler ve bu yüzden silme performansı çok hızlıdır.
- Tabloyu daha da küçültebileceğimiz compression metodu olarak columnstore_archieve geldi.
SQL Server 2016’de Columnstore Index’in Temel Özellikleri
- Snapshot Isolation ve Read Committed Snapshot Isolation Level’leri kullanabiliyoruz.
- Always on’daki read edilebilir, update edilebilir columnstore index’i destekliyor.
- Clustered columnstore index olan bir tabloda btree index oluşturulabiliyor(Non Clustered Index).
- Memory-Optimized Table’da column store index oluşturulabiliyor.
- Nonclustered columnstore index filtered olabiliyor.
- Tablo oluşturulurken columnstore index oluşturulabiliyor.
- Partition olan tabloya Clustered ColumnStore Index oluşturulamaz.
Columnstore index aşağıdakilerle birlikte kullanılamaz.
- Page ve Row Compression
- Replication
- Change tracking
- Change data capture
- Filestream
Örnek olarak Chance Data Capture özelliğini aktif etmek istediğimde hata verecektir.
NOT:ColumnStore Index NVARCHAR(MAX),VARCHAR(MAX), NTEXT , TEXT, IMAGE, BİNARY, VARBİNARY ve XML türündeki kolon içeren tablolara uygulanamaz.
Örnek Olarak:
Tablomuzda gördüğünüz gibi ntext ve image tipinde kolonlar bulunmaktadır.
Hatanın açıklaması ColumnStore Index gereksinimlerini karşılamıyor şeklinde bir hata alırsınız.
ColumnStore Index hakkında bu kadar bilgi aldıktan sonra örnek bir test ile sizler ile performansını ve compress özelliğini beraber inceleyelim.
AdventureWorks2019 database içerisinde Production.TransactionHistory tablosunu Kullandım.
Burada Clustered index’i kaldırıp işlemlere başlıyorum yukarıda belirtmek istediğim Key olarak eklenen kolonlar ColumnStoreİndex içerisinde gelecektir.
Tablomuzda Key olmasa dahi Clustered ColumnStore İndex oluşturabilirsiniz.
Öncelikle tablomuzun ilk olarak boyutuna bakıyoruz bunun için
Exec sp_spaceusad ‘TableName’ komutu ile görebilirsiniz.
Clustered ColumnStore İndex’imizi tablomuzda oluşturuyoruz.
İndex’imizi oluşturduktan sonra tekrar data boyutumuzu kontrol ediyoruz.
Resimde gördüğümüz gibi veri boyutumuzun 6 kat azaldığını görüyoruz.
Performansına bakmak için basit bir sorgu çalıştırıyoruz ve istatislikleri gözden geçiriyoruz.
İstatislikleri incelediğimiz zaman columnstoreindex’in kullanmış olduğu logical reds sayısından da anlaşılacağı üzere bariz bir performans farkı olduğunu görüyoruz.
İstenilen kolon ve koşulumuzda IX_TransacitonHistroy_ProductId Index’ine en uygun koşulu sağladığımız zaman logical reds sayısının eşit olduğunu görebiliriz.
Veri boyutunun küçük olduğu bir ortamda test etmiş olduk sizler bu testi daha büyük verilerin bulunduğu Databases’ lerde tam olarak etkisini görebilirsiniz.
ColumnStore Index Data Compress ve Performans açısından oldukça etkili olduğunu anlıyoruz.
ColumnStore Index’in uygulamayı düşünüyorsanız çok sayıda test yapmanızı tavsiye ederim. Verileri yüklemek veya değiştirmek için dikkatli planlama, tablolarınızı veri türü kısıtlamalarını hesaba katacak şekilde tasarlamak ve ColumnStore Index’in en iyi performansı almak için sorgularınızı nasıl yazacağınızı bilmeniz gerekir.
0 Yorum