Mehedi's Earth

Recently, some indexes of my production server were fragmented frequently. There were millions of INSERT and UPDATE operations in those tables. Even after rebuilding INDEX, it was fragmented 90% within 2-3 hours. As a result, queries were running slowly and IO operations were also increased. Details about INDEX fragmentation, it's causes, effects and INDEX Rebuild/Reorganize process were covered in my previous article. Here, you will see how tweaking fill factor can address index fragmentation. For this article, AdventureWorks2022 database is used.

Detecting INDEX Fragmentation

You can use below query to detect Fragmentation:

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent, ips.avg_page_space_used_in_percent,
       ips.page_count,ips.fragment_count, ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY page_count DESC;

From SSMS you can also identify INDEX Fragmentation for a particular, Table ->Indexes->Rebuild.

Diagram: Checking Index fragmentation

Tweaking Fill Factor

How much of a page will be filled by rows. For example if fill factor is 100 (or 0, which is equivalent in this context), page will be filled up 100%. Starting with fill factor 100, monitor the fragmentation levels, and gradually adjust the value down until you find an optimal one. Please be informed that changing the fill factor can decrease read performance inversely. Because the index spreads over more pages which requires more IO to retrieve the data. For example, a fill-factor value 50 means, Index will occupy two times more pages. Read operations need double time.

You can adjust fill factor from Table Designer menu -> Indexes/Keys -> Fill Specification -> Fill Factor, and put your desired value then close it.

Diagram: Tweaking fill factor

Last words, before changing the fill factor, please make sure that you have analyzed the Read IO impacts. After tweaking, just check whether there is any changes in performance