Sql-server – Fillfactor is ignored when non-clustered index added

fill-factorindexsql server

I have a test scenario where I have added fill-factor = 90 to a clustered index over a random generated GUID primary key.

If I create the table without fill-factor = 90 then the number of page splits is initially 31 for 3974 rows and splits occur with every subsequent insert. When I include the fill-factor = 90 the number of splits on population rises to 35 but splits stop happening with every subsequent insert. All good so far!

The problem is that when I add a non-clustered index although the clustered index still indicates a fill-factor of 90% the initial insert is back to 31 splits and splits are happening again on every subsequent insert indicating that the fill-factor is being ignored.

Does anyone have an idea as to why this might be happening?

    /*
    scenarios:
    1. Create table #Test02 without fillfactor, populate and observe 37 page splits occured, insert two rows at a time and observe page splits occurring
    2. Drop and create table #Test02 with filfactor = 90, populate and observe 40 page splits occurred, insert two rows at a time and observe that page splits do not occurr
    3. Drop and create table #Test02 with filfactor = 90, insert two rows then populate, observer 38 page splits occurred, insert two rows at a time and observe page splits occurring
    4. Drop and create table #Test02 with filfactor = 90, create index IX_#Test02_ProductName, populate and observe 37 & 35 page splits occurred, insert two rows at a time and observe that page splits occur on PK
    5. Rebuild index PK_Test02, insert two rows at a time and observe that page splits do not occurr

    Conclusion: On an empty table with a PK FF = 90 SQL preserves the fill factor
    */

    --non-sequential index table
    IF OBJECT_ID('tempdb.dbo.#Test02') IS NOT NULL DROP TABLE #Test02
    --CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID)) 
    CREATE TABLE #Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID) WITH (FILLFACTOR = 90))

    --ALTER INDEX PK_Test02 ON #Test02 REBUILD WITH (FILLFACTOR = 90);  

    IF EXISTS (SELECT * FROM tempdb.sys.indexes WHERE name = N'IX_#Test02_ProductName') DROP INDEX IX_#Test02_ProductName ON #Test02
    CREATE INDEX IX_#Test02_ProductName ON #Test02(ProductName)

    --populate script
    INSERT #Test02(ProductName)
    SELECT TOP 4000 COALESCE(O1.name,O2.name)
      FROM master.sys.objects O1
CROSS JOIN master.sys.objects O2

    --two row insert
    INSERT #Test02(ProductName) VALUES(N'Straight Banana'),(N'Bent Banana')

    --observe page splits
    SELECT ios.index_id
         , o.name as object_name
         , i.name as index_name
         , ios.leaf_allocation_count as page_split_for_index
         , ios.nonleaf_allocation_count page_allocation_caused_by_pagesplit
         , ios.leaf_insert_count
         , i.fill_factor
      FROM tempdb.sys.dm_db_index_operational_stats(db_id(N'db_name'), null, null, null) ios
      JOIN tempdb.sys.indexes i on ios.index_id = i.index_id AND ios.object_id = i.object_id
      JOIN tempdb.sys.objects o on ios.object_id = o.object_id
     WHERE o.type_desc = N'user_table' 
       AND o.name like N'#test02%'

Best Answer

First use permanenet table in this example.temp table show so many rows in this query "sys.dm_db_index_physical_stats" that it get confusing.

  1. Create table Test02 without fillfactor, populate and observe 37 page splits occured, insert two rows at a time and observe page splits occurring

Our example here is different from that of @sepupic,we are using uniqueidentifier and nvarchar(150) in our example.and in @sepupic example it is uniqueidentifier and int which matter becasue of data type.

CREATE TABLE Test02(ID uniqueidentifier default newid(), ProductName nvarchar(150), CONSTRAINT PK_Test02 PRIMARY KEY CLUSTERED (ID))

My intitial page count=28 and increase after every 2 rows insert.This may not happen if it was int becasue int is far smaller than nvarchar.

Here page split will keep happening after every insert,rows are added at end,which may not be true picture of page count.

I think this is called outdated statistics.

Once we rebuild index page count is back to 28 in my case.Note i added only 4-6 extra rows after that.I would have added again 1000 of rows second time,rebuild index

will sure show more page count than 28.

I think this is called updated statistics and query plan will reflect more accurate .

  1. Drop and create table #Test02 with filfactor = 90, populate and observe 40 page splits occurred, insert two rows at a time and observe that page splits do not occurr.

  2. Drop and create table #Test02 with filfactor = 90, insert two rows then populate, observer 38 page splits occurred, insert two rows at a time and observe page splits occurring

No my intital page count is 28 with first 4000 insert,thereafter page count keep increasing like 29,30 after every 2 insert.

After I rebuild index than page coutn changes to 31 becasue fill factor is 90,it has to leave 10% empty.

  1. Drop and create table #Test02 with filfactor = 90, create index IX_#Test02_ProductName, populate and observe 37 & 35 page splits occurred, insert two rows at a time and observe that page splits occur on PK.

Does anyone have an idea as to why this might be happening?

After first 4000 rows insert,CI page count =28,Non CI=26.

Two more insert CI page count =30,Non CI=28.

Two more insert CI page count =32,Non CI=28.

Two more insert CI page count =34,Non CI=28.

-------- and so on

No change in Non CI because fill factor is default 0 or 100 and page is still able to accomodate record in same page.

Also uniqueidentifier are 16 bytes and nvarchar are smaller than 16 bytes.So page split happen for CI but not for NON CI for only some more rows.

If ID was int then page count would have been less and also there will be no page split for smaller insert .

But if add more rows say again 4000 then Non CI page count changes to 82.

After rebuild index CI page count =61,Non CI=53.

Conclusion : FF of one index do not affect other in no way.Each index page split happen because of its own specified FF and also what data type it belong to.