sql server 索引碎片重组和生成

【核心代码】

USE [HCMM]
GO

/****** Object:  StoredProcedure [dbo].[sp_RefreshIndex]    Script Date: 2018/3/29 9:55:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_RefreshIndex] 
(
    @Reorganize_Fragmentation_Percent    SMALLINT = 1    -- 当逻辑碎片百分比> 5%  重新组织索引
    ,@Rebuild_Fragmentation_Percent        SMALLINT = 1    -- 当逻辑碎片百分比> 30% 重新生成索引
)
AS
BEGIN
    /* 调用方法：
.针对当前实例所有数据库：   exec sys.sp_MSforeachdb 'use ?;exec sp_RefreshIndex'
.针对当前数据库：           exec sp_RefreshIndex
    */

    --对系统数据库不作重新组织索引和重新生成索引
    IF (DB_NAME() IN ('master','model','msdb','tempdb')) RETURN;    

    --如果逻辑碎片（索引中的无序页）的百分比<= 5% ，就不作重新组织索引和重新生成索引
    IF NOT EXISTS(SELECT 1 FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) a WHERE a.index_id>0 AND a.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent) RETURN

    
    PRINT REPLICATE('-',60) CHAR(13) CHAR(10) REPLICATE(' ',14) N'对数据库' QUOTENAME(DB_NAME()) N' 进行索引优化' REPLICATE(' ',20) CHAR(13) CHAR(10)    

    DECLARE @sql NVARCHAR(2000),@str NVARCHAR(2000)

    DECLARE cur_x CURSOR FOR 
        SELECT 'alter index ' QUOTENAME(a.name) ' on ' QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) '.' QUOTENAME(OBJECT_NAME(a.object_id)) CASE WHEN b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent THEN ' reorganize;'ELSE ' rebuild;'END AS [sql]
                ,CASE WHEN b.avg_fragmentation_in_percent<=@Rebuild_Fragmentation_Percent THEN N'重新组织索引：' ELSE N'重新生成索引：'END  QUOTENAME(OBJECT_SCHEMA_NAME(a.object_id)) '.' QUOTENAME(OBJECT_NAME(a.object_id)) '.' QUOTENAME(a.name) AS [str]
            FROM sys.indexes a
                INNER JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) b ON b.object_id=a.object_id
                    AND b.index_id=a.index_id    
            WHERE a.index_id>0    
                AND b.avg_fragmentation_in_percent > @Reorganize_Fragmentation_Percent
            ORDER BY OBJECT_NAME(a.object_id),a.index_id

    OPEN cur_x
    FETCH NEXT FROM cur_x INTO @sql,@str    

    WHILE (@@fetch_status = 0)
    BEGIN

        EXEC(@sql)
        PRINT @str
        FETCH NEXT FROM cur_x INTO @sql,@str    

    END
    CLOSE cur_x
    DEALLOCATE cur_x 

END


GO