MSSQL中整理索引碎片的存储过程

最新推荐文章于 2021-11-16 20:48:01 发布

转载最新推荐文章于 2021-11-16 20:48:01 发布 · 864 阅读

文章标签：

#索引碎片 #ms sql

MSSQL 专栏收录该内容

2 篇文章

订阅专栏

本文提供了一个SQL脚本，用于批量检测并整理数据库中表的碎片，提高数据库性能。脚本首先检测碎片，然后对扫描密度低于指定阈值的索引进行整理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_defragment_indexes]') AND type in (N'P', N'PC'))
  Drop procedure sp_defragment_indexes
GO

EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE [dbo].[sp_defragment_indexes] @maxfrag DECIMAL, @LIKENAME VARCHAR(20)

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阶段:检测碎片

	--声明游标
	if @LIKENAME=''''
	  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
	else
	  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 AND so.name LIKE ''%''+@LIKENAME+''%''

	-- 创建一个临时表来存储碎片信息
	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 ORDER BY ObjectName 
	-- 第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
	   ORDER BY ScanDensity 
	-- 输出开始时间
--	SELECT ''Started defragmenting indexes at '' + CONVERT(VARCHAR,GETDATE(),113)
        Print ''Started defragmenting indexes at '' + CONVERT(VARCHAR,GETDATE(),113)

	--打开游标
	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)
        Print ''Now executing: ''+ @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(),113)
        Print ''Finished defragmenting indexes at '' + CONVERT(VARCHAR,GETDATE(),113)

	-- 删除临时表
	DROP TABLE #fraglist' 
END
GO

GRANT EXECUTE ON  SP_DEFRAGMENT_INDEXES TO PUBLIC
GO