Fillfactor is ignored when non-clustered index added

Question

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%'

Answer 1

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 .

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

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.

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.

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.

Answer 2

Fillfactor can be set once over the server (so it will be used in all newly created indexes unless another FF is explicitly specified in CREATE INDEX statement) or it can be defined individually for each index.

So if you want your non-clustered index to have FF = 90 just specify it when creating it.

FF of clustered index has no relation to FF of non-clustered indexes defined over that clustered index.

In other words, when creating an index, the specified FF is used. If nothing is specified, server default FF is used. In your case server default FF is 100%.

............................................................................

It seems I understood your question only now. You are asking about why don't they maintain FF when INSERTs happen to your table.

Here is documentation:

Specify Fill Factor for an Index

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

So, fillfactor (the percentage of fullness of the leaf level) is maintained only when you create or recreate your index. When you subsequently use your table (make INSERTs or UPDATEs) your index leaf level becomes full. It's normal, It does not depend on non-clustered index existence, it's just how it should be. The empty space created at index creation time serves to mitigate page splits, but if you want to maintain it just to maintain empty space it just has no sense.

UPDATE

It would seem that if an empty table only has a clustered index then SQL will maintain fill factor on initial population but not if it has a non-clustered index as well.

You still do not understand how FF works. When you define it on an index, i.e. the index is created, the FF is respected. This index will be populated with this FF in mind. The next time it will be respected is only when the index is rebuilt.

So when you define FF on a table containing rows, these rows will be put in index with respect of the FF defined.

If you define the FF in an index created on empty table, nothing happens, there are no rows, how can you leave empty space between them?

I wrote a small repro where you can clearly see that no FF is maintained when you define it in the index created on an empty table.

This does not depend on any non-clustered index, it's just how FF works:

create table dbo.nums_guid (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid(id) with (fillfactor = 50);

insert into dbo.nums_guid (id, n)
select newid(), n
from s1057.dbo.nums;

create table dbo.nums_guid_1 (id uniqueidentifier, n int);
create unique clustered index ix_id on dbo.nums_guid_1(id) with (fillfactor = 50);
create unique nonclustered index ix_n on dbo.nums_guid_1(n);

insert into dbo.nums_guid_1 (id, n)
select newid(), n
from s1057.dbo.nums;

select object_name( object_id) as tbl_name,
       object_id, 
       index_id, 
       page_count, 
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0


select object_name( object_id) as tbl_name,
       object_id, 
       index_id, 
       page_count, 
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0

There are 2 tables in my repro, both with FF = 50 defined: one has a clustered index only and another clustered + non-clustered indexes. In both cases clustered index is defined on empty table. When I do my INSERT, no FF is maintained:

And only after rebuild I'll have FF = 50 on both clustered (and still it does not depend on existence of non-clustered index):

--------
alter index ix_id on dbo.nums_guid rebuild;
alter index ix_id on dbo.nums_guid_1 rebuild;

select object_name( object_id) as tbl_name,
       object_id, 
       index_id, 
       page_count, 
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0


select object_name( object_id) as tbl_name,
       object_id, 
       index_id, 
       page_count, 
       avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.nums_guid_1'), NULL, NULL , 'detailed')
where index_id = 1 and index_level = 0