MSSQL Server ColumnStore Index

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. 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 […]

MSSQL Server ColumnStore Index

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.

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

  1. 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.
  2. 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.

Benzer Yazılar

SQL SERVER RIGHT-LEFT PARTITION

SQL Server 5 gün önce

SQL Server’da partitioning, büyük veritabanı tablolarını daha yönetilebilir ve performanslı hale getirmek amacıyla kullanılan bir tekniktir. Bu teknik, tablonun verilerini fiziksel olarak değil, mantıksal olarak parçalara ayırır. Veriler, belirli bir partition function ve partition scheme kullanılarak farklı bölümlere yönlendirilir. Partitioning, özellikle büyük veri kümeleriyle çalışan veri tabanlarında sorgu performansını artırır ve veri yönetimini kolaylaştırır. Partition Function ve Partition Scheme nedir? Partition Function: Verilerin hangi kriterlere göre bölüneceğini belirler. Örneğin, bir tarih aralığına göre verileri ayırmak. Partition Scheme: Verilerin hangi filegroup’larda depolanacağını belirler.   Örnek olarak Range LEFT ve Range RIGHT olmak üzere iki ayrı tabloda partition nasıl yapılır sizlere göstereceğim. İlk olarak Range LEFT olan partition yapısından başlayacağım. Öncelikle Veri tabanıma yeni filegroup ve file ekliyorum Şimdi sıra FUNCTION ve SCHEME oluşturmakta ben tablomu yıllık olarak partition yaptım sizler ihtiyaçlarınız doğrultusunda aylık,günlük vs yapabilirsiniz. LEFT partition dediğimiz olay vermiş olduğunuz tarih aralığına eşit bir veri geldiğinde bu veriyi solundaki partition […]

Veritabanı Recovery Pending Durumu ve Düzeltme Seçenekleri

SQL Server 2 hafta önce

İçindekilerVeritabanı Recovery Pending Durumu Nedir?Veritabanı Neden Recover Pending Duruma Düşer?Recovery Pending Durumu Nasıl Çözülür?SonuçKaynaklarVeritabanı Recovery Pending Durumu Nedir? SQL Server’da veritabanları bazı nedenlere bağlı olarak “Recovery Pending” (Kurtarma Bekleme)  moduna geçebilir. Veritabanın düzgün bir şekilde kapatılmaması, eksik veya bozuk log dosyaları, disk depolama sorunları, sistemde yaşanan anormal şekilde çökmeler veya MS SQL Server’daki hatalar bu duruma sebep olabilir. Recovery durumu, aslında veritabanını tekrar kullanılabilir hale getirmek için bir kurtarma işlemi yürüttüğünü ifade eder ve üç aşamadan oluşur; Analysis (Analiz): Transaction log incelemesi yapılması ve işlemlerin tamamlanma (Commit) durumunun kontrol edilmesi, Redo (Yeniden İşleme): Tamamlanmış (Commit) ancak henüz diske yazılamamış olan işlemlerin yeniden işlenmesi, Undo (Geri Alma): Başlamış (Begin) ancak tamamlanmamış (Commit) işlemlerin  geri alınmasıdır.   Veritabanı Neden Recover Pending Duruma Düşer? SQL Server Restart Süreci SQL Server servisi restart edildiğinde üzerinde bulunan tüm veritabanları tutarlılığın sağlanması için otomatik olarak recovery moduna girer ve redo/undo işlemleri sürecince devam eder. Ani Sistem […]

SQL Server DMV ve DMF – 6

SQL Server 2 hafta önce

Bu yazımızda DMV ve DMF Serimizin 6.sına devam edeceğiz. Bir önceki seride Memory’ye ilişkin DMV ve DMF’leri ele almıştık. Bu yazıda Memory konusunda devam edeceğiz. SQL server’da Memory kavramı en önemli kavramlardan biridir. Özellikle tüm transaction işlemlerinin önce Buffer sonra disk üzerinden devam ettiğini düşünürsek buffer’ın oynadığı kritik rolü daha iyi anlayabiliriz. Bu yazıda Memory’nin durumunu ve monitör edilmesine bakacağız. Özellikle Performans sorunlarında memory konusunda sorun yaşandığı durumda nasıl okumak gerektiği önemli rol oynamaktadır. Hangi database’de, hangi tablo’da sorun yaşandığına ilişkin bilgilere bu paylaşım sonrasında görebileceğiz. SQL Server’ın Memory kullanım durumunu incelediğimde; select physical_memory_in_use_kb/1048576.0 AS ‘physical_memory_in_use (GB)’, locked_page_allocations_kb/1048576.0 AS ‘locked_page_allocations (GB)’, virtual_address_space_committed_kb/1048576.0 AS ‘virtual_address_space_committed (GB)’, available_commit_limit_kb/1048576.0 AS ‘available_commit_limit (GB)’, page_fault_count as ‘page_fault_count’ from  sys.dm_os_process_memory; Görsel – 1   Physical_memory_in_use: Kullanımda olan Fiziksel Memory miktarını gösterir. locked_page_allocations: Memory’de lock’lanmış olan Page’lerin miktarını belirtir. virtual_address_space_contained: SQL Server VAS(Virtual Adress Space) için ayrılan miktarı belirtir. available_commit_limit: SQL Server tarafından kullanılabilecek Memory Miktarını gösterir. […]

0 Yorum

Yorum Yaz

Rastgele