為了理解填充因子，有必要好好理解聚集和非聚集索引，堆表(heap tables)，擴(kuò)展盤區(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ò)展盤區(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ù)磁盤容量的大小變化。如果重建后，占用空間減少了，是好現(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í)候，提高填充因子取值是有利的。當(dāng)設(shè)置填充因子的時(shí)候，索引的增長(zhǎng)和大小是必須考慮的。
和較小的表/索引相比，具有低掃描密度或者大幅度下降的平均頁(yè)密度的大表/索引更應(yīng)該被關(guān)注。它們具有更多的頁(yè)拆分，這些頁(yè)拆分會(huì)給較大的索引帶來(lái)大幅度的下降。因?yàn)槲覀兊哪繕?biāo)是減少頁(yè)拆分！一個(gè)十分大的索引下降2％的掃描密度，從純量上看，遠(yuǎn)遠(yuǎn)大于較小的索引降低30％的的幅度。
密切關(guān)注通過(guò)SHOWCONTIG得到的掃描密度和平均頁(yè)密度指標(biāo)。從這兩個(gè)指標(biāo)，可以得到很多相關(guān)的信息。
sysindexes表中的初始填充因子取值并不代表索引創(chuàng)建時(shí)的填充因子取值，至少該取值并非是必要的。該數(shù)值只在最后一次創(chuàng)建或重建時(shí)有用。
你可以用同樣的方法來(lái)考查堆表的情況。
當(dāng)你覺(jué)得填充因子設(shè)置合適后，請(qǐng)?jiān)谝欢螘r(shí)間后再觀察一下。這需要不斷的維護(hù)。
喜歡 
頂
難過(guò)
囧
圍觀
無(wú)聊
王者榮耀賬號(hào)惡
MARVEL Strike 
2017年10月手游
比官居一品好玩
