無論何時(shí)對基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQLServer數(shù)據(jù)庫引擎都會(huì)自動(dòng)維護(hù)索引。隨著時(shí)間的推移，這些修改可能會(huì)導(dǎo)致索引中的信息分散在數(shù)據(jù)庫中（含有碎片）。當(dāng)索引包含的頁中的邏輯排序（基于鍵值）與數(shù)據(jù)文件中的物理排序不匹配時(shí)，就存在碎片。碎片非常多的索引可能會(huì)降低查詢性能，導(dǎo)致應(yīng)用程序響應(yīng)緩慢。
1.索引碎片的產(chǎn)生？
由于在表里大量的插入、修改、刪除操作而使索引頁分裂。如果索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費(fèi)很多的時(shí)間，另一種情況是在查詢的時(shí)候索引根本不使用索引,都會(huì)導(dǎo)致性能降低。
2.碎片類型分為：
2.1內(nèi)部破碎
由于索引頁里的數(shù)據(jù)插入或修改操作而發(fā)生，以數(shù)據(jù)作為稀疏矩陣的形式的分布而結(jié)束，這將導(dǎo)致數(shù)據(jù)頁的增加，從而增加查詢時(shí)間。
2.2外部破碎
由于索引/數(shù)據(jù)頁的數(shù)據(jù)插入或修改而發(fā)生，以頁碼分離和在文件系統(tǒng)里不連貫的新的索引頁的分配而結(jié)束，數(shù)據(jù)庫服務(wù)器不能利用預(yù)讀操作的優(yōu)點(diǎn)，因?yàn)椋合乱粋€(gè)相關(guān)聯(lián)的數(shù)據(jù)頁不臨近，而且這些相關(guān)連的下面的頁碼可能在數(shù)據(jù)文件的任何地方。
自動(dòng)重建發(fā)生碎片的索引
在數(shù)據(jù)中新建碎片整理存儲(chǔ)過程
代碼如下:
--================================================
--TEMPLATEGENERATEDFROMTEMPLATEEXPLORERUSING:
--CREATEPROCEDURE(NEWMENU).SQL
--
--USETHESPECIFYVALUESFORTEMPLATEPARAMETERS
--COMMAND(CTRL-SHIFT-M)TOFILLINTHEPARAMETER
--VALUESBELOW.
--
--THISBLOCKOFCOMMENTSWILLNOTBEINCLUDEDIN
--THEDEFINITIONOFTHEPROCEDURE.
--================================================
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
--=============================================
--AUTHOR: <AUTHOR,,WUXIANGQIAN>
--CREATEDATE:<CREATEDATE,2014-05-16>
--DESCRIPTION:<DESCRIPTION,重建出現(xiàn)碎片的索引>
--=============================================
ALTERPROCEDUREUSP_IMS_DEFRAGMENT_INDEXES
AS
--聲明變量
SETNOCOUNTON
DECLARE@TABLENAMEVARCHAR(128)--表名稱（已發(fā)生索引碎片）
DECLARE@EXECSTRVARCHAR(255)--執(zhí)行重建索引的語句
DECLARE@INDEXNAMECHAR(255)--索引名稱
DECLARE@DBNAMESYSNAME--數(shù)據(jù)庫名稱
DECLARE@DBNAMECHARVARCHAR(20)--數(shù)據(jù)庫名稱
DECLARE@TABLEIDCHARVARCHAR(255)--表名稱（用于遍歷索引碎片）
--檢查是否在用戶數(shù)據(jù)庫里運(yùn)行
SELECT@DBNAME=DB_NAME()
IF@DBNAMEIN('master','msdb','model','tempdb')
BEGIN
PRINT'THISPROCEDURESHOULDNOTBERUNINSYSTEMDATABASES.'
RETURN
ENDELSE
BEGIN
SET@DBNAMECHAR='DBNAME'
END
--第1階段:檢測碎片
--聲明游標(biāo)
DECLARETABLESCURSORFOR
SELECTCONVERT(VARCHAR,SO.ID)
FROMSYSOBJECTSSO
JOINSYSINDEXESSI
ONSO.ID=SI.ID
WHERESO.TYPE='U'
ANDSI.INDID<2
ANDSI.ROWS>0
--創(chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)碎片信息
CREATETABLE#FRAGLIST(
TABLENAMECHAR(255),
INDEXNAMECHAR(255))
--打開游標(biāo)
OPENTABLES
--對數(shù)據(jù)庫的所有表循環(huán)執(zhí)行DBCCSHOWCONTIG命令
FETCHNEXT
FROMTABLES
INTO@TABLEIDCHAR
WHILE@@FETCH_STATUS=0
BEGIN
--對表的所有索引進(jìn)行統(tǒng)計(jì)
INSERTINTO#FRAGLIST
EXEC('SELECTOBJECT_NAME(DT.OBJECT_ID)ASTABLENAME,SI.NAMEASINDEXNAMEFROM'+
'(SELECTOBJECT_ID,INDEX_ID,AVG_FRAGMENTATION_IN_PERCENT,AVG_PAGE_SPACE_USED_IN_PERCENT'+
'FROMSYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID('''+@DBNAMECHAR+'''),object_id('''+@TABLEIDCHAR+''')'+
',NULL,NULL,''DETAILED'')WHEREINDEX_ID<>0)ASDTINNERJOINSYS.INDEXESSI'+
'ONSI.OBJECT_ID=DT.OBJECT_IDANDSI.INDEX_ID=DT.INDEX_IDAND'+
'DT.AVG_FRAGMENTATION_IN_PERCENT>10'+
'ANDDT.AVG_PAGE_SPACE_USED_IN_PERCENT<75ORDERBYDT.AVG_FRAGMENTATION_IN_PERCENTDESC')
FETCHNEXT
FROMTABLES
INTO@TABLEIDCHAR
END
--關(guān)閉釋放游標(biāo)
CLOSETABLES
DEALLOCATETABLES
--為了檢查，報(bào)告統(tǒng)計(jì)結(jié)果
SELECT*FROM#FRAGLIST
--第2階段:(整理碎片)為每一個(gè)要整理碎片的索引聲明游標(biāo)
DECLAREINDEXESCURSORFOR
SELECTTABLENAME,INDEXNAME
FROM#FRAGLIST
--輸出開始時(shí)間
SELECT'STARTEDDEFRAGMENTINGINDEXESAT'+CONVERT(VARCHAR,GETDATE())
--打開游標(biāo)
OPENINDEXES
--循環(huán)所有的索引
FETCHNEXT
FROMINDEXES
INTO@TABLENAME,@INDEXNAME
WHILE@@FETCH_STATUS=0
BEGIN
SETQUOTED_IDENTIFIERON
SELECT@EXECSTR='ALTERINDEX'+@INDEXNAME+'ON'+@TABLENAME+'REBUILDWITH(FILLFACTOR=90,ONLINE=ON)'
SELECT'Nowexecuting:'
SELECT(@EXECSTR)
EXEC(@EXECSTR)
SETQUOTED_IDENTIFIEROFF
FETCHNEXT
FROMINDEXES
INTO@TABLENAME,@INDEXNAME
END
--關(guān)閉釋放游標(biāo)
CLOSEINDEXES
DEALLOCATEINDEXES
--報(bào)告結(jié)束時(shí)間
SELECT'FINISHEDDEFRAGMENTINGINDEXESAT'+CONVERT(VARCHAR,GETDATE())
--刪除臨時(shí)表
DROPTABLE#FRAGLIST
GO
GO
設(shè)置定時(shí)執(zhí)行步驟
（1）啟動(dòng)【sqlserverManagementStudio】，在【對象資源管理器】窗口里選擇【管理】——【維護(hù)計(jì)劃】選項(xiàng)。
（2）右擊【維護(hù)計(jì)劃】，在彈出的快捷菜單里選擇【維護(hù)計(jì)劃向?qū)А窟x項(xiàng)，彈出如圖所示的【維護(hù)計(jì)劃向?qū)А繉υ捒颍瑔螕簟鞠乱徊健堪粹o
（3）彈出如圖所示【選擇目標(biāo)服務(wù)器】對話框，在【名稱】文本框里可以輸入維護(hù)計(jì)劃的名稱；在【說明】文本框里可以輸入維護(hù)計(jì)劃的說明文字；【在服務(wù)器】文本框里可以輸入要使用的服務(wù)器名；最后選擇正確的身份證信息，單擊【下一步】按鈕。
（4）彈出如圖所示【選擇維護(hù)任務(wù)】對話框，在該對話框中可以選擇執(zhí)行sql維護(hù)任務(wù)，插入執(zhí)行存儲(chǔ)過程語句
代碼如下:
USE[DBNAME]
GO
EXEC[dbo].[USP_IMS_DEFRAGMENT_INDEXES]