Fragmentation_Analysis存储过程执行耗时过长求优化建议

核心性能问题原因 #

原存储过程运行慢的核心问题来自多处不必要的冗余逻辑、错误配置以及无效操作：

• 循环内反复查询系统表获取元数据，索引量大时重复查询开销极高
• 索引重建后多余的统计信息更新，且采样规则WITH SAMPLE 30 ROWS完全无效，白白消耗执行时间
• 未过滤小索引：小于1000页的小索引维护收益远低于开销，不需要处理
• 未按分区维护：查询时拿到了分区号，但执行重建/重组时没有指定分区，单分区碎片高会触发全索引维护
• 游标使用默认高开销配置，动态SQL拼接物理统计查询属于多余操作

优化建议 #

• 一次性查询所有需要的元数据存入临时表，避免循环内反复查询系统表
• 移除索引重建后的多余统计更新逻辑：SQL Server在索引重建后会自动更新全量统计信息，无需手动执行；如果需要给重组后的索引更新统计，使用默认采样规则即可，不要指定30行采样
• 新增小索引过滤规则：查询物理碎片时添加page_count > 1000过滤条件，减少无效维护操作
• 分区表按分区执行维护：重建/重组索引时添加PARTITION = @Partitionnum参数，只处理碎片超标的分区
• 替换游标为低开销的LOCAL FAST_FORWARD快进只读游标，移除多余的动态SQL拼接逻辑
• 企业版SQL Server可以在索引重建时添加ONLINE = ON参数，避免锁表同时优化执行效率，还可指定MAXDOP限制并行度避免资源占满

优化后存储过程代码 #

CREATE PROCEDURE [dbo].[Fragmentation_Analysis]  
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Dbid Int = DB_ID(),
            @Objectid Int, 
            @Indexid Int, 
            @Partitionnum Int, 
            @Frag Float,
            @Schemaname Sysname, 
            @Objectname Sysname,
            @Indexname Sysname,
            @Qry NVARCHAR(MAX)

    IF OBJECT_ID('Tempdb..#FragTabInfo') IS NOT NULL
        DROP TABLE #FragTabInfo

    -- 一次性查询所有需要的元数据，避免循环内重复查询
    CREATE TABLE #FragTabInfo
    (
         Objectid Int, 
         Indexid Int, 
         Partitionnum Int, 
         Frag Float,
         Schemaname Sysname,
         Objectname Sysname,
         Indexname Sysname
    )

    INSERT INTO #FragTabInfo (Objectid, Indexid, Partitionnum, Frag, Schemaname, Objectname, Indexname)
    SELECT 
        ips.object_id AS Objectid, 
        ips.index_id AS Indexid, 
        ips.partition_number AS Partitionnum,  
        ips.avg_fragmentation_in_percent AS Frag,
        s.name AS Schemaname,
        o.name AS Objectname,
        i.name AS Indexname
    FROM sys.dm_db_index_physical_stats(@Dbid, NULL, NULL, NULL, 'Limited') ips
    JOIN sys.objects o ON ips.object_id = o.object_id
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10.0 
      AND ips.index_id > 0
      AND ips.page_count > 1000 -- 过滤小索引，减少无效操作

    IF NOT EXISTS(SELECT 1 FROM #FragTabInfo)
    BEGIN
        PRINT '所有索引碎片率均<=10%或无需要维护的索引'
        RETURN
    END

    -- 使用低开销快进只读游标
    DECLARE Partitions CURSOR LOCAL FAST_FORWARD FOR
        SELECT Objectid, Indexid, Partitionnum, Frag, Schemaname, Objectname, Indexname
        FROM #FragTabInfo 
        ORDER BY Objectname

    OPEN Partitions
    FETCH NEXT FROM Partitions INTO @Objectid, @Indexid, @Partitionnum, @Frag, @Schemaname, @Objectname, @Indexname

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 根据碎片率选择维护方式
        IF @Frag < 30.0
        BEGIN
            SELECT @Qry = N'ALTER INDEX ' + QUOTENAME(@Indexname) + N' ON ' + QUOTENAME(@Schemaname) + N'.' + QUOTENAME(@Objectname) 
                        + N' REORGANIZE PARTITION = ' + CAST(@Partitionnum AS NVARCHAR(10)) + N';'
            EXEC sp_executesql @Qry
            -- 重组索引需要手动更新统计信息时打开下方注释，不要指定30行采样
            -- SET @Qry = N'UPDATE STATISTICS ' + QUOTENAME(@Schemaname) + N'.' + QUOTENAME(@Objectname) + N' ' + QUOTENAME(@Indexname) + N';'
            -- EXEC sp_executesql @Qry
        END
        ELSE
        BEGIN
            -- 企业版可添加 ONLINE=ON 、MAXDOP=X参数优化
            SELECT @Qry = N'ALTER INDEX ' + QUOTENAME(@Indexname) + N' ON ' + QUOTENAME(@Schemaname) + N'.' + QUOTENAME(@Objectname) 
                        + N' REBUILD PARTITION = ' + CAST(@Partitionnum AS NVARCHAR(10)) + N' WITH (ONLINE = OFF);'
            EXEC sp_executesql @Qry
        END

        PRINT N'已执行：' + @Qry + N'，碎片率：' + CAST(@Frag AS NVARCHAR(20)) + N'%'
    
        FETCH NEXT FROM Partitions INTO @Objectid, @Indexid, @Partitionnum, @Frag, @Schemaname, @Objectname, @Indexname
    END

    CLOSE Partitions
    DEALLOCATE Partitions

    DROP TABLE #FragTabInfo
END

内容的提问来源于stack exchange，提问作者Chowdary