Merhaba Arkadaşlar ,
Bu gün sizlere SQL Server 2008 ile hayatımıza girmiş ve Optimize edilmiş NonClustered Index olan Filtered Index’ ten bahsedeceğim .
Konunun terimsel kısmını bırakıp direkt örnek üzerinden aktarımlarımı sunacağım ;
Aşağıda ki Script ‘leri EXECUTE ederek Örnek DB ve Tablo’muzu oluşturmuş oluruz …
--Test DB 'mizi Create ediyoruz... IF ( DB_ID('SQLTURKIYE_FilteredIndex') IS NULL ) CREATE DATABASE SQLTURKIYE_FilteredIndex; GO USE SQLTURKIYE_FilteredIndex; GO -- Test tablomuzu oluşturuyoruz. SQLTURKIYE_Inquiry (Anket Tablosu) CREATE TABLE SQLTURKIYE_Inquiry ( [PersonelNo] INT PRIMARY KEY CLUSTERED , [Name] VARCHAR(100) NOT NULL , [HireDate] DATE NULL , --DATE is a new data type in SQL Server 2008 [Inquiry_Date] DATE NULL, --DATE is a new data type in SQL Server 2008 ); GO -- Test tablomuza veri basıyoruz... DECLARE @intCounter INT = 1; WHILE @intCounter <= 10000 BEGIN IF ( @intCounter {6189a1005e4f6dc613b8beb03d694ce50f0902974162d6a7dea944a970422cc9} 100 ) = 0 BEGIN INSERT INTO SQLTURKIYE_Inquiry ( PersonelNo , Name , HireDate , [Inquiry_Date] ) VALUES ( @intCounter , 'Yusuf' + CAST(@intCounter AS VARCHAR) , CAST(( GETDATE() - @intCounter ) AS DATE) , CAST(( GETDATE() - @intCounter ) AS DATE) ); END; ELSE BEGIN INSERT INTO SQLTURKIYE_Inquiry ( PersonelNo , Name , HireDate , [Inquiry_Date] ) VALUES ( @intCounter , 'Yusuf' + CAST(@intCounter AS VARCHAR) , NULL , NULL ); END; SET @intCounter = @intCounter + 1; END;
Şöyle bir örnek düşünün..
Çalışanlarımıza yaptığımız bir anket çalışmamız var ve bunun için oluşturulan tablo içerisinde Personel numarası , İsim, işe başlangıç tarihi ve anket tarihi bilgileri bulunsun.
Fakat bizim raporlarımız için anket tarihi bilgisi zorunlu değil . Yani personellerimizin bu tablo içinde anket tarihi bilgilerini girme zorunluluğu bulunmadığından boş geçilebilir.
10.000 çalışanımıza doldurttuğumuz bu ankette, anket tarihi alanını sadece 100 çalışanımız boş bırakmamıştır ve biz her gün sistemimizde aşağıda ki gibi bir sorgu çalıştırmaktayız .
SELECT * FROM SQLTURKIYE_Inquiry WHERE [Inquiry_Date] IS NOT NULL;
Anket tarihi NULL olmayan tüm verileri çekerek çıkan değerleri raporumuzda ilgili alana ekliyoruz. Bu tarz bir sorgu için aşağıda ki Index ‘i oluşturuyoruz.
CREATE NONCLUSTERED INDEX SQLNCI_SQLTURKIYE_Employees_Inquiry_Date ON SQLTURKIYE_Inquiry(Inquiry_Date); GO
Anket tarihi kolonumuz üzerine bir NonClustered Index Create ettik ve query mizin performanslı çalışmasını sağlamış olduk . Fakat Query mize baktığımızda anket tarihi NULL olmayan kayıtları çekiyoruz ama biz index ‘imizi NULL kayıtlarında üzerine oluşturmuş oluyoruz.
Bu sebepten hem Index ‘in boyutu büyüyor,hem bakım maliyetti artıyor hem de sorgu performansını kötü yönde etkiliyor.
Bu durumda devreye Filtered Index giriyor. SQL Server 2008 ile hayatımıza giren Filtered Index ,Optimize edilmiş bir NonClustered index ‘tir. Where anahtar kelimesi sayesinde index key’in bütün verilerini değil sadece alt kümesini içerir. İyi tanımlanmış bir Filtered Index performansı arttırabilir, bakım maliyetini düşürür, aynı zamanda filtered olduğu için diskte daha az yer kaplar.
Örneğimize dönecek olursak ;
Aşağıdaki gibi bir Filtered Index tanımlayalım ;
--Filtered Index Tanımlıyoruz. CREATE NONCLUSTERED INDEX SQLTRNCFI_SQLTURKIYE_Employees_Date ON SQLTURKIYE_Inquiry([Inquiry_Date]) WHERE [Inquiry_Date] IS NOT NULL; -- Where Bloğunda NULL olmayan kayıtları Indekslemesi için... GO
Görüldüğü üzere Index ‘imizi Anket Tarihi kolununda Null olmayan kayıtların üzerine tanımlamış olduk. Şimdi ise hem normal tanımladığımız NonClustered hemde Filtered Index üzerinden geçirelim sorgumuzu ve karşılaştıralım ..
CHECKPOINT; DBCC DROPCLEANBUFFERS; -- Cache 'i temizleyelim ... GO SET STATISTICS IO ON; SELECT * FROM SQLTURKIYE_Inquiry WITH ( INDEX ( SQLNCI_SQLTURKIYE_Employees_Inquiry_Date ) ) WHERE [Inquiry_Date] IS NOT NULL; SELECT * FROM SQLTURKIYE_Inquiry WITH ( INDEX ( SQLTRNCFI_SQLTURKIYE_Employees_Date ) ) WHERE [Inquiry_Date] IS NOT NULL; SET STATISTICS IO OFF;
Yukarıda Cachi temizledikten sonra aynı query ‘i iki ayrı index ‘üzerinden geçirerek IO değerlerine ulaştık.
NuLL kayıtlarıda içeren Index ‘imizin IO değeri 402 iken, Filtered Index ‘imizin IO değeri 217 ‘dir. Görüldüğü üzere sorgu performansımızı arttırmış olduk Filtered Index kullanarak.
Index’ lerimzi boyut bakımından karşılaştırdığımızda ise ;
SELECT ind.index_id , ind.name , ind.type_desc , par.reserved_page_count , par.used_page_count , par.row_count , ind.filter_definition FROM sys.dm_db_partition_stats par INNER JOIN sys.indexes ind ON par.object_id = ind.object_id AND par.index_id = ind.index_id WHERE par.object_id = OBJECT_ID('SQLTURKIYE_Inquiry');
Gördüğünüz gibi Filtered Index 100 kayıt içerirken normal NonClustered Index ‘imiz 1000 kayıt içermektedir. Filtered Index 2 Page ‘ten oluşurken diğer Index’imiz 19 Page ‘ten oluşmaktadır. Bu değerlere göre Filtered Index ‘imizin diğer index’ten boyutsal olarak daha küçük bir index olduğunu, sorgu performansı açısından daha verimli olduğunu ve bu indexlerin bakımları diğer indexlere göre daha hızlı ve maliyeti az olduğunu görmüş bulunmaktayız.
Teşekkürler..
İyi Çalışmalar.
Merhabalar Yusuf Bey,
Yazınız için teşekkürler.
Yazınızda “Yukarıda Cachi temizledikten sonra aynı query ‘i iki ayrı index ‘üzerinden geçirerek IO değerlerine ulaştık.” kısmı var. Burada cache’i temizledikten sonra iki sorguyu ardarda çalıştırıyorsunuz bu durumda gerçek IO değerlerine ulaşılamıyor. Cache temizlemeyi her sorgu öncesinde yapmanız gerekiyor diye düşünüyorum.
Sorgu şu şekilde olmalıydı:
CHECKPOINT
DBCC DROPCLEANBUFFERS — Cache ‘i temizleyelim …
GO
SET STATISTICS IO ON
SELECT * FROM SQLTURKIYE_Inquiry WITH(INDEX(SQLNCI_SQLTURKIYE_Employees_Inquiry_Date))
WHERE [Inquiry_Date] IS NOT NULL
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS — Cache ‘i temizleyelim …
GO
SELECT * FROM SQLTURKIYE_Inquiry WITH(INDEX(SQLTRNCFI_SQLTURKIYE_Employees_Date))
WHERE [Inquiry_Date] IS NOT NULL
SET STATISTICS IO OFF
GO
Merhabalar ,
Query ‘i iki ayrı Index üzerinden force ettiğimden dolayı farklı Execution Planlar kullanıldı. Bu yüzden Cache ‘i temizlemeye o kısımda gerek yok. Ancak Query ‘de ındex Force edilmiş olmasaydı yani aynı Execution planı kullanacağı bir senaryo olmuş olsaydı dediğiniz gibi her işlemimden önce Cache i temizleyip sonuçları bu şekilde karşılaştıracaktık.
Örneğimizde Cache ‘i her işlemden önce temizlemenin bir anlamı olmayacaktır.