Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Haziran 2017  >>
PaSaÇaPeCuCuPa
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
Keywords

SQL Server 2011 Denali’nin ilk çıkan CTP’sinde anons edilen ColumnStore Index tipi özellikle DWH sistemleri için heyecan yaratmıştı. Bazı sorgularda 16 kata kadar iyileştirme sağladığı söylenen ColumnStore Index için ben de şu makaleyi yazmıştım.

CTP1 versiyonunda ColumnStore Index kullanımı olmadığı için sadece duyduklarımızla yetinirken CTP3’ün çıkmasıyla beraber artık ColumnStore Index’i denemeye başlayabiliriz.

[more]

Daha önce yazdığım makalede ColumnStore Index’in ana mantığının kayıtları page’lerde satır bazlı değil sütun bazlı saklaması olduğunu belirtip avantajlarını şu şekilde sıralamıştım.

  • Sadece istenen kolon ile ilgili page’ler disk’ten okunabilir. Bu durumda gereksiz kolonların okunması engellenmiş olur.Bu da daha az page okunması yani performans artışı manasına gelmektedir.
  • Page’lerin içinde aynı kolona ait bilgiler tutulduğu ve bu bilgilerin birbirinin aynısının olma ihtimali yüksek olduğu için columnstore page’ler çok iyi sıkıştırılabilir. Bu da gene diskten daha az datanın okunması anlamına gelmektedir.
  • Daha az page memory’e alındığı için buffer cache hit ratio performance counter’ı yani istenen bilgilerin memory’den karşılanması counter’ı çok artacaktır. Bu da performans artışına delalettir.

Şimdi isterseniz gelin bir örnek yaparak bu avantajları tek tek görmeye çalışalım.

Örneğimiz için ilk olarak bir çalışma tablosu create edip içine 1.000.000 kayıt basıyorum. Kayıtları mod 100’den geçirerek 100 farklı kayıdın oluşmasını sağlıyorum ki tablomda benzer kayıtlar olsun dolayısıyla ColumnStore index’in özelliklerinden faydalanabileyim.

Create Table DenemeTbl (Col1 int, Col2 char(10), Col3 char(20), Col4 char(15), Col5 char(20))
GO

SET NOCOUNT ON

declare @i int=0
while @i<1000000 begin
  insert DenemeTbl
    select @i
	  ,'turgay'+cast(@i%100 as varchar(2))
	  ,'sahtiyan'+cast(@i%100 as varchar(2))
	  ,'SQLServerDBA'+cast(@i%100 as varchar(2))
	  ,'555 55 55 '+cast(@i%100 as varchar(2))
  set @i=@i+1
end

 

Şimdi Col1 için Clustered Index ve Col2,Col3,Col4,Col5'ten oluşan composite bir NonClustered Index oluşturuyorum. Bu oluşturduğum NonClustered Index’i ColumnStore Index ile karşılaştırma yapmak için kullanacağım.

create clustered index CIX_1 on DenemeTbl (Col1)
GO

create nonclustered index IX_NormalIndex on DenemeTbl (Col2, Col3, Col4, Col5)
GO

 

Şimdi yukarıdaki NonClustered Index ile aynı kolonlar üzerine bir ColumnStore Index oluşturuyorum. İşlemi hem TSQL kod ile hem de SSMS ile yapabilirim.

SSMS ile arayüzden yapmak için tablonun detayında bulunan Index yazısına sağ tıklayıp Non-Clustered ColumnStore Index yazısını seçiyorum.

1

Gelen Index tanımlama ekranında Index için bir isim belirleyip daha sonra Add butonu vasıtasıyla Index’in kolonlarını seçiyorum.

2

OK’e basarak ColumnStore Index’i create ediyorum.

Aynı işlemi aşağıdaki TSQL komutunu kullanarak ta yapabilirim.

create nonclustered COLUMNSTORE index IX_ColumnsStoreIndex on DenemeTbl (Col2, Col3, Col4, Col5)
GO

 

Sonuç itibarıyla tablomda şu index’ler olacak.

Index Type IndexName
Clustered Index CIX_1
NonClustered Index IX_NormalIndex
NonClustered ColumnStore Index IX_ColumnsStoreIndex

 

Index listesine tablonun altındaki Index kısmından da ulaşabilirim.

3

Kayıtlar Page’lerde Satır Bazlı Değil Kolon Bazlı Tutulur

ColumnStore Index’in mantığı, klasik index’lerin aksine, kayıtların page’lerin içinde satır bazlı değil kolon bazlı olarak tutulmasıdır.

Her 2 index’in leaf level page’lerini incelediğimizde;

Normal NonClustered Index Leaf Level Page Örneği ;

4

Kayıtların satır bazında tutulduğunu görüyoruz.

ColumnStore Index’in leaf level page’ine baktığımızda ise kayıtların kolon bazında sıkıştırılarak tutulduğu için LOB olarak tutulduğunu görüyoruz.

5

ColumnStore Index Daha Az Page’ten Oluşur

ColumnStore Index, kayıtları kolon bazında tuttuğu ve bu kolondaki bilgilerin birbirinin aynısının olma ihtimali yüksek olduğu için ColumnStore page’ler çok iyi sıkıştırılabilir. Bu da Index’in daha az page’ten oluşması anlamına gelir.

Şimdi 2 index arasındaki boyut ve page sayısı farkına bakıp yukarıdaki savı doğrulayalım.

select i.name as IndexName
	   ,reserved_page_count as PageCount
	   ,reserved_page_count*8 as IndexSize_KB
from sys.dm_db_partition_stats ps
left join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
where ps.object_id=OBJECT_ID('DenemeTbl') and ps.index_id>1

 

6

Gördüğünüz gibi Normal NonClustered Index 9477 page’ten oluşurken aynı kolonlar üzerine oluşturulmuş ColumnStore Index ise sadece 354 page’ten oluşmakta.

ColumnStore Index Daha Performanslı Çalışır

ColumnStore Index,muadili Normal NonClustered Index’e oranla daha az page’ten oluştuğu ve ayrıca tek bir kolonun sorgulanmak istendiği durumda sadece ilgili kolonun page’lerini getirebilme özelliklerinden dolayı daha performanslı çalışır. Daha önceki makalemde bu performans artışının 16 kata kadar çıkabileceğini belirtmiştik. Şimdi bunun denemesi gelin bir de biz yapalım.

Yapacağım denemede IO istatistiğini açarak her 2 index’in logical read değerlerini karşılaştıracağım.

SET STATISTICS IO ON
GO

--Cache'de bulunan plan'ları silelim
DBCC FREEPROCCACHE
GO
--Memory'de bulunan page'leri silerek 
--page'lerin diskten okunmasını sağlayalım.
DBCC DROPCLEANBUFFERS
GO

select Col3 
from dbo.DenemeTbl with (Index = IX_NormalIndex)
where Col2 = 'Turgay15' 
GO

--Cache'de bulunan plan'ları silelim
DBCC FREEPROCCACHE
GO
--Memory'de bulunan page'leri silerek 
--page'lerin diskten okunmasını sağlayalım.
DBCC DROPCLEANBUFFERS
GO

select Col3 
from dbo.DenemeTbl with (Index = IX_ColumnsStoreIndex)
where Col2 = 'Turgay15' 
GO

 

7

Resimde de gördüğünüz gibi Normal NonClusteredIndex kullanılarak 98 page’in okunması gerekirken aynı sorgu ColumnStore Index kullanılarak 32 page okunarak tamamlanabiliyor.

Bu örneğimizde tabloda çok fazla kayıt olmadığı için performans artışı 3 kat seviyelerinde kaldı. Büyük DWH sistemlerinde performans artışı çok daha fazla olabilecektir.

ColumnStore Index İçeren Tablolarda Insert, Update, Delete Yapılamaz

ColumnStore Index’lerin en büyük eksisi bu tip index’leri içeren tablolarda insert,update,delete işlemleri yapılamamasıdır. ColumnStore index içeren tabloda bu tarz bir işlem yapılmak istendiğinde şu tarz bir hata mesajı alınacaktır.

insert DenemeTbl
	Select 1000001, 'Turgay', 'Sahtiyan', 'SQLServerDBA', '555 55 55'
GO

update DenemeTbl
	set Col2 = 'Turgay'
	where Col1 = 1
GO

delete from DenemeTbl where Col1=1
GO

 

Msg 35330, Level 15, State 1, Line 1
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.


Msg 35330, Level 15, State 1, Line 2 UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.


Msg 35330, Level 15, State 1, Line 2 DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

8

Hata mesajında da gördüğünüz gibi insert,delete,update işleminden önce ColumnStore index disable edilmeli, işlem bitiminde de rebuild edilmelidir.

alter index IX_ColumnsStoreIndex on dbo.DenemeTbl disable
GO

insert DenemeTbl
	Select 1000001, 'Turgay', 'Sahtiyan', 'SQLServerDBA', '555 55 55'
GO

alter index IX_ColumnsStoreIndex on dbo.DenemeTbl rebuild
GO

 

9

Her işlemden önce disable, sonrasında rebuild ilk etapta kulağa hoş gelmese de ColumnStore index’in DWH sistemleri için bir yenilik olduğunu unutmamak gerekir. DWH sistemleri çok fazla data değişimi yaşamadığından hatta çoğu zaman günde 1 kez populate olduğundan dolayı populate’ten önce disable ve sonra rebuild etmek çok problem çıkaracak bir durum gibi gözükmemektedir.

Sonuç

ColumnStore Index, SQL Server 2011 Denali ile gelen, DWH sistemlerinde bulunan büyük tablolar için performans arttırıcı yeni bir index tipidir. Kayıtları satır bazında değil kolon bazında tutuyor olması en büyük özelliğidir. Bu şekilde sadece istenen kolon ile ilgili page’lerin okunması yeterlidir. Ayrıca aynı kolonların verisinin aynı olma ihtimalinden dolayı ColumnStore index’lerde sıkıştırma oranı çok daha fazladır. Bu da index’in daha az page’ten oluşmasını dolayısıyla daha performanslı çalışmasını sağlamaktadır.

Özellikle ETL süreçlerini düşündüğümüzde, verinin extract edilmesi esnasında okunan verilerin ColumnStore indexlerden karşılanması verilerin çok fazla miktarlarda olduğunu göz önünde bulundurduğumuz senaryolarda veritabanı sunucularında bulanan memory baskısını ciddi oranda azaltacaktır , bu durum sistemimiz üzerinde olumlu bir performans etkisi yaratacaktır.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Column Based Query Accelarator,iş kritik uygulamalarının DWH ayağında performans iyileştirmek amacıyla, 2011 Denali ile beraber gelen en önemli özelliklerden biridir. Microsoft’un belirttiğine ve bazı demolara göre yoğun data içeren tablolarda 10 kata kadar daha iyi performans almak mümkündür.

[more]

Colum Based Query Accelarator, adından da anlaşılacağı üzere datayı page’lerde satır(row) bazında saklamaktansa, kolon (column) bazında saklayarak bu kolon üzerinden çekilen sorgularda 10 kata kadar performans artışı sağlamaktadır.

Bugünkü makalemde, bu yapının detayına değinip,avantajlarına baktıktan sonra Microsoft’un yaptığı bir demodan bahsedip yazımı noktalayacağım.

SQL Server 2011 Denali’de bu özelliği yeni gelecek olan columnstore index tipi ile sağlayacağız. columnstore index tanımlaması yapıldığında data, page’ler de satır bazında değil kolon bazında saklanmaktadır. Bunun ne demek olduğunu aşağıdaki resimde görmeye çalışalım.

1

Resimde de gördüğünüz gibi bir tablo için oluşturulmuş klasik index’lerde datalar page’lerde row yani satır bazlı olarak tutulurlar.

Oysaki columnstore index yapısında datalar page’lerde column yani kolon bazlı olarak tutulurlar.

Bu şekilde bir yapı şu avantajları sağlamaktadır;

  • Sadece istenen kolon ile ilgili page’ler disk’ten okunabilir. Bu durumda gereksiz kolonların okunması engellenmiş olur.Bu da daha az page okunması yani performans artışı manasına gelmektedir.
  • Page’lerin içinde aynı kolona ait bilgiler tutulduğu ve bu bilgilerin birbirinin aynısının olma ihtimali yüksek olduğu için columnstore page’ler çok iyi sıkıştırılabilir. Bu da gene diskten daha az datanın okunması anlamına gelmektedir.
  • Daha az page memory’e alındığı için buffer cache hit ratio performance counter’ı yani istenen bilgilerin memory’den karşılanması counter’ı çok artacaktır. Bu da performans artışına delalettir.

Columnstore index içeren tablolarda delete,insert,update işlemleri yapılamaz. Bu işlemleri yapabilmek için ilk olarak columnstore index’in disable edilmesi gerekmektedir. Table üzerinde update işlemlerini bitirdikten sonra index’i rebuild etmeniz yeterli olacaktır.

Şimdi, Microsoft’un verdiği sunumlarda kullandığı bir demo işleminden bahsedelim. Bu demoda 1,3 milyar row’dan oluşan, 1 TB boyutunda bir tablo kullanılmış. Ve bu tablo üzerinde şu şekilde bir columnstore index create edilmiş.

CREATE COLUMNSTORE INDEX cstore on [dbo].[catalog_sales] 
           ([cs_sold_date_sk] 
           ,[cs_sold_time_sk] 
           ,[cs_ship_date_sk] 
           ,[cs_bill_customer_sk] 
           ,[cs_bill_cdemo_sk] 
           ,[cs_bill_hdemo_sk] 
           ,[cs_bill_addr_sk] 
           ,[cs_ship_customer_sk] 
           ,[cs_ship_cdemo_sk] 
           ,[cs_ship_hdemo_sk] 
           ,[cs_ship_addr_sk] 
           ,[cs_call_center_sk] 
           ,[cs_catalog_page_sk] 
           ,[cs_ship_mode_sk] 
           ,[cs_warehouse_sk] 
           ,[cs_item_sk] 6 
           ,[cs_promo_sk] 
           ,[cs_order_number] 
           ,[cs_quantity] 
           ,[cs_wholesale_cost] 
           ,[cs_list_price] 
           ,[cs_sales_price] 
           ,[cs_ext_discount_amt] 
           ,[cs_ext_sales_price] 
           ,[cs_ext_wholesale_cost] 
           ,[cs_ext_list_price] 
           ,[cs_ext_tax] 
           ,[cs_coupon_amt] 
           ,[cs_ext_ship_cost] 
           ,[cs_net_paid] 
           ,[cs_net_paid_inc_tax] 
           ,[cs_net_paid_inc_ship] 
           ,[cs_net_paid_inc_ship_tax] 
           ,[cs_net_profit]) 

 

32 logical CPU ve 256 GB Ram üzerinde bulundurulan bu tabloda şu sorguyu çekerek sonucun gelme süreleri birbirleriyle karşılaştırılmış.

select w_city, w_state, d_year, SUM(cs_sales_price) as cs_sales_price 
from warehouse, catalog_sales, date_dim 
where w_warehouse_sk = cs_warehouse_sk 
and cs_sold_date_sk = d_date_sk 
and w_state in ('SD','OH') 
and d_year in (2001,2002,2003) 
group by w_city, w_state, d_year 
order by d_year, w_state, w_city; 

 

Şimdi columnstore index’in olduğu ve olmadığı durumlara göre sorgu sürelerini görelim.

  Total CPU Time (seconds) Total Elapsed Time (seconds)
Columnstore varken 31 1,10
Columnstore yokken 502 501
Hızlanma 16 Kat Hızlanma 455 Kat Hızlanma

 

16 Kat hızlanma. Gerçekten müthiş değil mi?

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan