SQL Server: how much index growth is expected after applying fill factor less than 100%

Question

I have about 250 GB of indexes in a database with fill factor 100%. To improve the performance I want to reduce fill factor up to 95%.

So before applying the fill factor to my indexes I wanted to know how much the index size will grow after applying 95% of fill factor.

What I am expecting is that total 5% of index size will grow is that correct?

Answer 1

I was thinking about this last night and logically it would make sense so i decided to test it:

--Create Testing table

CREATE TABLE TableIndexTest (id INT, id2 INT, var VARCHAR(100))

--Populate with some meaningful data

DECLARE @num     INT = 1000000
DECLARE @var     VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @num2    INT = 1000000
DECLARE @iter    INT = 1000000
DECLARE @num_curr INT = 1
WHILE (@num_curr <= @num)
BEGIN
    INSERT TableIndexTest (id, id2, var) SELECT @num_curr, @num_curr, @var
    SELECT @num_curr = @num_curr + 1
END

--Create some index with fillfactor

CREATE NONCLUSTERED INDEX ix_test_fillfactor100 ON TableIndexTest ([id], [id2], [var])
CREATE NONCLUSTERED INDEX ix_test_fillfactor90 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 90)
CREATE NONCLUSTERED INDEX ix_test_fillfactor80 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 80)
CREATE NONCLUSTERED INDEX ix_test_fillfactor70 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 70)
CREATE NONCLUSTERED INDEX ix_test_fillfactor60 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 60)
CREATE NONCLUSTERED INDEX ix_test_fillfactor50 ON TableIndexTest ([id], [id2], [var]) WITH (FILLFACTOR = 50)

--Check index Sizes

SELECT
    i.name AS IndexName,
    SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = OBJECT_ID('TableIndexTest')
GROUP BY i.name
ORDER BY SUM(s.used_page_count) * 8;

Expected outcome would be that the index will FillFactor set to 50 would be half the size of the index with no fillfactor set (Server default is 0 so would essentially be 100).

Results:

    IndexName              IndexSizeKB
ix_test_fillfactor100         52008
ix_test_fillfactor90          57176
ix_test_fillfactor80          64480
ix_test_fillfactor70          73952
ix_test_fillfactor60          85752
ix_test_fillfactor50          103328

So FillFactor 50 * 2 = 104,016 when expected would be 103,328 but taking into account that the last page of the index could potentially only have 1 row on it I think its pretty close so you can safely use that calculation