實際上，索引的維護主要包括以下兩個方面：

• 　　頁拆分
• 　　碎片

　　這兩個問題都和頁密度有關(guān)，雖然兩者的表現(xiàn)形式在本質(zhì)上有所區(qū)別，但是故障排除工具是一樣的，因為處理是相同的。

　　對于非常小的表(比64KB小得多)，一個區(qū)中的頁面可能屬于多余一個的索引或表---這被稱為混合區(qū)。如果數(shù)據(jù)庫中有太多的小表，混合區(qū)幫助SQL Server節(jié)約磁盤空間。

　　隨著表(或索引)增長并且請求超過8個頁面，SQL Server創(chuàng)建專用于該表(或索引)的區(qū)并且從該區(qū)中分配頁面。這樣一個區(qū)被稱為統(tǒng)一區(qū)，它可以為多達8個相同表或索引的頁面請求服務(wù)。

一、碎片

　　當數(shù)據(jù)庫增長，頁拆分，然后刪除數(shù)據(jù)時，就會產(chǎn)生碎片。從增長的方面看，平衡樹處理得很不錯。但是對于刪除方面，它并沒有太大的作用。最終可能會出現(xiàn)這種情況，一個頁上有一條記錄，而另一個頁上有幾個記錄。在這種情況下，一個頁上保存的數(shù)據(jù)量只是它能夠保存總數(shù)據(jù)量的一小部分。

　　1、碎片會造成空間的浪費，SQL Server每次會分配一個區(qū)段，如果一個頁上只有一條記錄，則仍然會分配整個區(qū)段。

　　2、散布在各處的數(shù)據(jù)會造成數(shù)據(jù)檢索時的額外系統(tǒng)開銷。為了獲取需要的10行記錄，SQL Server不是只加載一個頁，而是可能必須加載10個頁來獲取相同的信息。并不只是讀取行導(dǎo)致了這一結(jié)果，在讀取行前，SQL Server必須先讀取頁。更多的頁意味著更多的工作量。

　　但是碎片也不只是有壞處，比如一個插入非常頻繁的表就很喜歡碎片，因為在插入數(shù)據(jù)時幾乎不用擔心頁拆分的問題。所以大量的碎片意味著較差的讀取性能，但也意味著極好的插入性能。

　　關(guān)于碎片的理解，找到了數(shù)據(jù)庫牛人CareySon的這篇文章 T-SQL查詢高級—SQL Server索引中的碎片和填充因子 ，在這里消化一下

　　碎片分兩種，外部碎片和內(nèi)部碎片

　　外部碎片：

　　外部碎片指的是頁拆分而產(chǎn)生的碎片。如向表中插入一行，而這一行導(dǎo)致現(xiàn)有的頁空間無法容納新插入的行，則導(dǎo)致頁拆分。

　　新的頁不斷隨數(shù)據(jù)的增長而產(chǎn)生，而聚集索引要求行之間連續(xù)，所以如果聚集索引不是自增列，頁拆分后和原來的頁在磁盤上并不連續(xù)-這就是外部碎片。
　　由于頁拆分，導(dǎo)致數(shù)據(jù)在頁之間的移動，所以如果插入更新等操作經(jīng)常需要分頁，則會大大消耗IO資源，造成性能下降。
　　對于查找連說，在有特定搜索條件，如where子句有很細的限制或者返回無序結(jié)果集時，外部碎片并不會對性能產(chǎn)生影響。但如果要返回掃描聚集索引而且查找連續(xù)頁面時，外部碎片就會產(chǎn)生性能上的影響。所以當要讀取相同的數(shù)連續(xù)的數(shù)據(jù)時需要掃描更多的頁，更多的區(qū)。而且連續(xù)數(shù)據(jù)不能預(yù)讀，造成額外的物理讀，增加磁盤IO。通常，外部碎片過多會造成頻繁的區(qū)切換。

　　如果頁面連續(xù)排序，預(yù)讀功能可以提前讀取頁面而不需要太多的磁頭移動。

　　內(nèi)部碎片：　　

　　內(nèi)部碎片是頁拆分后，導(dǎo)致索引頁的數(shù)據(jù)并不滿，有空行。同樣讀取一個索引頁，卻只能拿到x%的數(shù)據(jù)。

--新建一張表
CREATE TABLE Person
(
    Id    int,
    Name    char(999),
    Addr    varchar(10)
)

--聚集索引
CREATE CLUSTERED INDEX CIX ON Person(Id)

--插入8條數(shù)據(jù)
DECLARE    @var INT
SET @var=100
WHILE(@var < 900)
BEGIN
INSERT INTO Person(Id,Name,Addr)
VALUES(@var,'xx','')
SET @var = @var+100
END

　　這個表每個行由int(4字節(jié)),char(999字節(jié))和varchar(10字節(jié)組成），所以每行為1003個字節(jié),則8行占用空間1003*8=8024字節(jié)加上一些內(nèi)部開銷，可以容納在一個頁面中。(原來這個表和數(shù)據(jù)搞得還挺巧的)。

　　執(zhí)行查看語句：

　　SELECT page_count,avg_page_space_used_in_percent,record_count,avg_record_size_in_bytes,avg_fragmentation_in_percent,fragment_count 
　　FROM sys.dm_db_index_physical_stats
　　(DB_ID('Nx'),object_id('dbo.Person'),NULL,NULL,'sampled')

　　示例如下：

　　其中page_count是查看占用了多少個頁，而第二個參數(shù)表示該頁空間的使用率。因此從以上信息可以獲得，這8條數(shù)據(jù)是放在一個頁上，而且該頁的空間使用率已經(jīng)是百分之百了。

　　現(xiàn)在將其中一行的Addr改長一點：

　　UPDATE Person SET Addr = '廣東廣州' where Id = 100

　　則再執(zhí)行檢查索引語句：

　　可以看到，這個表已經(jīng)有了兩頁，頁面平均使用為50%左右。但是明顯也造成了碎片，在列avg_fragmentation_in_percent上可以看到，碎片大約為50%。

　　頁拆分后的示意圖如下：

　　這個時候，繼續(xù)插入數(shù)據(jù)，碎片會上升。在又插入了至達到48條記錄后，碎片程度如下：

　　這個時候，執(zhí)行一個查詢計劃，查看下IO性能：

　　可以看到I/O下降了不少。

二、元數(shù)據(jù)函數(shù)sys.dm_db_index_physical_stats分析碎片

　　SQL Server提供了一種特殊的元數(shù)據(jù)函數(shù)sys.dm_db_index_physical_stats，它有助于確定數(shù)據(jù)庫中的頁和區(qū)段有多滿。然后用該信息作出一些維護數(shù)據(jù)庫的決策。

　　該函數(shù)語法如下：

sys.dm_db_index_physical_stats(
    {<database id> | NULL | 0 | DEFAULT},
    { <object id> | NULL | 0 | DEFAULT },
    { <index id> } | NULL | 0 | -1 | DEFAULT },
    { <partition no> | NULL | 0 | DEFAULT },
    { <mode> | NULL | DEFAULT }
)

　　下面假設(shè)從SmartScan中獲取所有的索引信息：

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'Nx');
SET @object_id = OBJECT_ID(N'Account')
SELECT database_id,object_id,index_id,index_depth,avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats(@db_id,@object_id,NULL,NULL,NULL);

 　　下面看看統(tǒng)計信息的說明：

列名	數(shù)據(jù)類型	說明
database_id	smallint	表或視圖的數(shù)據(jù)庫 ID。
object_id	int	索引所在的表或視圖的對象 ID。
index_id	int	索引的索引 ID。 0 = 堆。
partition_number	int	所屬對象內(nèi)從 1 開始的分區(qū)號；表、視圖或索引。 1 = 未分區(qū)的索引或堆。
index_type_desc	nvarchar(60)	索引類型的說明： HEAP CLUSTERED INDEX NONCLUSTERED INDEX PRIMARY XML INDEX SPATIAL INDEX XML INDEX
alloc_unit_type_desc	nvarchar(60)	對分配單元類型的說明： IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA LOB_DATA 分配單元包含類型為text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 的列中所存儲的數(shù)據(jù)。  ROW_OVERFLOW_DATA 分配單元包含類型為 varchar(n)、nvarchar(n)、varbinary(n) 和sql_variant 的列（已推送到行外）中所存儲的數(shù)據(jù)。
index_depth	tinyint	索引總級別數(shù)。 1 = 堆，或 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元。
index_level	tinyint	索引的當前位于B樹結(jié)構(gòu)中的級別。 0 表示索引葉級別、堆以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元。 大于 0 的值表示非葉索引級別。 index_level 在索引的根級別中屬于最高級別。 僅當 mode = DETAILED 時才處理非葉級別的索引。
avg_fragmentation_in_percent	float	索引的邏輯碎片，或 IN_ROW_DATA 分配單元中堆的區(qū)碎片。 此值按百分比計算，并將考慮多個文件。 0 表示 LOB_DATA 和 ROW_OVERFLOW_DATA 分配單元。 如果是堆表且mode模式 為 Sampled 時，為 NULL。如果碎片小于10%~20%，碎片不太可能會成為問題，如果索引碎片在20%~40%，碎片可能成為問題，但是可以通過索引重組來消除索引解決，大規(guī)模的碎片(當碎片大于40%)，可能要求索引重建。
fragment_count	bigint	IN_ROW_DATA 分配單元的葉級別中的碎片數(shù)。  對于索引的非葉級別，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，為 NULL。 對于堆，當 mode 為 SAMPLED 時，為 NULL。
avg_fragment_size_in_pages	float	IN_ROW_DATA 分配單元的葉級別中的一個碎片的平均頁數(shù)。 對于索引的非葉級別，以及 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，為 NULL。 對于堆，當 mode 為 SAMPLED 時，為 NULL。
page_count	bigint	索引或數(shù)據(jù)頁的總數(shù)。 對于索引，表示 IN_ROW_DATA 分配單元中 b 樹的當前級別中的索引頁總數(shù)。 對于堆，表示 IN_ROW_DATA 分配單元中的數(shù)據(jù)頁總數(shù)。 對于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，表示該分配單元中的總頁數(shù)。
avg_page_space_used_in_percent	float	所有頁中使用的可用數(shù)據(jù)存儲空間的平均百分比。 對于索引，平均百分比應(yīng)用于 IN_ROW_DATA 分配單元中 b 樹的當前級別。 對于堆，表示 IN_ROW_DATA 分配單元中所有數(shù)據(jù)頁的平均百分比。 對于 LOB_DATA 或 ROW_OVERFLOW DATA 分配單元，表示該分配單元中所有頁的平均百分比。 當 mode 為 LIMITED 時，為 NULL。
record_count	bigint	總記錄數(shù)。 對于索引，記錄的總數(shù)應(yīng)用于 IN_ROW_DATA 分配單元中 b 樹(包括非葉子數(shù)據(jù)頁的數(shù)量)的當前級別。 對于堆，表示 IN_ROW_DATA 分配單元中的總記錄數(shù)。  注意 對于堆，此函數(shù)返回的記錄數(shù)可能與通過對堆運行 SELECT COUNT(*) 返回的行數(shù)不匹配。 這是因為一行可能包含多個記錄。 例如，在某些更新情況下，單個堆行可能由于更新操作而包含一條前推記錄和一條被前推記錄。 此外，多數(shù)大型 LOB 行在 LOB_DATA 存儲中拆分為多個記錄。 對于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，表示整個分配單元中總記錄數(shù)。 當 mode 為 LIMITED 時，為 NULL。
ghost_record_count	bigint	分配單元中將被虛影清除任務(wù)刪除的虛影記錄數(shù)。 對于 IN_ROW_DATA 分配單元中索引的非葉級別，為 0。 當 mode 為 LIMITED 時，為 NULL。
version_ghost_record_count	bigint	由分配單元中未完成的快照隔離事務(wù)保留的虛影記錄數(shù)。 對于 IN_ROW_DATA 分配單元中索引的非葉級別，為 0。 當 mode 為 LIMITED 時，為 NULL。
min_record_size_in_bytes	int	最小記錄大小（字節(jié)）。 對于索引，最小記錄大小應(yīng)用于 IN_ROW_DATA 分配單元中 b 樹的當前級別。 對于堆，表示 IN_ROW_DATA 分配單元中的最小記錄大小。 對于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，表示整個分配單元中的最小記錄大小。 當 mode 為 LIMITED 時，為 NULL。
max_record_size_in_bytes	int	最大記錄大小（字節(jié)）。 對于索引，最大記錄的大小應(yīng)用于 IN_ROW_DATA 分配單元中 b 樹的當前級別。 對于堆，表示 IN_ROW_DATA 分配單元中的最大記錄大小。 對于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，表示整個分配單元中的最大記錄大小。 當 mode 為 LIMITED 時，為 NULL。
avg_record_size_in_bytes	float	平均記錄大小（字節(jié)）。 對于索引，平均記錄大小應(yīng)用于 IN_ROW_DATA 分配單元中 b 樹的當前級別。 對于堆，表示 IN_ROW_DATA 分配單元中的平均記錄大小。 對于 LOB_DATA 或 ROW_OVERFLOW_DATA 分配單元，表示整個分配單元中的平均記錄大小。 當 mode 為 LIMITED 時，為 NULL。
forwarded_record_count	bigint	堆中具有指向另一個數(shù)據(jù)位置的轉(zhuǎn)向指針的記錄數(shù)。 （在更新過程中，如果在原始位置存儲新行的空間不足，將會出現(xiàn)此狀態(tài)。） 除 IN_ROW_DATA 分配單元外，對于堆的其他所有分配單元都為 NULL。 當 mode = LIMITED 時，對于堆為 NULL。
compressed_page_count	bigint	壓縮頁的數(shù)目。 對于堆，新分配的頁未進行 PAGE 壓縮。 堆在以下兩種特殊情況下進行 PAGE 壓縮：大量導(dǎo)入數(shù)據(jù)時和重新生成堆時。 導(dǎo)致頁分配的典型 DML 操作不會進行 PAGE 壓縮。 當 compressed_page_count 值增長到超過您所需的閾值時，將重新生成堆。 對于具有聚集索引的表，compressed_page_count 值表示 PAGE 壓縮的效率。

三、關(guān)于碎片的解決方法

　　1.刪除索引并重建

　　這種方式有如下缺點：

　　索引不可用：在刪除索引期間，索引不可用。

　　阻塞：卸載并重建索引會阻塞表上所有的其他請求，也可能被其他請求所阻塞。

　　對于刪除聚集索引，則會導(dǎo)致對應(yīng)的非聚集索引重建兩次(刪除時重建，建立時再重建，因為非聚集索引中有指向聚集索引的指針)。

　　唯一性約束：用于定義主鍵或者唯一性約束的索引不能使用DROP INDEX語句刪除。而且，唯一性約束和主鍵都可能被外鍵約束引用。在主鍵卸載之前，所有引用該主鍵的外鍵必須首先被刪除。盡管可以這么做，但這是一種冒險而且費時的碎片整理方法。

　　基于以上原因，不建議在生產(chǎn)數(shù)據(jù)庫，尤其是非空閑時間不建議采用這種技術(shù)。

　　2.使用DROP_EXISTING語句重建索引

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

CREATE UNIQUE CLUSTERED INDEX IX_C1 ON t1(c1)
WITH (DROP_EXISTING = ON)

　　缺點:

　　阻塞：與卸載重建方法類似，這種技術(shù)也導(dǎo)致并面臨來自其他訪問該表(或該表的索引)的查詢的阻塞問題。

　　使用約束的索引：與卸載重建不同，具有DROP_EXISTING子句的CREATE INDEX語句可以用于重新創(chuàng)建使用約束的索引。如果該約束是一個主鍵或與外鍵相關(guān)的唯一性約束，在CREATE語句中不能包含UNIQUE。

　　具有多個碎片化的索引的表：隨著表數(shù)據(jù)產(chǎn)生碎片，索引常常也碎片化。如果使用這種碎片整理技術(shù)，表上所有索引都必須單獨確認和重建。

　　3.使用ALTER INDEX REBUILD語句重建索引

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

　　阻塞：這個依然有阻塞問題。

　　事務(wù)回滾：ALTER INDEX REBUILD完全是一個原子操作，如果它在結(jié)束前停止，所有到那時為止進行的碎片整理操作都將丟失，可以通過ONLINE關(guān)鍵字減少鎖，但會造成重建時間加長。

　　4.使用ALTER INDEX REORGANIZE

　　這種方式不會重建索引，也不會生成新的頁，僅僅是整理葉級數(shù)據(jù)，不涉及非葉級，當遇到加鎖的頁時跳過，所以不會造成阻塞。但同時，整理效果會差于前三種。

　　4種索引整理技術(shù)比較：

四、填充因子FILLFACTOR

 　　重建索引能夠解決碎片的問題，但是重建索引的代碼一來需要經(jīng)常操作，二來會造成數(shù)據(jù)阻塞，影響使用。在數(shù)據(jù)比較少的情況下，重建索引代價很快，但是當索引比較大的時候，例如超過100M，那么重建索引的時間會非常長。

 　　填充因子的作用是控制索引葉子頁面中的空閑空間數(shù)量。說白了就是預(yù)留一些空間給INSERT和UPDATE。如果知道表上有很多的INSERT查詢或者索引鍵列上有足夠的UPDATE查詢，可以預(yù)先使用填充因子來增加索引葉子頁面的空閑空間已最小化頁面分割。如果表示只讀的，可以創(chuàng)建一個高填充因子來減少索引頁面的數(shù)量。

　　默認的填充因子為0，這意味著頁面將被100%充滿。

 　　填充因子的概念可以理解為預(yù)留一定的空間存放插入和更新新增加的數(shù)據(jù)，以避免頁拆分：

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

 　　填充因子值的選擇：

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

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

1. 當讀寫比例大于100：1時，不要設(shè)置填充因子，100%填充
2. 當寫的次數(shù)大于讀的次數(shù)時，設(shè)置50%-70%填充
3. 當讀寫比例位于兩者之間時80%-90%填充