索引中填充因子的最佳值
我使用
SQL Server 2008 R2並希望為每個索引中的填充因子屬性設置值。我尋找配方來計算此屬性的最佳值（插入記錄時具有更好的性能）。另外，我如何通過為此屬性設置參考值來衡量查詢性能。提前非常感謝。
對於填充因子，沒有一個答案。這在很大程度上取決於您的數據修改如何在數據庫範圍或特定索引中進行。開箱即用的預設值為 0 (100)，這在頁面中幾乎沒有空間。這意味著插入頁面中間會導致頁面拆分。
監控您的頁面拆分/秒計數器，如果您看到較高的速率，那麼您可能需要重新考慮您的填充因子。但是不要盲目地設置這麼低，因為您將使用更多的磁碟空間和較低的填充因子。當然，它會導致更高的 IO 讀取，因為有更多的頁面來容納數據。
我建議您在這裡查看我關於填充因子的答案。這是一個不同類型的問題，但它應該讓您更好地了解填充因子及其對您的影響。
基本思想是您希望頁面盡可能完整。打包到數據頁中的行越多，需要讀取和保存在緩衝池中的頁就越少。
因此，最終您希望所有頁面 100% 充滿數據。
然而：
當您的所有頁面都已滿時，您會遇到幾個問題：
- 超出索引順序插入。這些條目必須放在數據之間。由於頁面已滿，其他記錄被強制移至新頁面。
- 您有一個表模式，其中大部分行是可變數據類型。更改行大小的更新可能會導致頁面拆分
- 更新索引鍵，使條目在索引中移動會導致頁面拆分。
- 快照版本控制會在行中添加一個 14 字節的標籤，這可能會導致頁面拆分。
頁面拆分還有其他原因，但這些是最大的原因。
以上都會造成碎片化。現在這很糟糕，但以一種奇怪的方式很好。因為這是唯一可以正確幫助您確定正確填充因子的方法。我會解釋為什麼。
你要做的是：
看看以上是否適用。對於這樣做的索引，請執行以下操作。
- 從 90% 的填充因子開始（我們必須從某個地方開始）
在您的索引維護過程中，建構您跟踪索引建立碎片的速度。（比如說一周內 30%） 2. 現在在您的下一個重建程序中，稍微降低填充因子。說80%。看看在接下來的 7 天內，您的碎片是否低於 30%。 3. 像這樣繼續，直到碎片沒有下降，或者碎片變得非常低。
請記住，您應該隨著時間的推移對此進行評估。您的工作量（數據模式）可能會隨著時間而改變。
還要記住你目前的問題是什麼。頁面分裂會導致日誌文件的額外負載（可能很多）。對數據文件的額外寫入（更高的檢查點峰值）。由於碎片，預讀變得不那麼有效。但是，低填充因子會佔用緩衝區空間，增加讀取負載。
需要非常小心的一件事是，pagesplits/sec 計數器並不是真正可靠的。他們跟踪所有頁面拆分。（sql 2012 更好）我的意思是，當您在索引的最後插入一條記錄時，實際上您需要將一個新頁面添加到索引中才能繼續。新數據頁的添加也算作分頁。但實際上沒有什麼是分裂的。因此，當您查看 pagesplit/sec 計數器時，集群索引上的插入仍然會有頁面拆分，但它們還不錯。所以只看這個計數器與其他事實的關係。例如：無法解釋的高傳輸日誌負載以及高亂序插入以及高頁面拆分/秒。
最後一句警告。特別是在大桌子上。始終檢查索引的目前頁面密度，併計算使用不同填充因子重建索引時對大小的影響。（使用新的 50% 填充因子重建具有 100% 頁面密度的索引將有效地使索引大小翻倍）