sqlserver索引周期性维护存储过程

--USE [ECOLOGY]
GO

/****** Object:  StoredProcedure [dbo].[up_idxMaintenance_d1]    Script Date: 2024/1/18 14:29:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- 创建存储过程
create or ALTER   PROCEDURE [dbo].[up_idxMaintenance_d1]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- 声明变量
    DECLARE @NoOfPartitions BIGINT;
    DECLARE @objectid INT; 
    DECLARE @indexid INT;
    DECLARE @idxname NVARCHAR(255);
    DECLARE @objname NVARCHAR(255); 
    DECLARE @partitionnum BIGINT; 
    DECLARE @schemaname NVARCHAR(255);
    DECLARE @partitions BIGINT;
    DECLARE @frag FLOAT;
    DECLARE @statement VARCHAR(8000); 
    
    -- 检查临时表是否存在，如果存在则删除
    IF OBJECT_ID('defrag_work', 'U') IS NOT NULL
        DROP TABLE defrag_work;

    -- 将碎片化大于5的索引数据复制到工作表defrag_work
    SELECT [object_id] AS objectid,
            index_id AS indexid,
            partition_number AS partition_no,
            avg_fragmentation_in_percent AS frag
    INTO defrag_work
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 5.0 and index_id > 0;
    
    -- 使用游标处理分区列表
    DECLARE partitions CURSOR FOR
        SELECT *
        FROM defrag_work;  
    OPEN partitions;
    
    -- 循环处理分区
    FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;

    WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT @objname = QUOTENAME(so.name),
                @schemaname = QUOTENAME(ss.name)
        FROM sys.objects AS so
        JOIN sys.schemas AS ss ON ss.schema_id = so.schema_id
        WHERE so.object_id = @objectid;        

        SELECT @idxname = QUOTENAME(name)
        FROM sys.indexes
        WHERE object_id = @objectid
                AND index_id = @indexid;

        SELECT @NoOfPartitions = COUNT(*)
        FROM sys.partitions
        WHERE object_id = @objectid
                AND index_id = @indexid;

        -- 根据碎片化百分比选择重新组织或重建索引
        IF (@frag < 30.0) -- @frag > 5 is already filtered in our first query, so we need that condition here
        BEGIN
            BEGIN TRY
                SELECT @statement = 'ALTER INDEX ' + @idxname + ' ON '
                        + @schemaname + '.' + @objname + ' REORGANIZE';
                IF @NoOfPartitions > 1
                    SELECT @statement = @statement + ' PARTITION='
                            + CONVERT (CHAR, @partitionnum);
                EXEC (@statement);
            END TRY
            BEGIN CATCH
                PRINT '跳过索引重组错误：' + ERROR_MESSAGE();
            END CATCH;
        END;

        IF @frag >= 30.0
        BEGIN
            BEGIN TRY
                SELECT @statement = 'ALTER INDEX ' + @idxname + ' ON '
                        + @schemaname + '.' + @objname + ' REBUILD';--  ' REBUILD WITH (ONLINE=ON)'
                IF @NoOfPartitions > 1
                    SELECT @statement = @statement + ' PARTITION='
                            + CONVERT (CHAR, @partitionnum);
                EXEC (@statement);
            END TRY
            BEGIN CATCH
                PRINT '跳过索引重建错误：' + ERROR_MESSAGE();
            END CATCH;
        END;

        PRINT 'Executed ' + @statement;

        FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
    END;

    -- 关闭并释放游标
    CLOSE partitions;
    DEALLOCATE partitions;

    -- 删除工作表
    IF OBJECT_ID('defrag_work', 'U') IS NOT NULL
        DROP TABLE defrag_work;
END;
GO