確定SQL Server填充因子(FILLFACTOR)值的方法

      設置填充因子是否重要？我認為是。很多資料都提到，缺省的填充因子取值一般情況下是較優的，而且建議除非你很清楚在做什么，否則不要更改填充因子取值。就像數據庫管理員常識那樣，除非你理解你正在做什么，否則不更改任何設置是明智的。說得雖然沒錯，但是到底該如何確定填充因子取值，以及知道該做什么？我試圖找到一個確定取值的簡單公式。你猜結果如何？我找不到。

     為了理解填充因子，有必要好好理解聚集和非聚集索引，堆表(heap tables)，擴展盤區(extents)，頁(pages)，頁拆分(page splits)，以及DBCC SHOWCONTIG命令結果。如果你不熟悉這些概念，我建議參考SQL Server聯機幫助，或者相關背景的資料。理解SQL Server物理文件結構的組成是十分重要的。

     基本上填充因子是指當索引被創建和重建時，指定存儲記錄的葉節點頁空間的百分比。我們也許以前都看過這個定義，但是該定義中有一個常被人忽視的關鍵點－－"當索引被創建和重建時"。一旦頁拆分發生，經過拆分后生成的兩個新頁上，原來設置的填充因子取值就失效了。當索引創建時，如果指定填充因子為80，意味著每個頁只使用80％的空間。在某個頁填入數據后，例如添加新記錄或者修改記錄，頁拆分為兩個新頁，并且填充空間均為50％。原來那個填充因子對這兩個新頁失效。如果你沒有重建索引，那么除非數據量很小，或者數據更新很小，否則很可能會產生很多頁拆分，并且最早的填充因子會失效。

     還是那個問題，我們該設置填充因子為多少呢？取值低，則需要更多的頁來存儲數據，因而讀取范圍大；這會影響性能。取值高，則會造成大量的頁拆分；同樣會影響性能。頁拆分同時消耗CPU和I/O資源，但頁拆分本身確實是必要的。也許你知道在只讀表上應該設置填充因子高，而有大量更新的表上應該設置較低。這個結論沒錯，但是關鍵是你怎么知道表上面數據的更新程度？正如我現在的數據庫，要指出哪些表是更新較低的，而那些是較高的，是一件很花時間的任務。其中的一個數據庫有22,000張表，所以你能想象，要做完上述的工作有多漫長了。并且即使同一張表上的不同索引也具有不同的碎片率。所以要定義"高"和"低"的設置，是一件令人絕望的事情。

     我想在這里闡述的東西不是嚴格的科學，也不是放置四海皆準的方法，而是一般情況下，我經常遵循的常識性的東西，就象當創建物理模型時，經常在外鍵上創建索引的常規做法一樣道理。因此我想我已經講明白本文的性質了。請在一兩個表上試用這些方法，看看到底效果如何。
目標
在索引大小和頁拆分之間找到平衡點。當添加影響索引的數據時，較小的填充因子減少頁拆分；較大的填充因子正好相反。
盡量減少擴展盤區交換次數，并保持頁數據的連續。每個索引的掃描密度（Scan Density）指標越大越好。我就比較喜歡將掃描密度保持在90%或者更高的水平，雖然在索引重建時，并不總是能達到。
防止在索引重建后，每秒頁拆分(page splits/sec)指標突然升高。這會導致我的服務器崩潰。
盡量使得每個索引的平均頁密度(Average Page Density)指標越高越好。該指標越高，說明讀取操作越少。
平均頁密度(Average Page Density)接近或者高于填充因子取值。在完美的情況下，當索引重建時，Average Page Density取值應該在填充因子取值到100%之間。
保持數據庫平穩持續增長。你可以監控索引重建前和重建后數據庫磁盤容量的大小變化。如果重建后，占用空間減少了，是好現象。
在索引重建時，盡量保持性能參數平穩。如果只能有一個目標，我認為這就是我要選擇的目標。上面的所有目標最終是為該目標服務的。我喜歡在整個過程中性能保持平穩和一致。如此，可以減少很多性能問題的擔憂。

要緊的事先做，讓我們開始吧

      首先定義一個重建索引的調度作業。我會根據數據庫的不同創建不同的調度。例如對于相對較小的數據庫，我創建的調度作業為：在非高峰時間，對所有用戶表執行DBCC DBREINDEX。在某些階段，有些數據庫會受到額外的關注。這對于如何設置填充因子是很重要的一個因素。填充因子的設置依賴于索引重建的頻率。

     下面將講述一些調試數據庫的規律性東西。你可以列舉很多這樣的規律，不過我想分享一些我認為更關鍵的規律。首先，性能監控時請關注Page Splits/Sec這個指標。記住頁拆分是一項不得不做的工作，同時也因此而帶來性能問題。其次，獲取DBCC SHOWCONTIG的信息。我有一個調度作業，該作業每個星期執行一次DBCC SHOWCONTIG，負責獲取我所有服務器上的用戶數據庫結果，并放到某個表中。當然我會在這些信息上添加實例和數據庫名字的字段以方便查詢分析。運行DBCC SHOWCONTIG，并加上ALL_INDEXES和TABLERESULTS的選項。TABLERESULTS選項能讓我們方便地讀取和操作結果數據。ALL_INDEXES選項是必要的，因為如果沒有該選項，你看的的結果是針對聚集索引和堆的。我們需要掌握全局并分別對待每個索引。這是因為某個表上的某個索引的好壞，并不意味該表上別的索引也有相同結論。一個索引也許建立在相對變化大的字段上，而另一個也許建立在相對靜態的字段上。因此你還應該獲取一系列的表名，索引，以及sysindexes表中的OrigFillFactor數值

      Ok，讓我們按照這些準則并將其應用在數據庫上。由于DBCC SHOWCONTIG需要消耗資源，所以最好在系統不那么繁忙的時間做該工作。也許你的數據庫有許多表和索引，所以最好能夠按字母順序或者按行數來排列得到的結果。起初，看那些結果會比較麻煩，不過只要用一段時間，你就會慢慢體會到那些數值的重要性了。

      在每次調度中執行DBREINDEX命令。總是要留意的是，該命令的執行會十分消耗資源，在某些情況下有可能讓數據庫崩潰，而且我直覺地認為會降低數據庫性能。比如當填充因子設置為100，并且數據庫同時有大量的insert操作的時候。此時會發生什么？首先，數據庫會有大量的頁拆分。因為填充因子取值設置不當，重建索引工資也許會在好幾天內都對性能產生影響。

在重建索引后，監視Page Splits/Sec指標，看看是否上升了。從重建索引開始，直到下一次重建前，之間間隔要超過24小時。在這段時間內需要獲取三次Page Splits/Sec的取值，一次是在索引重建完成時，一次是距離下次重建的中間時間點，還有一次就是下次重建前一刻。

在下次重建前的24小時內，運行DBCC SHOWCONTIG，并保存結果信息。

利用最初的填充因子取值，和由DBCC SHOWCONTIG命令獲得的信息，就可以開始進行新的填充因子設置了。通過重建索引的調度作業，在上述時間段內觀察索引的碎片程度，當然，前提是事務造成的容量變化不大。

       我并不會糾纏較小的索引。所謂較小的索引，不僅指索引對應的記錄數小，而且指涉及的頁數小。這樣可以減少很多分析的時間，而且填充因子的修改也不至于影響太大。關于每個索引，可以通過Scan Density指標來觀測。通常，這個指標是關鍵，不過也有例外。如果Page Density指標為24%，這就意味著索引正被拆分了，一些數據發生改變了。當平均Page Density為50%，而原始的填充因子取值為90，那么可能有問題了。既然我知道下次索引會在什么時候被重建，所以我還會看看，當原始的填充因子為80，并且平均Page Denisty為我要增加的填充因子取值的90%時的情形。
填充因子設置的一般性準則和指導
索引重建任務的時間間隔要相對一致。
如果索引較小，就沒有必要去調整填充因子。
在索引級別上進行監控和更新，而不是表級別上。
保存填充一直在0，或者75和100之間。如果你要將填充因子設置為低于75，那么你必須自信你在做什么。保持較低的Scan Density和較低的平均Page Density是十分重要的情形。做一些觀察，在將填充因子取值降低前，找出表被讀取的頻繁程度。
如果Scan Density高于或等于90%，別去改變填充因子，或者調整任務中填充因子至少不應該是首先被調整的。
如果Scan Density在60%到90%之間，小小地降低一下填充因子，例如降低幅度2%。
如果Scan Density低于60%，則加大降低填充因子的幅度。填充因子的取值我一般會取平均Page Density和最初的填充因子之間的中值。例如Original Fill factor = 100，Average Page Density ＝ 60，那么我就取填充因子為80。
如果Average Page Density大于Original Fill Factor，并且Scan Density接近于100%，那么要提高填充因子取值。這樣做很好，因為你可以把更多的數據放在同一個頁中。例如：Fill Factor ＝ 80，Scan Density ＝ 98，Average Page Density ＝ 88。此時，在下次索引重建之前，頁面按照8%的速度填充，而且該速度相對持續穩定；這種情況嚇，就可以提高填充因子取值，但是不要超過92。因為按照8%的增長速度，頁面馬上會填充到100％而引起頁拆分。我建議是提高到88%，并且開始觀察下一步數據變化如何。
要力圖避免改變填充因子過快。我建議多觀察一些周期，然后微調。

      這就是我提供的設置填充因子的常規原則和步驟。如果你索引重建周期很規律，你就可以發現索引碎片如何。通過使用DBCC SHOWCONTIG獲取的數據，可以在下次索引重建前，輔助你來決定填充因子的新取值。通過監視指標，可以慢慢學到頁拆分和填充因子設置的技巧。我曾經嘗試得出設置填充因子的公式，這花了我很多時間。上面列舉的規則很直觀，一般情況下我會將填充因子盡可能的設置高一些。在很多情況下，這是基本的準則。

看完上面的規則后，你會覺得這并不難。因此快點應用到實際上，但是不要期望結果是十全十美的，因為這和目標有關。

其它的觀點和考慮
      使用時間長的數據庫的填充因子一般來說比新數據庫要高。這很容易解釋。很多表增長是一個常數，既不是線性也不是指數型。假設某個索引最初需要1000頁，索引重建期間擴展了500頁，填充因子值位90。這相當于空余0.10*8096=790KB的容量用于后面數據的增加或更新。此時的填充因子被認為也許高了一些，應該調低。但是一旦填充因子同樣為90，但是如果那個表最初有10,000頁，那么意味著有大約7906KB的空閑空間可以用于后面的數據增加或更新。因此10,000頁的時候，提高填充因子取值是有利的。