Is there any benefit to specifying a fill factor when the table is empty?

Question

When the table has lots of data, and I create a new index with fill factor (say 30), then sql server will leave space for new entries to be added into the data page. This will keep fragmentation low when new inserts are made.

But if the table is empty, and I create an index, then what is the benefit of adding fill factor to the index?

Answer 1

It depends on if and how you defragment this index. (Which might not be something you want to do in the first place, but that is a different discussion.)

The FF value doesn't affect the modifications in any way, the pages will fill up as you do inserts and this will not be limited by the specified FF value (that would make FF useless).

ALTER INDEX REORAGINZE should not change the FF value compared to prior the reorg (depending on how you interpret the documentation). The documentation is vague and I noticed that in some cases the reorg did actually reapply the value I specified when I created the index. I.e. I see some inconsistency here, possibly due to a bug in SQL Server.

ALTER INDEX REBUILD seem to consistently re-apply the value you specified when you created the index.

Here's something to play with:

USE tempdb

DROP TABLE IF EXISTS PhoneLog

--Note 20% for the FF value
CREATE TABLE dbo.PhoneLog
( PhoneLogID int IDENTITY(1,1) NOT NULL,
  LogRecorded datetime2 NOT NULL INDEX IX_PhoneLog_LogRecorded,
  PhoneNumberCalled nvarchar(200) NOT NULL,
  CallDurationMs int NOT NULL
)

CREATE CLUSTERED INDEX PK_PhoneLog ON PhoneLog(PhoneLogID) WITH FILLFACTOR = 20 

--Check the "saved" FF value that you specified at create-time
SELECT fill_factor FROM sys.indexes 
WHERE object_id = OBJECT_ID('PhoneLog')
AND type_desc = 'CLUSTERED'

--Empty, (re)-load and fragment data
TRUNCATE TABLE PhoneLog
INSERT PhoneLog (LogRecorded, PhoneNumberCalled, CallDurationMs)
SELECT TOP(100000) SYSDATETIME(),'999-9999',CAST(RAND() * 1000 AS int)
FROM sys.columns AS a, sys.all_columns AS b
UPDATE dbo.PhoneLog 
SET PhoneNumberCalled = PhoneNumberCalled + REPLICATE('x', 180)
WHERE PhoneLogID % 2 = 1
GO

--Check FF (avg_page_space_used_in_percent), should be about 54% after Empty, (re)-load and fragment data
--(Frag should be about 40%)
SELECT
 DB_NAME(database_id)
,OBJECT_SCHEMA_NAME(object_id, database_id)
,OBJECT_NAME(object_id, database_id)
,index_id
,avg_fragmentation_in_percent
,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('PhoneLog'),NULL,NULL,'DETAILED') 
WHERE index_level = 0
AND index_id = 1


--Perform reorg
ALTER INDEX ALL ON dbo.PhoneLog REORGANIZE
--Check FF using above query. Should be same as before according to the documentation.
--(I noticed that it sometimes becomes 20% as per the value specified when we created the index)

--Empty, (re)-load and fragment data using above SQL.

--Perform rebuild
ALTER INDEX ALL ON dbo.PhoneLog REBUILD
--Check FF using above query. Should be about 20%.

--Empty, (re)-load and fragment data using above SQL.

--Perform rebuild with FF specified to 80%
ALTER INDEX ALL ON dbo.PhoneLog REBUILD WITH(ONLINE = ON, FILLFACTOR = 80) 
--Check FF using above query. Should be about 80%.