Which Fillfactor should I use to improve performance?

The performance of your SQL Server database will depend on multiple factors. One of these factors is index fragmentation. Fragmentation of indexes will slow down performance because more page should be read. This blog post will talk about how some index fragmentation can be avoided by using the fill factor option in the index definition. By default all index information is stored in pages. During the creation of the index the storage engine will make the index leaf level pages 100% full. In other words leaving no space. This means that a random insert, or a record that increases in size, will result in a page splitt. Now fragmentation of your index is started. Random inserts will occur if the first column of an index will have random values for instance an GUID. The more page splitts occur on your index, the more fragmentation you have on this index. Page splitts can be avoided if there is enough space in the page to insert the new index records. By using the fill factor option in the configuration of your index, the storage engine will leave some free space in the leaf level pages during index build or rebuild operations. A fill factor of 90 means that 90 % of the index pages will be used and 10% is free index space.

The fill factor to use depends on the percentage of random inserts, or records that increase in size in the periode between 2 index defragmentations periods. For instance if you defrag your indexes every week, you need to know the percentage of index insert during a week.

First of all I will use a script to show the index fragmentation. I will create a table and a stored procedure to insert records and I will create 4 indexes with a GUID as the first column to generate random inserts.

-- Create a test table
CREATE TABLE FillFactorTest
   (Division INT,
    HID INT Identity(1,1) NOT NULL,
    TestINT INT NULL,
    TestGuid [uniqueidentifier] ROWGUIDCOL NOT NULL,
    TestChar CHAR(760) NULL)
GO
CREATE CLUSTERED INDEX PK_FillfactorTest ON FillfactorTest(Division, HID)
GO

Now we have a test table: FillFactorTest which need to get data. I will use a stored procedure for this. This stored procedure can be used to add data in batches.

CREATE PROC [dbo].[FillFactorinsert](@Division INT, @NumberInserts INT) AS
  DECLARE @COUNTER INT
  DECLARE @TestINT INT
  SET @Division = 100
  SET @TestINT = 2
  SET @COUNTER = 1
  INSERT INTO FillFactorTest (Division,TestINT,TestGuid,TestChar)
    Values (@Division,@TestINT,NEWID(),'Pipo')
  WHILE @COUNTER < @NumberInserts
  BEGIN
     INSERT INTO FillFactorTest (Division,TestINT,TestGuid,TestChar)
        Values (@Division,@TestINT,NEWID(),'Pipo')
  SET @TestINT = @TestINT +1
  SET @COUNTER = @COUNTER + 1
  END
GO

-- Now it is time to insert records
-- Insert first set of 10000 records for Division 100
EXEC FillFactorinsert 100,10000
GO
-- Insert first set of 2500 records for Division 200
EXEC FillFactorinsert 200,2500
GO
-- Insert first set of 7000 records for Division 300
EXEC FillFactorinsert 300,7500
GO

-- Now we have added 20.000 records to the table FillFactorTest
SELECT COUNT(*) FROM FillFactorTest

-- Now it is time to add indexes. We will add 4 indexes on which only the fillfactor is different.
-- Create 4 times the same index on only guid only fillfactor is different
CREATE INDEX TestEntryGUID ON FillfactorTest(Testguid)
CREATE INDEX TestEntryGUIDFILL98 ON FillfactorTest(Testguid) WITH (FillFactor = 98)
CREATE INDEX TestEntryGUIDFILL95 ON FillfactorTest(Testguid) WITH (FillFactor = 95)
CREATE INDEX TestEntryGUIDFILL90 ON FillfactorTest(Testguid) WITH (FillFactor = 90)


-- Use next query to retrieve the fragmentation of these indexes.
-- Defragmentation percentage of indexes in a table
SELECT index_name AS IndexName,MAX(Fill_factor) AS Fill_factor
     ,SUM(avg_fragmentation_in_percent) AS Fragmentation_Percent
FROM (
    SELECT
      SI.[name] AS index_name
      , SDDIPS.[avg_fragmentation_in_percent]
      , NULL as Fill_factor
    FROM sys.[dm_db_index_physical_stats](DB_ID(),OBJECT_ID('FillfactorTest') , NULL, NULL, 'Detailed') SDDIPS
    INNER JOIN sys.[indexes] SI
      ON SDDIPS.[object_id] = SI.[object_id] AND SDDIPS.[index_id] = SI.[index_id]
    WHERE SDDIPS.[index_id] > 1
    UNION ALL
    SELECT COALESCE(i.name, 'N/A') as index_name
      ,NULL AS avg_fragmentation_in_percent,i.fill_factor AS Fill_Factor
    FROM sys.tables t
    INNER JOIN sys.indexes i ON t.object_id = i.object_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius
       ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = db_id()
   WHERE t.object_id = OBJECT_ID('FillfactorTest') AND i.type = 2
) XX
GROUP BY index_name

You wil see that all indexes do not have any fragmentation.
Now it is time to insert additional records. Let's see when fragmentation occurs. We will do it in steps of 1 %.

Step 1:
-- Insert 1% new records.
EXEC FillFactorinsert 100,100
GO
EXEC FillFactorinsert 200,25
GO
EXEC FillFactorinsert 300,75
GO
SELECT COUNT(*) FROM FillFactorTest
GO
Step 2:
Re run the defragmentation script. You will see that the index with the default fill factor is full fragmented. (More than 95%)

Step3: repeat Step 1 and Step 2 for 4 times.
During these steps you will see when fragmentation start occuring on the different indexes. The results of these inserts are reflected in next graph.


Conclusion: Fragmentation (> 20%) will occur if 60% of the free space created by the fillfactor is used. This means that if you insert 5% new data you need a fill factor of at least (5/60)*100 = 8,3 Rounded to 10

Attention point: Using a fill factor will improve performance because it will avoid fragmentation. However you need to configure a defragmentation job to avoid fragmentation in the future. This means that if you insert 5 % new records per month, the defragmentation job should be scheduled once per month.