0

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?

2
  • Think it this way: what will happen when data is being added to the table that used to be empty? Adjusting fill factor is seldom necessary.
    – vonPryz
    Oct 6 '21 at 7:45
  • Fill factor is applicable only when index is created, rebuilt or reorganized.
    – variable
    Oct 6 '21 at 10:13
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%.
4
  • Fill factor is applicable only when index is created, rebuilt or reorganized.
    – variable
    Oct 6 '21 at 10:13
  • I fail to see your intention here. Are you confirming my statement above? Or are you challenging it? Please clarify. Oct 6 '21 at 12:10
  • When table is empty, and I create an index with FF, then the FF will be useless until the 1st rebuild. Because inserts into the table will fill up entire page. Then when I do rebuild it will respect the FF. My question is - what is benefit of adding FF to empty table.
    – variable
    Oct 6 '21 at 12:46
  • Say that want your rebuild for this particular index to be done with 75% FF. Then you can create it with this value and just execute a "generic" rebuild command (not specifying FF in that command). I.e., you can have different FFs for different indexes and have those different FFs be applied using the same REBUILD command. Oct 6 '21 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.