Fillfactor is ignored when non-clustered index added 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 &amp; 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%'
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