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.
