添加非聚集索引時忽略填充因子

我有一個測試場景，我fill-factor通過隨機生成的 GUID 主鍵將 = 90 添加到聚集索引中。

如果我創建沒有fill-factor= 90 的表，那麼對於 3974 行，頁面拆分數最初為 31，並且每次後續插入都會發生拆分。當我包含fill-factor= 90 時，人口分裂的數量上升到 35，但每次後續插入都會停止發生分裂。到目前為止一切都很好！

問題是，當我添加一個非聚集索引時，儘管聚集索引仍然指示fill-factor90% 的初始插入返回到 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%'

在此範例中首先使用 permanenet 表。臨時表在此查詢“sys.dm_db_index_physical_stats”中顯示瞭如此多的行，以至於令人困惑。

1. 創建沒有填充因子的表 Test02，填充並觀察發生 37 個頁面拆分，一次插入兩行並觀察發生的頁面拆分

我們這裡的範例與@sepupic 的範例不同，我們在範例中使用了 uniqueidentifier 和 nvarchar(150)。在@sepupic 範例中，由於數據類型的原因，它是 uniqueidentifier 和 int。

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

我的初始頁數 = 28 並在每插入 2 行後增加。如果它是 int 可能不會發生，因為 int 遠小於 nvarchar。

每次插入後頁面拆分都會繼續發生，最後添加行，這可能不是頁數的真實情況。

我認為這被稱為過時的統計數據。

在我的情況下，一旦我們重建索引頁數回到 28。請注意，在那之後我只添加了 4-6 額外的行。我會再次添加 1000 行第二次，重建索引

肯定會顯示超過 28 頁的頁數。

我認為這被稱為更新的統計資訊和查詢計劃會體現得更準確。

2. 刪除並創建 filfactor = 90 的表#Test02，填充並觀察發生 40 個頁面拆分，一次插入兩行並觀察未發生頁面拆分。
3. 刪除並創建 filfactor = 90 的表#Test02，插入兩行然後填充，觀察者發生 38 個頁面拆分，一次插入兩行並觀察發生的頁面拆分

不，我的初始頁數是 28，前 4000 次插入，此後頁數不斷增加，每 2 次插入後增加 29,30。

在我重建索引後，頁面計數更改為 31，因為填充因子為 90，它必須保留 10% 為空。

4. 刪除並創建 filfactor = 90 的表 #Test02，創建索引 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，並且頁面仍然能夠容納同一頁面中的記錄。

uniqueidentifier 也是 16 字節，nvarchar 小於 16 字節。因此，CI 會發生頁面拆分，但 NON CI 不會發生頁面拆分，只有更多行。

如果 ID 是 int ，那麼頁數會更少，並且對於較小的 insert 也不會出現頁面拆分。

但如果添加更多行，再次說 4000，則非 CI 頁數變為 82。

重建索引後 CI 頁數 =61，非 CI=53。

Conclusion: 一個索引的 FF 不會影響另一個索引。每個索引頁面拆分的發生是因為它自己指定的 FF 以及它所屬的數據類型。

引用自：https://dba.stackexchange.com/questions/191882