無論何時(shí)對(duì)基礎(chǔ)數(shù)據(jù)執(zhí)行插入、更新或刪除操作，SQLServer數(shù)據(jù)庫(kù)引擎都會(huì)自動(dòng)維護(hù)索引。隨著時(shí)間的推移，這些修改可能會(huì)導(dǎo)致索引中的信息分散在數(shù)據(jù)庫(kù)中（含有碎片）。當(dāng)索引包含的頁(yè)中的邏輯排序（基于鍵值）與數(shù)據(jù)文件中的物理排序不匹配時(shí)，就存在碎片。碎片非常多的索引可能會(huì)降低查詢性能，導(dǎo)致應(yīng)用程序響應(yīng)緩慢。

1.索引碎片的產(chǎn)生？

由于在表里大量的插入、修改、刪除操作而使索引頁(yè)分裂。如果索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費(fèi)很多的時(shí)間，另一種情況是在查詢的時(shí)候索引根本不使用索引,都會(huì)導(dǎo)致性能降低。

2.碎片類型分為：

2.1內(nèi)部破碎

由于索引頁(yè)里的數(shù)據(jù)插入或修改操作而發(fā)生，以數(shù)據(jù)作為稀疏矩陣的形式的分布而結(jié)束，這將導(dǎo)致數(shù)據(jù)頁(yè)的增加，從而增加查詢時(shí)間。

2.2外部破碎

由于索引/數(shù)據(jù)頁(yè)的數(shù)據(jù)插入或修改而發(fā)生，以頁(yè)碼分離和在文件系統(tǒng)里不連貫的新的索引頁(yè)的分配而結(jié)束，數(shù)據(jù)庫(kù)服務(wù)器不能利用預(yù)讀操作的優(yōu)點(diǎn)，因?yàn)椋合乱粋€(gè)相關(guān)聯(lián)的數(shù)據(jù)頁(yè)不臨近，而且這些相關(guān)連的下面的頁(yè)碼可能在數(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í)行重建索引的語(yǔ)句

DECLARE@INDEXNAMECHAR(255)--索引名稱

DECLARE@DBNAMESYSNAME--數(shù)據(jù)庫(kù)名稱

DECLARE@DBNAMECHARVARCHAR(20)--數(shù)據(jù)庫(kù)名稱

DECLARE@TABLEIDCHARVARCHAR(255)--表名稱（用于遍歷索引碎片）

--檢查是否在用戶數(shù)據(jù)庫(kù)里運(yùn)行

SELECT@DBNAME=DB_NAME()

IF@DBNAMEIN('master','msdb','model','tempdb')

BEGIN

PRINT'THISPROCEDURESHOULDNOTBERUNINSYSTEMDATABASES.'

RETURN

ENDELSE

BEGIN

SET@DBNAMECHAR='DBNAME'

END

--第1階段:檢測(cè)碎片

--聲明游標(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

--對(duì)數(shù)據(jù)庫(kù)的所有表循環(huán)執(zhí)行DBCCSHOWCONTIG命令

FETCHNEXT

FROMTABLES

INTO@TABLEIDCHAR

WHILE@@FETCH_STATUS=0

BEGIN

--對(duì)表的所有索引進(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】，在【對(duì)象資源管理器】窗口里選擇【管理】——【維護(hù)計(jì)劃】選項(xiàng)。

（2）右擊【維護(hù)計(jì)劃】，在彈出的快捷菜單里選擇【維護(hù)計(jì)劃向?qū)А窟x項(xiàng)，彈出如圖所示的【維護(hù)計(jì)劃向?qū)А繉?duì)話框，單擊【下一步】按鈕

（3）彈出如圖所示【選擇目標(biāo)服務(wù)器】對(duì)話框，在【名稱】文本框里可以輸入維護(hù)計(jì)劃的名稱；在【說明】文本框里可以輸入維護(hù)計(jì)劃的說明文字；【在服務(wù)器】文本框里可以輸入要使用的服務(wù)器名；最后選擇正確的身份證信息，單擊【下一步】按鈕。

（4）彈出如圖所示【選擇維護(hù)任務(wù)】對(duì)話框，在該對(duì)話框中可以選擇執(zhí)行sql維護(hù)任務(wù)，插入執(zhí)行存儲(chǔ)過程語(yǔ)句

代碼如下:

USE[DBNAME]

GO

EXEC[dbo].[USP_IMS_DEFRAGMENT_INDEXES]

更多信息請(qǐng)查看IT技術(shù)專欄