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

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

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

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

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

     這就是所謂的外部碎片.

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

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

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

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

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

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

   分頁(yè)后的示意圖:

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

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

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

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

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

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

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

    1.刪除索引并重建

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

    2.使用DROP_EXISTING語(yǔ)句重建索引

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

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

       使用這個(gè)語(yǔ)句同樣也是重建索引，但是通過(guò)動(dòng)態(tài)重建索引而不需要卸載并重建索引.是優(yōu)于前兩種方法的，但依舊會(huì)造成阻塞。可以通過(guò)ONLINE關(guān)鍵字減少鎖，但會(huì)造成重建時(shí)間加長(zhǎng).

    4.使用ALTER INDEX REORGANIZE

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

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

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

      下面來(lái)看一個(gè)例子:

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

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

     這時(shí)我再插入一頁(yè)，不會(huì)造成分頁(yè):

     上面的概念可以如下圖來(lái)解釋:

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

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

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

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

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

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

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