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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

其它的觀點和考慮
      使用時間長的數(shù)據(jù)庫的填充因子一般來說比新數(shù)據(jù)庫要高。這很容易解釋。很多表增長是一個常數(shù)，既不是線性也不是指數(shù)型。假設某個索引最初需要1000頁，索引重建期間擴展了500頁，填充因子值位90。這相當于空余0.10*8096=790KB的容量用于后面數(shù)據(jù)的增加或更新。此時的填充因子被認為也許高了一些，應該調低。但是一旦填充因子同樣為90，但是如果那個表最初有10,000頁，那么意味著有大約7906KB的空閑空間可以用于后面的數(shù)據(jù)增加或更新。因此10,000頁的時候，提高填充因子取值是有利的。當設置填充因子的時候，索引的增長和大小是必須考慮的。
和較小的表/索引相比，具有低掃描密度或者大幅度下降的平均頁密度的大表/索引更應該被關注。它們具有更多的頁拆分，這些頁拆分會給較大的索引帶來大幅度的下降。因為我們的目標是減少頁拆分！一個十分大的索引下降2％的掃描密度，從純量上看，遠遠大于較小的索引降低30％的的幅度。
密切關注通過SHOWCONTIG得到的掃描密度和平均頁密度指標。從這兩個指標，可以得到很多相關的信息。
sysindexes表中的初始填充因子取值并不代表索引創(chuàng)建時的填充因子取值，至少該取值并非是必要的。該數(shù)值只在最后一次創(chuàng)建或重建時有用。
你可以用同樣的方法來考查堆表的情況。
當你覺得填充因子設置合適后，請在一段時間后再觀察一下。這需要不斷的維護。