什麼是在流量穩定的表上分割我的索引？

我正在使用 Ola Hallengren 的解決方案來優化索引。我每週週日執行它。該索引在過去 6 個月的碎片化程度較低，不需要重組。它的使用方式也沒有改變。

在過去的三週內，每次執行它都會報告數據庫最大表上的聚集索引的碎片率在 5-12% 之間。奇怪的是，這不會在工作日發生，因此它必須在維護作業執行之前的某個時間出現。

我的表的每一行都有一個時間戳，所以我知道流量幾個月來一直保持在同一水平。通常每月的碎片化率低於 1%。

我有兩個問題：

1. 還有什麼可能使我的索引碎片化？
2. 我可以設置一個自動化的解決方案來跟踪更改嗎？理想情況下不會造成破壞，因為這是一個生產盒。

該表有 134 列，包含 14GB 的數據，主鍵不是身份（嘆息）

有問題的索引如下所示：

• 平均行大小：1413
• 深度：4
• 葉級行數：9884500
• 最大行大小：2303
• 最小行大小：746
• 頁數：1904907
• 分區 ID：1

CREATE UNIQUE CLUSTERED INDEX [FOOINDEX] ON [dbo].[FOOTABLE]     
(
  [FOONO] ASC,

  [ID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,  IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

一般來說

只是在開始時要明確：碎片是指下一個邏輯數據頁（即1行或多行的欄位中的值）不是下一個物理數據頁（數據文件中頁面的位置）時。

非碎片頁面（按物理頁面順序）：

1. 邏輯頁 1：A1、A2、A3、A4
2. 邏輯頁 2：B1、B2、B3、B4
3. 邏輯頁 3：C1、C2、C3、C4

碎片頁面（按物理頁面順序）：

1. 邏輯頁 1：A1、A2、A3、A4
2. 邏輯頁 3：C1、C2、C3、C4
3. 邏輯頁 2：B1、B2、B3、B4

一般來說，碎片化是由以下原因引起的：

• 亂序插入的數據（相關索引的順序）。

  • 使用不斷增加的值（例如 IDENTITY 欄位）是緩解這種情況的一種方法，因為行將始終在索引的末尾創建（假設 ASC 排序順序）。
  • FILLFACTOR 是在不使用不斷增加的值時減少碎片的另一種方法。但與不斷增加的值不同，FILLFACTOR 只是每個數據頁的一定空間量，並且根據輸入的數據，它可能沒有足夠的空間，在這種情況下會發生頁面拆分。並且根據行的最大大小，可能沒有多少保留空間會有所幫助（如果行超過 4030 字節）。

• 可變長度欄位（例如 VARCHAR、NVARCHAR）和標記為 SPARSE 的可空固定長度列的更新從 NULL 變為非 NULL 值。如果這些類型的欄位變化的組合增加了行的大小並且數據頁上沒有更多的空間，就會發生頁面拆分。

  • FILLFACTOR 在這裡再次提供幫助，因為它保留了一些空間（假設行大小足夠小以允許它）用於更新操作以增加行的大小而無需進行頁面拆分。

• 刪除足夠多的行以刪除一個或多個數據頁

有關索引碎片的其他資源：

• SQL Server 索引碎片、類型和解決方案
• 帶有插入/更新的索引碎片，測量它並修復它
• 視覺化索引碎片

特別是

對於此特定索引，平均行大小為 1413，因此每頁只能獲得 5 行（同樣，平均而言）。未指定 FILLFACTOR，因此它將採用系統預設值（預設“預設”值為 0，表示填充數據頁）。鑑於這FOONO是VARCHAR(12). 當新行進來時，這會經常給你一些碎片。當然，即使沒有指定 FILLFACTOR，也會有少量的自然保留空間，因為行大小是可變的。這意味著，在 REINDEX 之後，頁面上可能還剩下 2000 個字節，因為按順序排列的下一行大於 2000 個字節並且無法放入頁面。如果一個新行在邏輯上適合這些現有行並且 <= 2000 字節，它應該去那裡。因此，對於這些情況，為了了解傳入的值與碎片增加之間的關係，您需要辨識新行，考慮到時間戳欄位，這似乎是可行的。

已確認此表是 INSERT-only。這縮小了新行進入的碎片來源。碎片的變化應該是a）FOONO欄位值分佈和b）新行寬度的函式。鑑於有一個 DATETIME 欄位指示何時插入行，您應該能夠在過去 6 週內每週查詢：

1. FOONO領域_
2. ID領域_
3. 每行中可變長度欄位的總大小（基於那裡的數據；COALESCE(DATALENGTH(ColumnName), 0)用於每個欄位）。您不關心固定長度欄位，因為它們不是碎片增加的一個因素（除非它們被標記為 SPARSE 並且現在以非 NULL 值出現，而在前幾週它們通常為 NULL）。

您正在尋找的是（將最近的 3 週或現在的 4 週與碎片較低的情況進行比較；並確保ORDER BY該DATETIME領域）至少以下一項：

1. 更多樣化的FOONO價值分佈

2. ID價值觀：

3. 散佈有現有ID值的分佈，AND / OR

4. DATETIME 時間戳欄位表示的相對於時間 INSERT 順序的非順序/無序值（因此為什麼需要這樣做ORDER BY TimestampField）

5. 更大的整體行大小

發生這些事情的任何組合都應該解釋碎片化的增加。

橡膠與道路相遇的地方

這 4 種可能性的共同主題是它們都具有增加頁面拆分率的效果。當新行（或比以前更大的更新行，但現在我們只關心新行）邏輯上應該放置在沒有足夠可用空間來容納新行的數據頁上時，就會發生頁面拆分排。發生這種情況時，SQL Server 將（通常）獲取現有數據的後​​一部分並將其移動到新頁面，並將新行添加到它在邏輯上屬於的兩個頁面中的任何一個。最終結果是，新頁面將至少有一半，如果不是稍微多一點的話（我用一個每頁包含 10 行的表進行了多次測試，每個頁面拆分留下現有頁面 5 行和6 行的新頁面）。

不過，典型的頁面拆分只是故事的一半。另一半是當數據按順序添加到拆分操作創建的這些新頁面時發生的情況。隨著新行的添加和新頁面的填滿，在不移動任何行的情況下創建新頁面。發生這種情況時，新頁面不僅按邏輯順序排列，而且與前一頁相比也按物理順序排列。這不被認為是碎片頁面，因為從前一頁到這個新頁，它們是邏輯和物理順序。

這意味著，如果我們有完整的頁面 A0、B0 和 C0，並添加了一些在邏輯上會進入 B0 的行，則會發生頁面拆分，從而為我們提供一個新頁面 B1，在 B0 和 B1 上都有一定數量的行。但是現在，如果我們填充 B1 並繼續按順序添加邏輯上在 C0 上的數據之前的行，它將（或經常）為新行創建一個新頁面 B2，而不會將任何內容從 B1 移動到 B2。物理上，頁面將按以下順序排列：

• B1、B2、A0、B0、C0

要麼

• A0、B0、C0、B1、B2

從 B0 到 B1 以及從 B2 到 C0 的跳轉被認為是碎片，因為邏輯順序（A0、B0、B1、B2、C0）與物理順序不匹配。但是，從 B1 到 B2 的跳轉不被視為碎片，因為邏輯順序確實與物理順序匹配。

現在讓我們把所有這些放在一起。請記住，此特定索引的數據頁被打包得非常緊密（這很好），大多數插入操作很可能會進入已填充的頁，因此需要拆分。如果有 4 個新行要添加，如果它們分散開（就值而言FOONO和/或就ID–if 新值可能介於現有值之間）這樣它們應該進入 4 個單獨的頁面，那是 4 個頁面拆分，代表 4 個新頁面和 8 次出現的碎片（每個拆分的跳轉亂序並返回到順序）。但，如果4個新行是有序的，那麼有可能只發生了1個頁面拆分，代表1個新頁面和2個碎片出現。如果更多行順序添加到由拆分創建的新頁面，從而需要一個新頁面，它們可以滾動到新的下一頁，這顯然會將頁數增加 1，但不會記錄為碎片的出現。

另一個場景——就一周的新記錄而言是連續的，但沒有按“時間戳”DATETIME 欄位排序確定的順序添加的數據——與剛剛描述的主要場景基本相同。這裡的區別在於 a) 如果沒有按“時間戳”欄位排序，它可能看起來好像數據是連續的，並且會被錯誤地排除為碎片的來源，並且 b) 頁面拆分將在創建的集合中更頻繁地發生由於初始頁面拆分而剛剛創建的頁面。

希望以下範例將有助於說明問題。假設我們正盯著邏輯頁 A0、B0 和 C0，它們在物理上也是按順序排列的。頁 B0 已填充並包含行 Ba、Bf 和 Bn。新的行是 Bo、Bp、Bq、Br 和 Bt。但他們不是按這個順序來的。下面列出了每一行進入時數據頁應該發生的情況。頁 ID（例如 A0）表示邏輯頁，而順序表示它們的物理位置。

• 開始：

A0

B0 = Ba、Bf 和 Bn（頁面已滿）

C0

• 添加溴：

B1 = Bn 和 Br（物理上在 A0 之前，但邏輯上在 B0 = 碎片之後）

A0

B0 = Ba 和 Bf（拆分：Bn 移動到新頁面 B1）

C0

• 添加BT：

B1 = Bn、Br 和 Bt（已填滿頁面）

A0

B0 = Ba 和 Bf

C0

• 添加BP：

B1 = Bn 和 Bp（拆分：Br 和 Bt 移動到新頁面 B2）

B2 = Br 和 Bt（在 B1 = NOT 分片之後物理與邏輯）

A0

B0 = Ba 和 Bf

C0

• 添加 Bq：

B1 = Bn、Bp 和 Bq（已填滿頁面）

B2 = Br 和 Bt

A0

B0 = Ba 和 Bf

C0

• 加博：

B1 = Bn 和 Bo（拆分：Bp 和 Bq 移到新頁面 B2）

B3 = Br 和 Bt（物理上仍然在 B1 之後，但現在邏輯上在 B2 = 碎片之後）

B2 = Bp 和 Bq（物理上在 B3 之後，但邏輯上在 B1 之後= 碎片）

A0

B0 = Ba 和 Bf

C0

結果： 3 個頁面拆分、3 個新頁面、4 個碎片計數、4 個頁面部分填充

如果按順序插入這些相同的行，它最終會看起來像：

• 開始：

A0

B0 = Ba、Bf 和 Bn（頁面已滿）

C0

• 最終的：

B1 = Bn、Bo 和 Bp

B2 = Bq、Br 和 Bt

A0

B0 = Ba 和 Bf（拆分：Bn 移至新頁面 B1）

C0

結果： 1 個頁面拆分、2 個新頁面、2 個碎片計數、1 個頁面部分填充

---

雜項。

只是為了讓那些具有類似設置但確實允許 UPDATE 操作的人注意它，如果確實發生了 UPATE，那麼這個問題將包含一個錯誤的前提，最終將無法回答。手頭的問題是時間戳（無論是使用 TIMESTAMP / ROWVERSION 數據類型還是 DATETIME / DATETIME2 數據類型）只會告訴您最後一次發生變化；它沒有告訴你 a) 發生了多少變化，也沒有告訴你 b) 發生了哪些具體變化（即行變小了嗎？5 個欄位變大了，但 20 個欄位變小了，所以行大小實際上保持不變一樣嗎？等）。如果此表確實發生了更新，那麼查看導致碎片增加的唯一方法是收集更改的審計數據（無論是自定義的、基於觸發器的解決方案還是更改數據擷取）。但是在目前設置中，無法可靠地將更改的行數（如時間戳欄位所示）與任何數量的碎片相關聯（同樣，IF 更新正在發生）。

此外，關於此聲明：

該表有 134 列，包含 14gb 的數據，主鍵不是身份（嘆氣）

如果這是一個 OLTP 表，那麼我會發現 134 列比不基於 IDENTITY 欄位的主鍵更成問題。因為這是一個歷史表，所以有 134 列可能是可以的。但即使是歷史表，也不一定最好是 PK 基於一個 IDENTITY 欄位，或者該表甚至有一個 PK（我假設 PK 和聚集索引不一樣，因為問題中的索引是 UNIQUE CLUSTERED 而不是 PK）。而且即使我們在談論聚集索引（無論是否是PK），那麼仍然不一定總是最好作為領先的列。這一切都取決於如何訪問數據。如果前導欄位是 IDENTITY 但該值從未在 JOIN 或 UPDATE 或 DELETE 操作中使用，那麼您將犧牲所有查詢的性能，即使沒有碎片（不可修復），只是為了避免一些碎片（可通過REBUILD或REORGANIZE) 修復。

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