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

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

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

2.碎片類型分為：

2.1內(nèi)部破碎

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

2.2外部破碎

由于索引/數(shù)據(jù)頁的數(shù)據(jù)插入或修改而發(fā)生，以頁碼分離和在文件系統(tǒng)里不連貫的新的索引頁的分配而結(jié)束，數(shù)據(jù)庫服務器不能利用預讀操作的優(yōu)點，因為：下一個相關聯(lián)的數(shù)據(jù)頁不臨近，而且這些相關連的下面的頁碼可能在數(shù)據(jù)文件的任何地方。

自動重建發(fā)生碎片的索引

在數(shù)據(jù)中新建碎片整理存儲過程

代碼如下:

--================================================

--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ù)庫里運行

SELECT@DBNAME=DB_NAME()

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

BEGIN

PRINT'THISPROCEDURESHOULDNOTBERUNINSYSTEMDATABASES.'

RETURN

ENDELSE

BEGIN

SET@DBNAMECHAR='DBNAME'

END

--第1階段:檢測碎片

--聲明游標

DECLARETABLESCURSORFOR

SELECTCONVERT(VARCHAR,SO.ID)

FROMSYSOBJECTSSO

JOINSYSINDEXESSI

ONSO.ID=SI.ID

WHERESO.TYPE='U'

ANDSI.INDID<2

ANDSI.ROWS>0

--創(chuàng)建一個臨時表來存儲碎片信息

CREATETABLE#FRAGLIST(

TABLENAMECHAR(255),

INDEXNAMECHAR(255))

--打開游標

OPENTABLES

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

FETCHNEXT

FROMTABLES

INTO@TABLEIDCHAR

WHILE@@FETCH_STATUS=0

BEGIN

--對表的所有索引進行統(tǒng)計

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

--關閉釋放游標

CLOSETABLES

DEALLOCATETABLES

--為了檢查，報告統(tǒng)計結(jié)果

SELECT*FROM#FRAGLIST

--第2階段:(整理碎片)為每一個要整理碎片的索引聲明游標

DECLAREINDEXESCURSORFOR

SELECTTABLENAME,INDEXNAME

FROM#FRAGLIST

--輸出開始時間

SELECT'STARTEDDEFRAGMENTINGINDEXESAT'+CONVERT(VARCHAR,GETDATE())

--打開游標

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

--關閉釋放游標

CLOSEINDEXES

DEALLOCATEINDEXES

--報告結(jié)束時間

SELECT'FINISHEDDEFRAGMENTINGINDEXESAT'+CONVERT(VARCHAR,GETDATE())

--刪除臨時表

DROPTABLE#FRAGLIST

GO

GO

設置定時執(zhí)行步驟

（1）啟動【sqlserverManagementStudio】，在【對象資源管理器】窗口里選擇【管理】——【維護計劃】選項。

（2）右擊【維護計劃】，在彈出的快捷菜單里選擇【維護計劃向?qū)А窟x項，彈出如圖所示的【維護計劃向?qū)А繉υ捒?，單擊【下一步】按鈕

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

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

代碼如下:

USE[DBNAME]

GO

EXEC[dbo].[USP_IMS_DEFRAGMENT_INDEXES]

更多信息請查看IT技術專欄