在添加非聚集索引时忽略填充因子。

Question

我有一个测试场景，通过随机生成的GUID主键将fill-factor = 90添加到聚集索引中。

如果我在不使用fill-factor = 90的情况下创建表，那么对于3974行，最初的分页数为31，每次插入时都会出现分页。当我包含fill-factor = 90时，人口上的分裂数增加到35，但是每次插入都不再发生分裂。目前为止一切都很好！

问题是，当我添加一个非聚集索引时，尽管聚集索引仍然表示90%的fill-factor，初始插入返回到31个拆分，并且在随后的每个插入上再次发生分裂，这表明fill-factor被忽略了。

有人知道为什么会发生这种事吗？

    /*
    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

首先，在这个example.temp表中使用permanenet表显示这个查询"sys.dm_db_index_physical_stats“中有这么多行，这使它变得很混乱。

1. 创建没有填充因子的表Test02，填充和观察37页拆分，一次插入两行，并观察分页的发生。

我们这里的示例与@sepupic的不同，我们在@sepupic示例中使用了unique标识符和nvarchar(150)，因为数据类型的关系，它是unique标识符和int。

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

我的初始页面count=28和增加后每2行insert.This可能不会发生，如果它是int，因为int远小于nvarchar。

在这里，页面拆分将在每次插入后持续发生，在结束时添加行，这可能不是页面计数的真实图片。

我认为这被称为过时的统计数字。

一旦我们重建索引页计数回到28在我的case.Note，我只增加了4-6多行之后，我会再次添加1000行第二次，重建索引

肯定会显示多于28页的页数。

我认为这被称为更新统计和查询计划将反映更准确。

1. 删除并创建带有filfactor = 90的表#Test02 02，填充并观察40页拆分发生，一次插入两行，并观察没有出现分页现象。
2. 删除并创建带有filfactor = 90的表#Test02 02，插入两行，然后填充，观察者发生38页拆分，每次插入两行，并观察页面拆分的发生

不，我的intital页数是28和头4000插入，此后页面数在每2次插入之后就会增加29,30。

当我重建索引比页面coutn更改为31，因为填充因子是90，它必须留下10%的空。

1. 删除并创建带有filfactor = 90的表# that 02，创建索引IX_#Test02_ProductName，填充并观察37 & 35页拆分发生，一次插入两行，并观察PK上发生的分页。

有人知道为什么会发生这种事吗？

插入前4000行后，CI页计数=28，非CI=26。

另外两个插入CI页面计数=30，非CI=28。

另外两个插入CI页计数=32，非CI=28。

另外两个插入CI页面计数=34，非CI=28。

-等等

非CI中没有任何更改，因为填充因子默认为0或100，并且页仍然能够容纳同一页中的记录。

另外，unique标识符是16个字节，nvarchar小于16 bytes.So页面拆分发生在CI，而非CI只发生在更多的行。

如果ID是int，那么页面计数就会更少，并且对于较小的insert也不会有分页。

但是，如果添加更多的行，比如再次添加4000，那么非CI页面计数将更改为82。

重建索引CI页面计数=61，非CI=53。

Conclusion：一个索引的FF不影响其他索引中的其他索引，因为它本身指定的FF以及它属于什么数据类型，因此不会发生way.Each索引页面拆分。

Answer 2

Fillfactor可以在服务器上设置一次(因此它将在所有新创建的索引中使用，除非在CREATE INDEX语句中显式指定另一个FF )，或者可以为每个索引分别定义它。

因此，如果您希望您的非聚集索引有FF = 90，只需在创建它时指定它。

聚集索引的FF与在该聚集索引上定义的非聚集索引的FF无关。

换句话说，在创建索引时，将使用指定的FF。如果未指定任何内容，则使用server default FF。在您的情况下，服务器默认FF是100%。

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

看来我现在才明白你的问题。你在问为什么当INSERT**s碰巧在你的桌子上的时候，他们不保持FF。

以下是文件：

为索引指定填充因子

填充因子选项用于微调索引、数据存储和性能。创建或重建索引时，填充因子值确定每个叶级页面上要填充数据的空间百分比，将每个页上的剩余部分保留为未来增长的空闲空间。例如，指定填充因子值为80意味着每个叶级页面的20 %将为空，为索引扩展提供空间，因为数据被添加到基础表中。空空间保留在索引行之间，而不是在索引末尾。

因此，只有在创建或重新创建索引时才会保持填充因子(叶级的丰满百分比)。当您随后使用您的表(使INSERTs或UPDATEs)，您的索引叶水平变得满。这是正常的，它不依赖于非聚集索引的存在，而是它应该是怎样的。在索引创建时创建的空空间可以减少页面分裂，但是如果您想维护它只是为了维护空空间，那么它就是没有意义的。

更新

看起来，如果一个空表只有一个聚集索引，那么SQL将在初始填充中保持填充因子，但如果它也有一个非聚集索引，则不会。

你仍然不明白FF是如何工作的。当您在索引上定义它时，即创建索引时，会尊重FF。此索引将使用此FF填充。下一次它将被尊重是只有当索引被重建。

因此，当您在包含行的表上定义FF时，这些行将被放入针对所定义的FF的索引中。

如果在空表上创建的索引中定义了FF，什么都不会发生，没有行，如何在它们之间留下空空间？

我编写了一个小的repro，您可以清楚地看到，当您在空表上创建的索引中定义FF时，没有任何FF被维护。

这不依赖于任何非聚集索引，它只是FF的工作方式：

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

在我的repro中有两个表，两个表都定义了FF = 50：一个只有clustered index，另一个只有clustered + non-clustered索引。在这两种情况下，聚集索引都定义在空表上。当我执行INSERT时，不维护FF：

只有在重建之后，我才会在两个集群上设置FF = 50 (而且它仍然不依赖于非聚集索引的存在)：

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