SQL Server Filtered INDEX

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.