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

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

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

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

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

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

要緊的事先做，讓我們開(kāi)始吧

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

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

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

      在每次調(diào)度中執(zhí)行DBREINDEX命令。總是要留意的是，該命令的執(zhí)行會(huì)十分消耗資源，在某些情況下有可能讓數(shù)據(jù)庫(kù)崩潰，而且我直覺(jué)地認(rèn)為會(huì)降低數(shù)據(jù)庫(kù)性能。比如當(dāng)填充因子設(shè)置為100，并且數(shù)據(jù)庫(kù)同時(shí)有大量的insert操作的時(shí)候。此時(shí)會(huì)發(fā)生什么？首先，數(shù)據(jù)庫(kù)會(huì)有大量的頁(yè)拆分。因?yàn)樘畛湟蜃尤≈翟O(shè)置不當(dāng)，重建索引工資也許會(huì)在好幾天內(nèi)都對(duì)性能產(chǎn)生影響。

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

在下次重建前的24小時(shí)內(nèi)，運(yùn)行DBCC SHOWCONTIG，并保存結(jié)果信息。

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

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

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

看完上面的規(guī)則后，你會(huì)覺(jué)得這并不難。因此快點(diǎn)應(yīng)用到實(shí)際上，但是不要期望結(jié)果是十全十美的，因?yàn)檫@和目標(biāo)有關(guān)。

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