在SQL Server中，存儲數(shù)據(jù)的最小單位是頁，每一頁所能容納的數(shù)據(jù)為8060字節(jié).而頁的組織方式是通過B樹結(jié)構(gòu)（表上沒有聚集索引則為堆結(jié)構(gòu)，不在本文討論之列)如下圖:

    在聚集索引B樹中，只有葉子節(jié)點(diǎn)實(shí)際存儲數(shù)據(jù)，而其他根節(jié)點(diǎn)和中間節(jié)點(diǎn)僅僅用于存放查找葉子節(jié)點(diǎn)的數(shù)據(jù).

    每一個葉子節(jié)點(diǎn)為一頁，每頁是不可分割的. 而SQL Server向每個頁內(nèi)存儲數(shù)據(jù)的最小單位是表的行(Row).當(dāng)葉子節(jié)點(diǎn)中新插入的行或更新的行使得葉子節(jié)點(diǎn)無法容納當(dāng)前更新或者插入的行時，分頁就產(chǎn)生了.在分頁的過程中，就會產(chǎn)生碎片.

    首先，理解外部碎片的這個“外”是相對頁面來說的。外部碎片指的是由于分頁而產(chǎn)生的碎片.比如，我想在現(xiàn)有的聚集索引中插入一行，這行正好導(dǎo)致現(xiàn)有的頁空間無法滿足容納新的行。從而導(dǎo)致了分頁:

     因?yàn)樵赟QL SERVER中，新的頁是隨著數(shù)據(jù)的增長不斷產(chǎn)生的，而聚集索引要求行之間連續(xù)，所以很多情況下分頁后和原來的頁在磁盤上并不連續(xù).

     這就是所謂的外部碎片.

     由于分頁會導(dǎo)致數(shù)據(jù)在頁之間的移動，所以如果插入更新等操作經(jīng)常需要導(dǎo)致分頁，則會大大提升IO消耗，造成性能下降.

     而對于查找來說，在有特定搜索條件，比如where子句有很細(xì)的限制或者返回?zé)o序結(jié)果集時，外部碎片并不會對性能產(chǎn)生影響。但如果要返回掃描聚集索引而查找連續(xù)頁面時,外部碎片就會產(chǎn)生性能上的影響.

     在SQL Server中，比頁更大的單位是區(qū)(Extent).一個區(qū)可以容納8個頁.區(qū)作為磁盤分配的物理單元.所以當(dāng)頁分割如果跨區(qū)后，需要多次切區(qū)。需要更多的掃描.因?yàn)樽x取連續(xù)數(shù)據(jù)時會不能預(yù)讀，從而造成額外的物理讀，增加磁盤IO.

    和外部碎片一樣，內(nèi)部碎片的”內(nèi)”也是相對頁來說的.下面我們來看一個例子:

    我們創(chuàng)建一個表，這個表每個行由int(4字節(jié)),char(999字節(jié))和varchar(0字節(jié)組成），所以每行為1003個字節(jié),則8行占用空間1003*8=8024字節(jié)加上一些內(nèi)部開銷，可以容納在一個頁面中:

    當(dāng)我們隨意更新某行中的col3字段后，造成頁內(nèi)無法容納下新的數(shù)據(jù)，從而造成分頁:

   分頁后的示意圖:

    而當(dāng)分頁時如果新的頁和當(dāng)前頁物理上不連續(xù)，則還會造成外部碎片

    外部碎片對于性能的影響上面說過，主要是在于需要進(jìn)行更多的跨區(qū)掃描，從而造成更多的IO操作.

    而內(nèi)部碎片會造成數(shù)據(jù)行分布在更多的頁中，從而加重了掃描的頁樹，也會降低查詢性能.

    下面通過一個例子看一下,我們?nèi)藶榈臑閯偛拍莻€表插入一些數(shù)據(jù)造成內(nèi)部碎片:

    通過查看碎片，我們發(fā)現(xiàn)這時碎片已經(jīng)達(dá)到了一個比較高的程度:

    通過查看對碎片整理之前和之后的IO，我們可以看出，IO大大下降了:

    基本上所有解決辦法都是基于對索引的重建和整理，只是方式不同

    1.刪除索引并重建

       這種方式并不好.在刪除索引期間，索引不可用.會導(dǎo)致阻塞發(fā)生。而對于刪除聚集索引，則會導(dǎo)致對應(yīng)的非聚集索引重建兩次(刪除時重建，建立時再重建).雖然這種方法并不好，但是對于索引的整理最為有效

    2.使用DROP_EXISTING語句重建索引

       為了避免重建兩次索引，使用DROP_EXISTING語句重建索引，因?yàn)檫@個語句是原子性的，不會導(dǎo)致非聚集索引重建兩次，但同樣的，這種方式也會造成阻塞

    3.如前面文章所示，使用ALTER INDEX REBUILD語句重建索引

       使用這個語句同樣也是重建索引，但是通過動態(tài)重建索引而不需要卸載并重建索引.是優(yōu)于前兩種方法的，但依舊會造成阻塞?？梢酝ㄟ^ONLINE關(guān)鍵字減少鎖，但會造成重建時間加長.

    4.使用ALTER INDEX REORGANIZE

       這種方式不會重建索引，也不會生成新的頁，僅僅是整理，當(dāng)遇到加鎖的頁時跳過，所以不會造成阻塞。但同時，整理效果會差于前三種.

      重建索引固然可以解決碎片的問題.但是重建索引的代價(jià)不僅僅是麻煩，還會造成阻塞。影響使用.而對于數(shù)據(jù)比較少的情況下，重建索引代價(jià)并不大。而當(dāng)索引本身超過百兆的時候。重建索引的時間將會很讓人蛋疼.

      填充因子的作用正是如此。對于默認(rèn)值來說，填充因子為0（0和100表示的是一個概念）,則表示頁面可以100%使用。所以會遇到前面update或insert時，空間不足導(dǎo)致分頁.通過設(shè)置填充因子，可以設(shè)置頁面的使用程度:

      下面來看一個例子:

      還是上面那個表.我插入31條數(shù)據(jù)，則占4頁:

     通過設(shè)置填充因子，頁被設(shè)置到了5頁上:

     這時我再插入一頁，不會造成分頁:

     上面的概念可以如下圖來解釋:

      可以看出，使用填充因子會減少更新或者插入時的分頁次數(shù)，但由于需要更多的頁，則會對應(yīng)的損失查找性能.

    如何設(shè)置填充因子的值并沒有一個公式或者理念可以準(zhǔn)確的設(shè)置。使用填充因子雖然可以減少更新或者插入時的分頁，但同時因?yàn)樾枰嗟捻?，所以降低了查詢的性能和占用更多的磁盤空間.如何設(shè)置這個值進(jìn)行trade-off需要根據(jù)具體的情況來看.

    具體情況要根據(jù)對于表的讀寫比例來看,我這里給出我認(rèn)為比較合適的值:

    1.當(dāng)讀寫比例大于100：1時，不要設(shè)置填充因子，100%填充

    2.當(dāng)寫的次數(shù)大于讀的次數(shù)時，設(shè)置50%-70%填充

    3.當(dāng)讀寫比例位于兩者之間時80%-90%填充

    上面的數(shù)據(jù)僅僅是我的看法，具體設(shè)置的數(shù)據(jù)還要根據(jù)具體情況進(jìn)行測試才能找到最優(yōu).