利用存储过程进行选择性的进行碎片整理
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
