Filtered Index
Filtered Index, SQL Server 2008 ile gelen, optimize edilmiş bir nonclustered index’tir. Oluşturulurken kullanılan 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.
Filtered Index’in sağladığı avantajları detaylı olarak analiz etmek gerekirse;
-
Filtered Index Sorgu Performansını Arttırır : Filtered Index normal Index’e oranla daha az data içerdiği için bu Index üzerinden yapılacak seek/scan işlemleri normal Index üzerinden yapılacak seek/scan işlemlerine oranla daha performanslı çalışacaktır. Ayrıca Filtered Index için otomatik olarak oluşacak olan istatistik te veriyi daha iyi temsil edeceğinden estimated rows daha iyi tahmin edilecek, dolayısıyla da Index’e erişim metodu daha iyi belirlenebilecektir.
-
Index Bakım Maliyetlerini Düşürür : Index fragmante olduğu zaman bakım yapılarak bu fragmantasyonun giderilmesi gerekir. (Detaylı bilgi için şu makaleyi inceleyebilirsiniz.) Index fragmantasyonu tabloda DML işlemleri olduğu zaman oluşur. Filtered Index normal NonClustered Index’e oranla daha az veri içerdiği için fragmante olma ihtimali daha azdır. Ayrıca fragmante olsa dahi bakım işlemi sırasında daha az data ile uğraşılacağı için bakım maliyeti her halukarda normal index’e oranla daha az olacaktır.
-
Disk Maliyetini Düşürür : Daha önceki maddelerde de belirttiğim gibi Filtered Index datanın tamamını içermediği için normal Index’e oranla daha az yer kaplar.
Teorik kısmı burada kesip Filtered Index’i kafamızda daha iyi canlandırmak amacıyla canlı bir örnek verelim. Örneğin tbl1 isimli tablonuzda bulunan col1 adlı kolona index tanımlaması yapıyorsunuz. Tablo toplamda 1000 kayıttan oluşuyor. Fakat bu 1000 kaydın 700’ü için col1 kolonunda NULL bilgisi bulunuyor. Siz Col1 üzerinden yaptığınız bütün aramalarda null olmayan bir değer ile arama yapıyorsunuz. Böyle bir senaryoda col1’in tüm değerleri için Index tanımlamak yerine NULL olmayan satırlar için Index tanımlamanız daha anlamlı olacaktır. Bu şekilde 1000 değerden oluşan bir Index yerine 300 değerden oluşan bir Index olacak, bunun neticesinde B-Tree küçülecek ve Index üzerinden yapılan aramalar daha performanslı olacaktır. Ayrıca Index bütün kayıtlar için değil sadece 300 kayıt için oluştulduğu için diskte daha az yer kaplayacak ve Index maliyeti de otomatikman düşecektir.
Şimdi yukarıda bahsettiğim örneği SQL Server’da gerçekleştirelim ve 2 Index arasındaki farklara bakalım.
Bunun için ilk olarak 1.000.000 satırdan oluşan bir tablo oluşturacağım ve 700.000 kaydın col1 kolonuna null değer atacağım. Kalan 300.000 kaydın col1 kolonunu NewID() fonksiyonundan gelen GUID değeri ile dolduracağım.
--Bir çalışma DB'si oluşturuyoruz.
create database DBFilterIndex
GO
use DBFilterIndex
GO
--Bir çalışma tablosu oluşturuyoruz
create table tbl_FilterIndex (ID int Identity(1,1), col1 varchar(37))
GO
--col1 bilgisi NULL olan 700.000 kayıt insert ediyoruz.
insert tbl_FilterIndex
select null
go 700000
--col1 bilgisi NULL olmayan 300.000 kayıt insert ediyoruz.
insert tbl_FilterIndex
select cast(NEWID() as varchar(37))
go 300000
1.000.000 kayıttan oluşan çalışma tablomuz hazır. Şimdi Index’leri oluşturalım.
--ID kolonu üzerine Clustered Index oluşturuyoruz
create clustered index CIX on tbl_FilterIndex (ID)
GO
--col1 için normal bir NonClustered Index oluşturuyoruz
create nonclustered index IX_RegularIndex on tbl_FilterIndex (col1)
GO
--col1 için Filtered NonClustered Index oluşturuyoruz
create nonclustered index IX_FilterIndex on tbl_FilterIndex (col1)
where col1 is not null
GO
Şimdi 2 Index’i birbiri ile karşılaştırabiliriz.
İlk olarak 2 index’in kayıt sayılarını ve boyutlarını karşılaştıralım.
SELECT i.index_id, i.name, i.type_desc, ps.reserved_page_count, ps.used_page_count,
ps.reserved_page_count*8 as Size_KB,
ps.row_count, i.filter_definition
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.object_id = OBJECT_ID('tbl_FilterIndex')
Resimde de gördüğünüz üzere normal NonClustered Index 1.000.000 kayıttan oluşurken Filtered Index’te null olan kayıtlar bulundurulmadığı için 300.000 kayıttan oluşmakta. Bu yüzden normal index 22 MB iken Filtered Index 15 MB boyutunda. Disk açısından maliyetin nasıl düşürüldüğünü görmüş olduk.
Şimdi bir de performans karşılaştırması yapalım. Bu karşılaştırma için aynı sorguyu 2 farklı index’i force ederek yapacağım. Aynı zamanda farkı daha net görebilmnek adına Index’i scan edecek bir kriter gireceğim.
SET STATISTICS IO ON
--Normal NonClustered Index'i force ederek
select * from tbl_FilterIndex WITH (INDEX=IX_RegularIndex)
where col1=NEWID()
--Filtered Index'i force ederek
select * from tbl_FilterIndex WITH (INDEX=IX_FilterIndex)
where col1=NEWID()
IO karşılaştırmasına baktığımızda normal Index sorguyu tamamlamak için 2749 IO yaparken Filtered Index aynı sorguyu 1879 IO yaparak getirebilmekte.
Query Plan’ları karşılaştırarak maliyetlere bakacak olursak;
Resimde de görüldüğü üzere normal Index’in maliyeti 4 kat daha fazla. Bu örneklerle de Filtered Index’in performansa olan etkisini görmüş olduk.
Filtered Index’te desteklenen özelliklere bakacak olursak;
-
Filtered Index’in kriteri değiştirilebilir.
-
Missing Index DMV’leri Filtered Index önerisi toplamaz.
-
Database Engine Tuning Advisor “not null” Filtered Index önerisi sunabilir.
-
Filtered Index, online Index operasyonunu destekler. Yani Filtered Index’ler online olarak Rebuild edilebilirler.
-
Table Hint’ler Filtered Index tarafından desteklenir.
SQL Server 2008 ile beraber gelen Filtered Index özelliği Performance Tuning çalışmaları yapılırken göz önünde bulundurulması gereken güzel özelliklerden biri. Filtered Index sayesinde verinin alt kümesi için Index tanımlaması yapılarak performans artışı sağlanabilir.
İlişkili Yazılar
-
Ekleyen:
Turgay Sahtiyan Microsoft Senior SQL Server PFE
-
Ekleyen:
Turgay Sahtiyan Microsoft Senior SQL Server PFE
-
Ekleyen:
Turgay Sahtiyan Microsoft Senior SQL Server PFE