利用存储过程进行选择性的进行碎片整理

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

AS --王成辉翻译整理，转贴请注明出处

--声明变量

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

DECLARE @tableidchar VARCHAR(255)

--检查是否在用户数据库里运行

SELECT @dbname = db_name()

IF @dbname IN ('master', 'msdb', 'model', 'tempdb')

BEGIN

PRINT 'This procedure should not be run in system databases.'

RETURN

END

--第1阶段:检测碎片

--声明游标

DECLARE tables CURSOR FOR

SELECT convert(varchar,so.id)

FROM sysobjects so

JOIN sysindexes si

ON so.id = si.id

WHERE so.type ='U'

AND si.indid < 2

AND si.rows > 0

-- 创建一个临时表来存储碎片信息

CREATE TABLE #fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

--打开游标

OPEN tables

-- 对数据库的所有表循环执行dbcc showcontig命令

FETCH NEXT

FROM tables

INTO @tableidchar

WHILE @@FETCH_STATUS = 0

BEGIN

--对表的所有索引进行统计

INSERT INTO #fraglist

EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT

FROM tables

INTO @tableidchar

END

-- 关闭释放游标

CLOSE tables

DEALLOCATE tables

-- 为了检查，报告统计结果

SELECT * FROM #fraglist

--第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity

FROM #fraglist f

JOIN sysobjects so ON f.ObjectId=so.id

WHERE ScanDensity <= @maxfrag

AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

-- 输出开始时间

SELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

--打开游标

OPEN indexes

--循环所有的索引

FETCH NEXT

FROM indexes

INTO @tablename, @objectowner, @objectid, @indexname, @frag

WHILE @@FETCH_STATUS = 0

BEGIN

SET QUOTED_IDENTIFIER ON

SELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' +

', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS'

SELECT 'Now executing: '

SELECT(@execstr)

EXEC (@execstr)

SET QUOTED_IDENTIFIER OFF

FETCH NEXT

FROM indexes

INTO @tablename, @objectowner, @objectid, @indexname, @frag

END

-- 关闭释放游标

CLOSE indexes

DEALLOCATE indexes

-- 报告结束时间

SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())

-- 删除临时表

DROP TABLE #fraglist

GO