MSSQL自動重建出現(xiàn)碎片的索引的方法分享

1、無論何時對基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQLServer數(shù)據(jù)庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導(dǎo)致索引中的信息分散在數(shù)據(jù)庫中（含有碎片）。當(dāng)索引包含的頁中的邏輯排序 （基于鍵值）與數(shù)據(jù)文件中的物理排序不匹配時，就存在碎片。碎片非常多的索引可能會降低查詢性能，導(dǎo)致應(yīng)用程序響應(yīng)緩慢。1索引碎片的產(chǎn)生？由于在表里大量的插入、修改、刪除操作而使索引頁分裂。如果索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費很多的時間，另一種情況是在查詢的時候索引根本不使用索引，都會導(dǎo)致性能降低。2碎片類型分為：2.1內(nèi)部破碎由于索引頁里的數(shù)據(jù)插入或修改操作而發(fā)生，以數(shù)據(jù)作為稀疏矩陣的

2、形式的分布而結(jié) 束，這將導(dǎo)致數(shù)據(jù)頁的增加，從而增加查詢時間。2.2外部破碎由于索引/數(shù)據(jù)頁的數(shù)據(jù)插入或修改而發(fā)生，以頁碼分離和在文件系統(tǒng)里不連貫的新的 索引頁的分配而結(jié)束，數(shù)據(jù)庫服務(wù)器不能利用預(yù)讀操作的優(yōu)點，因為：下一個相關(guān)聯(lián)的數(shù)據(jù)頁不臨近，而且這些相關(guān)連的下面的頁碼可能在數(shù)據(jù)文件的任何地方。自動重建發(fā)生碎片的索引在數(shù)據(jù)中新建碎片整理存儲過程代碼如下：-TEMPLATEGENERATEDFROMTEMPLATEEXPLORERUSING:-CREATEPROCEDURE(NEWMENU).SQL-USETHESPECIFYV ALUESFORTEMPLATEPARAMETERS-COMMAND

3、(CTRL-SHIFT-M)TOFILLINTHEPARAMETER -VALUESBELOW.-THISBLOCKOFCOMMENTSWILLNOTBEINCLUDEDIN -THEDEFINITIONOFTHEPROCEDURE.SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGO-AUTHOR:&n bsp;<AUTHOR,WUXIANGQIA N>-CREATEDA TE:<CREATEDA TE,2014-05-16> -DESCRIPTIO N:& lt;DESCRIPTION,重建出現(xiàn)碎片的索引 >ALTERPROCED

4、UREUSP_IMS_DEFRAGMENT_INDEXESAS-聲明變量SETNOCOUNTONDECLARETABLENAMEV ARCHAR(128)-表名稱(已發(fā)生索引碎片) DECLAREEXECSTRV ARCHAR(255)-執(zhí)行重建索引的語句 DECLAREINDEXNAMECHAR(255)-索引名稱DECLAREDBNAMESYSNAME-數(shù)據(jù)庫名稱DECLAREDBNAMECHARV ARCHAR(20)-數(shù)據(jù)庫名稱DECLARETABLEIDCHARV ARCHAR(255)-表名稱(用于遍歷索引碎片) -檢查是否在用戶數(shù)據(jù)庫里運行SELECTDBNAME=DB_NAME

5、()IFDBNAMEIN(master,msdb,model,tempdb)BEGINPRINTTHISPROCEDURESHOULDNOTBERUNINSYSTEMDATABASES. RETURNENDELSEBEGINSETDBNAMECHAR=DBNAMEEND-第1階段:檢測碎片-聲明游標(biāo)DECLARETABLESCURSORFORSELECTCONVERT(V ARCHAR,SO.ID)FROMSYSOBJECTSSOJOINSYSINDEXESSIONSO.ID=SI.IDWHERESO.TYPE=UANDSI.INDID<2ANDSI.ROWS>O-創(chuàng)建一個臨時表來

6、存儲碎片信息CREATETABLE#FRAGLIST(TABLENAMECHAR(255),INDEXNAMECHAR(255)-打開游標(biāo)OPENTABLES-對數(shù)據(jù)庫的所有表循環(huán)執(zhí)行DBCCSHOWCONTIG 命令FETCHNEXTFROMTABLESINTOTABLEIDCHARWHILEFETCH_STA TUS=0BEGIN-對表的所有索引進行統(tǒng)計INSERTINTO#FRAGLISTEXEC(SELECTOBJECT_NAME(DT.OBJECT_ID)ASTABLENAME,SI.NAMEASINDEXNAMEFROM+(SELECTOBJECT_ID,INDEX_ID,A VG

7、_FRAGMENTATION_IN_PERCENT,A VG_PAGE_SPA CE_USED_IN_PERCENT+FROMSYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(”+DBNAMECHAR+”),object_id(”+TABLEIDCHAR+”)+,NULL,NULL,”DETAILED”)WHEREINDEX_ID&l t; >0)ASDTINNERJOINSYS.INDEXESSI+ONSI.OBJECT_ID=DT.OBJECT_IDANDSINDEX_ID=DTNDEX_IDAND+ DT.AVG_FRAGMENTATION_IN_PERCE

8、NT>10+ANDDT.A VG_PAGE_SPACE_USED_IN_PERCENT<75ORDERBYDT.A VG_FRAGMENTA TION_IN_PERCENTDESC)FETCHNEXTFROMTABLESINTOTABLEIDCHAREND-關(guān)閉釋放游標(biāo)CLOSETABLESDEALLOCATETABLES-為了檢查，報告統(tǒng)計結(jié)果SELECT*FROM#FRAGLIST-第2階段:(整理碎片)為每一個要整理碎片的索引聲明游標(biāo)DECLAREINDEXESCURSORFORSELECTTABLENAME,INDEXNAMEFROM#FRAGLIST-輸出開始時間SELE

9、CTSTARTEDDEFRAGMENTINGINDEXESAT+CONVERT(V ARCHAR,GETDATE()-打開游標(biāo)OPENINDEXES-循環(huán)所有的索引FETCHNEXTFROMINDEXESINTOTABLENAME,INDEXNAMEWHILEFETCH_STA TUS=0BEGINSETQUOTED_IDENTIFIERONSELECTEXECSTR=ALTERINDEX+INDEXNAME+ON+TABLENAME+REBUILDWITH(FILLFACTOR=9O,ONLINE=ON)SELECTNowexecuti ng:SELECT(EXECSTR)EXEC(EXEC

10、STR)SETQUOTED_IDENTIFIEROFFFETCHNEXTFROMINDEXESINTOTABLENAME,INDEXNAMEEND-關(guān)閉釋放游標(biāo)CLOSEINDEXESDEALLOCATEINDEXES-報告結(jié)束時間SELECTFINISHEDDEFRAGMENTINGINDEXESAT+CONVERT(V ARCHAR,GETDATE()-刪除臨時表DROPTABLE#FRAGLISTGOGO設(shè)置定時執(zhí)行步驟(1) 啟動【sqlserverManagementStudio】，在【對象資源管理器】窗口里選擇【管理】 【維護計劃】選項。(2) 右擊【維護計劃】，在彈出的快捷菜單里選擇【維護計劃向?qū)А窟x項，彈出如圖所 示的【維護計劃向?qū)А繉υ捒颍瑔螕簟鞠乱徊健堪粹o(3) 彈出如圖所示【選擇目標(biāo)服務(wù)器】對話框，在【名稱】文本框里可以輸入維護計劃的名稱；在【說明】文本框里可以輸入維護計劃的說明文字