確定SQL Server填充因子(FILLFACTOR)值的方法
設(shè)置填充因子是否重要？我認為是。很多資料都提到，缺省的填充因子取值一般情況下是較優(yōu)的，而且建議除非你很清楚在做什么，否則不要更改填充因子取值。就像數(shù)據(jù)庫管理員常識那樣，除非你理解你正在做什么，否則不更改任何設(shè)置是明智的。說得雖然沒錯，但是到底該如何確定填充因子取值，以及知道該做什么？我試圖找到一個確定取值的簡單公式。你猜結(jié)果如何？我找不到。
為了理解填充因子，有必要好好理解聚集和非聚集索引，堆表(heap tables)，擴展盤區(qū)(extents)，頁(pages)，頁拆分(page splits)，以及DBCC SHOWCONTIG命令結(jié)果。如果你不熟悉這些概念，我建議參考SQL Server聯(lián)機幫助，或者相關(guān)背景的資料。理解SQL Server物理文件結(jié)構(gòu)的組成是十分重要的。
基本上填充因子是指當索引被創(chuàng)建和重建時，指定存儲記錄的葉節(jié)點頁空間的百分比。我們也許以前都看過這個定義，但是該定義中有一個常被人忽視的關(guān)鍵點－－"當索引被創(chuàng)建和重建時"。一旦頁拆分發(fā)生，經(jīng)過拆分后生成的兩個新頁上，原來設(shè)置的填充因子取值就失效了。當索引創(chuàng)建時，如果指定填充因子為80，意味著每個頁只使用80％的空間。在某個頁填入數(shù)據(jù)后，例如添加新記錄或者修改記錄，頁拆分為兩個新頁，并且填充空間均為50％。原來那個填充因子對這兩個新頁失效。如果你沒有重建索引，那么除非數(shù)據(jù)量很小，或者數(shù)據(jù)更新很小，否則很可能會產(chǎn)生很多頁拆分，并且最早的填充因子會失效。
還是那個問題，我們該設(shè)置填充因子為多少呢？取值低，則需要更多的頁來存儲數(shù)據(jù)，因而讀取范圍大；這會影響性能。取值高，則會造成大量的頁拆分；同樣會影響性能。頁拆分同時消耗CPU和I/O資源，但頁拆分本身確實是必要的。也許你知道在只讀表上應(yīng)該設(shè)置填充因子高，而有大量更新的表上應(yīng)該設(shè)置較低。這個結(jié)論沒錯，但是關(guān)鍵是你怎么知道表上面數(shù)據(jù)的更新程度？正如我現(xiàn)在的數(shù)據(jù)庫，要指出哪些表是更新較低的，而那些是較高的，是一件很花時間的任務(wù)。其中的一個數(shù)據(jù)庫有22,000張表，所以你能想象，要做完上述的工作有多漫長了。并且即使同一張表上的不同索引也具有不同的碎片率。所以要定義"高"和"低"的設(shè)置，是一件令人絕望的事情。
我想在這里闡述的東西不是嚴格的科學(xué)，也不是放置四海皆準的方法，而是一般情況下，我經(jīng)常遵循的常識性的東西，就象當創(chuàng)建物理模型時，經(jīng)常在外鍵上創(chuàng)建索引的常規(guī)做法一樣道理。因此我想我已經(jīng)講明白本文的性質(zhì)了。請在一兩個表上試用這些方法，看看到底效果如何。
目標
在索引大小和頁拆分之間找到平衡點。當添加影響索引的數(shù)據(jù)時，較小的填充因子減少頁拆分；較大的填充因子正好相反。
盡量減少擴展盤區(qū)交換次數(shù)，并保持頁數(shù)據(jù)的連續(xù)。每個索引的掃描密度（Scan Density）指標越大越好。我就比較喜歡將掃描密度保持在90%或者更高的水平，雖然在索引重建時，并不總是能達到。
防止在索引重建后，每秒頁拆分(page splits/sec)指標突然升高。這會導(dǎo)致我的服務(wù)器崩潰。
盡量使得每個索引的平均頁密度(Average Page Density)指標越高越好。該指標越高，說明讀取操作越少。
平均頁密度(Average Page Density)接近或者高于填充因子取值。在完美的情況下，當索引重建時，Average Page Density取值應(yīng)該在填充因子取值到100%之間。
保持數(shù)據(jù)庫平穩(wěn)持續(xù)增長。你可以監(jiān)控索引重建前和重建后數(shù)據(jù)庫磁盤容量的大小變化。如果重建后，占用空間減少了，是好現(xiàn)象。
在索引重建時，盡量保持性能參數(shù)平穩(wěn)。如果只能有一個目標，我認為這就是我要選擇的目標。上面的所有目標最終是為該目標服務(wù)的。我喜歡在整個過程中性能保持平穩(wěn)和一致。如此，可以減少很多性能問題的擔(dān)憂。
要緊的事先做，讓我們開始吧
首先定義一個重建索引的調(diào)度作業(yè)。我會根據(jù)數(shù)據(jù)庫的不同創(chuàng)建不同的調(diào)度。例如對于相對較小的數(shù)據(jù)庫，我創(chuàng)建的調(diào)度作業(yè)為：在非高峰時間，對所有用戶表執(zhí)行DBCC DBREINDEX。在某些階段，有些數(shù)據(jù)庫會受到額外的關(guān)注。這對于如何設(shè)置填充因子是很重要的一個因素。填充因子的設(shè)置依賴于索引重建的頻率。
下面將講述一些調(diào)試數(shù)據(jù)庫的規(guī)律性東西。你可以列舉很多這樣的規(guī)律，不過我想分享一些我認為更關(guān)鍵的規(guī)律。首先，性能監(jiān)控時請關(guān)注Page Splits/Sec這個指標。記住頁拆分是一項不得不做的工作，同時也因此而帶來性能問題。其次，獲取DBCC SHOWCONTIG的信息。我有一個調(diào)度作業(yè)，該作業(yè)每個星期執(zhí)行一次DBCC SHOWCONTIG，負責(zé)獲取我所有服務(wù)器上的用戶數(shù)據(jù)庫結(jié)果，并放到某個表中。當然我會在這些信息上添加實例和數(shù)據(jù)庫名字的字段以方便查詢分析。運行DBCC SHOWCONTIG，并加上ALL_INDEXES和TABLERESULTS的選項。TABLERESULTS選項能讓我們方便地讀取和操作結(jié)果數(shù)據(jù)。ALL_INDEXES選項是必要的，因為如果沒有該選項，你看的的結(jié)果是針對聚集索引和堆的。我們需要掌握全局并分別對待每個索引。這是因為某個表上的某個索引的好壞，并不意味該表上別的索引也有相同結(jié)論。一個索引也許建立在相對變化大的字段上，而另一個也許建立在相對靜態(tài)的字段上。因此你還應(yīng)該獲取一系列的表名，索引，以及sysindexes表中的OrigFillFactor數(shù)值
Ok，讓我們按照這些準則并將其應(yīng)用在數(shù)據(jù)庫上。由于DBCC SHOWCONTIG需要消耗資源，所以最好在系統(tǒng)不那么繁忙的時間做該工作。也許你的數(shù)據(jù)庫有許多表和索引，所以最好能夠按字母順序或者按行數(shù)來排列得到的結(jié)果。起初，看那些結(jié)果會比較麻煩，不過只要用一段時間，你就會慢慢體會到那些數(shù)值的重要性了。
在每次調(diào)度中執(zhí)行DBREINDEX命令�？偸且粢獾氖牵撁畹膱�(zhí)行會十分消耗資源，在某些情況下有可能讓數(shù)據(jù)庫崩潰，而且我直覺地認為會降低數(shù)據(jù)庫性能。比如當填充因子設(shè)置為100，并且數(shù)據(jù)庫同時有大量的insert操作的時候。此時會發(fā)生什么？首先，數(shù)據(jù)庫會有大量的頁拆分。因為填充因子取值設(shè)置不當，重建索引工資也許會在好幾天內(nèi)都對性能產(chǎn)生影響。
在重建索引后，監(jiān)視Page Splits/Sec指標，看看是否上升了。從重建索引開始，直到下一次重建前，之間間隔要超過24小時。在這段時間內(nèi)需要獲取三次Page Splits/Sec的取值，一次是在索引重建完成時，一次是距離下次重建的中間時間點，還有一次就是下次重建前一刻。
在下次重建前的24小時內(nèi)，運行DBCC SHOWCONTIG，并保存結(jié)果信息。
利用最初的填充因子取值，和由DBCC SHOWCONTIG命令獲得的信息，就可以開始進行新的填充因子設(shè)置了。通過重建索引的調(diào)度作業(yè)，在上述時間段內(nèi)觀察索引的碎片程度，當然，前提是事務(wù)造成的容量變化不大。
我并不會糾纏較小的索引。所謂較小的索引，不僅指索引對應(yīng)的記錄數(shù)小，而且指涉及的頁數(shù)小。這樣可以減少很多分析的時間，而且填充因子的修改也不至于影響太大。關(guān)于每個索引，可以通過Scan Density指標來觀測。通常，這個指標是關(guān)鍵，不過也有例外。如果Page Density指標為24%，這就意味著索引正被拆分了，一些數(shù)據(jù)發(fā)生改變了。當平均Page Density為50%，而原始的填充因子取值為90，那么可能有問題了。既然我知道下次索引會在什么時候被重建，所以我還會看看，當原始的填充因子為80，并且平均Page Denisty為我要增加的填充因子取值的90%時的情形。
填充因子設(shè)置的一般性準則和指導(dǎo)
索引重建任務(wù)的時間間隔要相對一致。
如果索引較小，就沒有必要去調(diào)整填充因子。
在索引級別上進行監(jiān)控和更新，而不是表級別上。
保存填充一直在0，或者75和100之間。如果你要將填充因子設(shè)置為低于75，那么你必須自信你在做什么。保持較低的Scan Density和較低的平均Page Density是十分重要的情形。做一些觀察，在將填充因子取值降低前，找出表被讀取的頻繁程度。
如果Scan Density高于或等于90%，別去改變填充因子，或者調(diào)整任務(wù)中填充因子至少不應(yīng)該是首先被調(diào)整的。
如果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ù)變化如何。
要力圖避免改變填充因子過快。我建議多觀察一些周期，然后微調(diào)。
這就是我提供的設(shè)置填充因子的常規(guī)原則和步驟。如果你索引重建周期很規(guī)律，你就可以發(fā)現(xiàn)索引碎片如何。通過使用DBCC SHOWCONTIG獲取的數(shù)據(jù)，可以在下次索引重建前，輔助你來決定填充因子的新取值。通過監(jiān)視指標，可以慢慢學(xué)到頁拆分和填充因子設(shè)置的技巧。我曾經(jīng)嘗試得出設(shè)置填充因子的公式，這花了我很多時間。上面列舉的規(guī)則很直觀，一般情況下我會將填充因子盡可能的設(shè)置高一些。在很多情況下，這是基本的準則。
看完上面的規(guī)則后，你會覺得這并不難。因此快點應(yīng)用到實際上，但是不要期望結(jié)果是十全十美的，因為這和目標有關(guān)。
其它的觀點和考慮
使用時間長的數(shù)據(jù)庫的填充因子一般來說比新數(shù)據(jù)庫要高。這很容易解釋。很多表增長是一個常數(shù)，既不是線性也不是指數(shù)型。假設(shè)某個索引最初需要1000頁，索引重建期間擴展了500頁，填充因子值位90。這相當于空余0.10*8096=790KB的容量用于后面數(shù)據(jù)的增加或更新。此時的填充因子被認為也許高了一些，應(yīng)該調(diào)低。但是一旦填充因子同樣為90，但是如果那個表最初有10,000頁，那么意味著有大約7906KB的空閑空間可以用于后面的數(shù)據(jù)增加或更新。因此10,000頁的時候，提高填充因子取值是有利的。
