Filtered Index Nedir

Filtered Index filtrelenmiş bir nonclustered index’tir. Örneğin bir tabloda 100 bin kaydınız var. Ve bazı sorgularınız sadece bu 100 bin kaydın 20 bin tanesi ile ilgileniyor. Bu sorgular için tabloya nonclustered index koysanız bu index’in 100 bin tane kaydı olacaktır. Ama 80 bin tane kayıt ile ilgisi olmayan bir sorgu için 100 bin kayıtlı bir index oluşturmak mantıksız olacaktır. Burada filtered index imdadımıza yetişir. Ve index’i kullanacak sorgunun daha az kayıt sayısına sahip filtered index’i kullanarak daha performanslı çalışmasını sağlayabiliriz.

Filtered Index’in avantajlarını aşağıdaki birkaç madde ile sırayabiliriz.

• Daha az kayıt sayısı ile daha az storage kullanımı
• Daha az kayıt sayısı olduğu için üretilen execution plan daha kaliteli olur. Execution Plan ile ilgili detayları “Execution Plan Nedir” isimli makalemde bulabilirsiniz. Ayrıca filtered index filtered istatistiğe sahip olacaktır. Filtrelenmiş istatistikler full-table istatistiklere göre daha doğru sonuç verecektir. İstatistiklerle ilgili “SQL Server’da İstatistik Kavramı ve Performansa Etkisi” isimli makalemde daha detaylı bilgi bulabilirsiniz.

Kısaca daha kaliteli bir execution plan ve daha doğru sonuç veren bir istatistik kullanarak daha az kayıt üzerinde arama yapılacağı için performans artışı olacaktır.

• Daha az kayıt sayısı olduğu için bakım işlemleri daha kolay olacaktır.

Makalemize bir örnek yaparak devam edelim.

Test isimli veritabanımızda aşağıdaki script yardımıyla yeni bir tablo oluşturuyoruz ve bu tablonun içine birkaç tane kayıt insert ediyoruz.

CREATE TABLE [dbo].[FilteredIndexOrnekTable](33
[Ad] [varchar](50) NULL,
[Soyad] [varchar](50) NULL
) ON [PRIMARY]
USE [Test]
GO

INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES('Nurullah','ÇAKIR')
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES('Hakan','GÜRBAŞLAR')
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES('Faruk','ERDEM')
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)
INSERT INTO [dbo].[FilteredIndexOrnekTable]([Ad],[Soyad]) VALUES(NULL,NULL)

Script’te gördüğünüz üzere Ad ve Soyad bilgisi dolu olan 3 tane kayıt insert ettik. Diğer kayıtlarda ise Ad ve Soyad bilgisi null olarak insert edildi. Ad ve Soyad bilgisi ile sorgulama yapan bir sorgu olduğunu düşünün. Eğer Ad ve Soyad bilgisine normal nonclustered index koyarsak bu index’şteki kayıt sayısı tablodaki kayıt sayısı kadar olacaktır. Index’in içersinde Ad ve Soyad bilgisi boş olan bir sürü kayıt olacağı için index’in boyutu boş yere büyük olacak ve dolayısıyla performansıda kötü olacaktır. Şimdi aşağıdaki script ile içinde sadece Ad ve Soyad bilgisi dolu olan kayıtları tutan bir filtered index oluşturalım.

CREATE NONCLUSTERED INDEX DoluAdSoyad
    ON [dbo].[FilteredIndexOrnekTable](Ad, Soyad)
    WHERE Ad IS NOT NULL And Soyad IS NOT NULL ;

Karşılaştırma yapmak için aşağıdaki script ile filtered olmayan bir nonclustered index oluşturalım.

CREATE NONCLUSTERED INDEX AdSoyad
    ON [dbo].[FilteredIndexOrnekTable](Ad, Soyad)

Aşağıdaki script yardımıyla tablomuzda bulunan nonclustered index’lerdeki kayıt sayılarını kontrol edelim.

SELECT ind.name, dps.used_page_count, dps.row_count, ind.filter_definition
FROM sys.dm_db_partition_stats dps
INNER JOIN sys.indexes ind ON dps.object_id = ind.object_id AND dps.index_id = ind.index_id
WHERE dps.object_id = OBJECT_ID('FilteredIndexOrnekTable') and ind.type_desc='NONCLUSTERED'

Gördüğünüz gibi AdSoyad isimli normal nonclustered index üzerinde 10 kayıt varken sadece dolu olan Ad Soyad kayıtlarını barındıran Filtered Index’te 3 kayıt var.

Aşağıdaki script yardımıyla filtered index’i silebilirsiniz.

USE [Test]
GO
DROP INDEX [DoluAdSoyad] ON [dbo].[FilteredIndexOrnekTable]