Fill Factor

SQL Server’da performansı artırmak için oluşturduğumuz indexler diskte page’ler hâlinde saklandığını biliyoruz. Indexler SQL Server’in beygir gücüdür ancak indexleri oluştururken aynı zamanda yapılandırmasını da iyi yapmak gerekir. Tamamen indexlerle alakalı olan fill factor, indexlerin performansını artırmada yardımcı olabileceği gibi dikkatli şekilde ayarlanmadığında da ortalığı kasıp kavurabilir.

Fill factor nedir, nasıl ayarlanır?

Indexler oluşturulurken veriler en uç seviyede yani leaf level’daki 8K’lık page’lerde saklanır. Bir index oluşturduğumuzda veya rebuild ettiğimizde leaf level’daki bu page’lerin doluluk oranlarının belirlenmesine “fill factor” denir.  

Diyelim ki bir index’i rebuild ettiğinizde bu değeri 70 (yani %70) olarak ayarladınız. SQL Server bu indexi tekrar baştan oluştururken leaf level’daki her bir page’i %70 oranında doldurmaya çalışacaktır ve geri kalan %30’luk alanı boş bırakılacaktır.

Toplamda üç farklı yolla ayarlanabilir:

1. SQL Server instance düzeyinde
2. Index oluştururken
3. Index rebuild ederken
4. sp_configure ile

Server düzeyinde Object Explorer’dan server’iniz üzerine sağ tıklayıp Database Settings sekmesine geldiğinizde Default index fill factor değeri belirecektir. Varsayılan olarak bu değer 0’dır, yani page’ler tamamen doldurulur. (0 ve 100 değerleri aynı şekilde tam doluluk anlamına gelir.)

Index oluştururken veya rebuild ederken fillfactor seçeneği kullanılarak ayarlanır. Fillfactor = 70 diye belirtildiğindi bunun anlamı page’in %30’u boş kalmasıdır.

CREATE NONCLUSTERED INDEX [NCI_SalesOrderDetail_SalesOrderID] 
ON [Sales].[SalesOrderDetail]
( [SalesOrderID] ASC ) WITH ( FILLFACTOR = 70 )
GO

Index rebuild ederken FILLFACTOR seçeneği kullanılabilir.

ALTER INDEX [AK_SalesOrderDetail_rowguid] 
ON [Sales].[SalesOrderDetail] REBUILD
WITH (FILLFACTOR = 70, ONLINE = ON)
GO

Fill factor ayarlanmış bir index üzerinde reorganize işlemi gerçekleştirildiğinde, SQL Server bu işlemi önceden ayarlanmış olan fillfactor değerini dikkate alarak işlem gerçekleştirilecektir. Fakat reorganize yaparken fillfactor değeri ayarlanamaz, REORGANIZE ifadesinde böyle bir seçenek yoktur. Aşağıdaki işlem hata verecektir.

ALTER INDEX [IX_SalesOrderDetail_ProductID] 
ON [Sales].[SalesOrderDetail] REORGANIZE  
WITH (FILLFACTOR = 70)
GO

Son seçenek olan sp_configure ile fillfactor seçeneğinin ayarlanması önerilmemektedir.

Not olarak, fill factor etkin olan bir index’e eklenen yeni page’lerde fill factor oranı dikkate alınmaz. Örneğin bir index %70 doluluk oranıyla oluşturulmuş olsun, yeni satır insert ettiğmizde eklenecek olan yeni page’ler olabildiğince doldurulur. Bunun sebebi, fill factor özelliğinin sadece index oluşturulduğunda ve rebuild edildiğinde uygulanmasıdır.

Performansa etkisi nedir?

Fill factor ayarı varsayılan olarak (%100’e) ayarlansın. Yani page’ler tamamen doldurulmuş olsun. Bu durumda tabloya değerlerin arasına yeni kayıt insert ettiğimde “kötü huylu” page split denen işlem uygulanır. Bu adımlar şöyle özetlenebilir:

• Indexin mantıksal sırasına yeni page sıkıştırılır. Bu işlem, indexin mantıksal yapısını bozar
• Verinin yarısı bir page’de, diğer yarısı diğer page’de saklanır
• Page’ler arasındaki bağlantılar güncellenir

İşte bu kötü huylu page split kaynakları tüketir, fragmantasyona sebep olur ve performansa birçok yönde negatif etkisi vardır. Ekstra transaction log kaydı olur. Yani primary üzerinde gerçekleşti bu işlem diyelim. Transaction log secondary’e gönderilecek. Bu işlemlerin aynısı secondary üzerinde birebir gerçekleşecektir.

İşte bu anlattığım şeyler üzerine fillfactor performansa etkisi yadsınamaz. Bazı indexlerde fillfactor etkinleştirilerek performans etkisi gerçekten de gözlemlenbilir. Peki geniş perspektiften baktığımızda fillfactor değerini düşürdüğümüzde sunucu üzerinde neler gerçekleşir:

• Page doluluk oranı düşüyor ama veri boyutu aynı, sonuç olarak diskte daha fazla 8KB’lık yer açılır
• RAM’de boş alan miktarı azalır. Daha fazla page, daha fazla caching demektir. O page’lerin belirli bir yüzdesi boş olduğu için RAM boşa kullanılmış olacak
• Önceki ikisini göz önüne aldığımızda, daha fazla page olacağı için lock gibi daha fazla sistem kaynağı tüketilmiş olacak bu sebeple sorgular ve benzeri işlemler daha uzun sürecek

Tüm bunları göz önüne aldığımızda fillfactor değerini 80’nin altına indirdiğimizde birçok problemle karşılacabileceğimiz anlamına geliyor.

Öneriler

• Sunucu düzeyinde fillfactor ayarlaması yapılmamalı
• Sunucunun yöneticiliğine yeni başlandıysa hemen fillfactor değerleriyle oynanmaması önerilmektedir. İlk başlarda sistem sürekli izlenmeli ve kademeli olarak azaltma yoluna gidilmelidir
• Index oluştururken ve rebuild yaparken değeri dikkatli seçmelisiniz. Reconfigure ile fillfactor değerini baştan ayarlamak mümkün değil. Büyük sunucularda hemen index rebuild yaparak fillfactor değerini sık sık değiştirme gibi bir şansınız olmayabilir
• Düzenli index bakımlarının yapılmalıdır  (Ola Hallengren)
• Bir veritabanı read-only olacak şekilde arşivlenmek isteniyorsa fillfactor değerini 100’e ayarlayarak sorguların çok hızlı çalışması sağlanabilir

Fill Factor Kontrolü

Aşağıdaki script ile server düzeyinde bilgi alınabilir:

SELECT * FROM sys.indexes 
WHERE fill_factor < 80 AND fill_factor <> 0
AND is_disabled = 0 AND is_hypothetical = 0;

Server düzeyinde fillfactor değeri ayarladıktan sonra index düzeyinde fillfactor ayarlanabilir. Artık o index için index düzeyindeki fillfactor geçerli olacaktır.

Fill factor oranını belirlemek için daha çok page split oranı gözetilmelidir. Rowstore index’ler için sys.dm_db_index_physical_stats, columnstore index’ler içinse sys.column_store_row_groups DMF’leriyle page density değerine bakılarak page split oranına göz atılabilir ve duruma göre rebuild/reorganize işlemi gerçekleştirilebilir.

Ayrıntılı olarak sonuç alabileceğiniz sp_BlitzIndex kullanabilirsiniz.

Kaynak:
5 Things About Fillfactor
What is Fill Factor? Index, Fill Factor and Performance, Part 1