索引的碎片整理SQL语句

╰+哭是因爲堅強的太久メ 2022-03-08 06:46 352阅读 0赞

原文出处：[https://blog.csdn.net/iteye\_2210/article/details/81622929][https_blog.csdn.net_iteye_2210_article_details_81622929]

SET NOCOUNT ON
    DECLARE @tablename VARCHAR (128)
    DECLARE @execstr VARCHAR (255)
    DECLARE @objectid INT
    DECLARE @indexid INT
    DECLARE @frag DECIMAL
    DECLARE @maxfrag DECIMAL
    
    --设置最大允许的碎片数量,超过则对索引进行碎片整理
    SET @maxfrag = 30.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
    )
    
    --获取当前数据库中所有数据表的索引碎片信息,并保存到临时表
    DECLARE tables CURSOR FOR 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
    OPEN tables
    FETCH NEXT FROM tables INTO @tablename
    WHILE @@FETCH_STATUS = 0 BEGIN
    	INSERT INTO #fraglist 
    	EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') 
    		WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
    
    	FETCH NEXT FROM tables INTO @tablename
    END
    CLOSE tables
    DEALLOCATE tables
    
    /*
    ObjectName:数据表名称
    IndexId:索引ID
    LogicalFrag:逻辑碎片值
    */
    DECLARE indexes CURSOR FOR 
    SELECT ObjectName, ObjectId, IndexId, LogicalFrag 
    FROM #fraglist
    WHERE LogicalFrag >= @maxfrag
    	AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
    OPEN indexes
    FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
    WHILE @@FETCH_STATUS = 0 BEGIN
    	PRINT '正在执行 DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
    		' + RTRIM(@indexid) + ') - fragmentation currently '
    		+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
    
    	--执行索引碎片整理
    	SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'
    	EXEC (@execstr)
    
    	FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
    END
    CLOSE indexes
    DEALLOCATE indexes
    DROP TABLE #fraglist
    GO

[https_blog.csdn.net_iteye_2210_article_details_81622929]: https://blog.csdn.net/iteye_2210/article/details/81622929