如何通过SQL Agent作业在实例所有非Master库运行索引优化查询？

问题描述 #

尝试通过SQL Agent作业在同一SQL实例的所有数据库（排除master库）中执行索引优化查询，但创建的作业仅能在单个数据库运行，附上作业配置截图及原查询代码寻求解决办法。

问题根源 #

1. 原脚本开头固定USE [DBNAME]，绑定了单个数据库，无法跨库执行
2. 未实现遍历实例内所有目标数据库的逻辑，仅查询当前库的索引信息
3. 作业步骤若指定了单个数据库，会限制脚本的执行范围

修正方案 #

1. 替换为跨库索引优化脚本 #

以下脚本会自动遍历实例中所有在线的用户数据库（排除master、tempdb、model、msdb系统库），并根据索引碎片比例执行对应的优化操作：

SET NOCOUNT ON

DECLARE @DBName NVARCHAR(128)
DECLARE @SQL NVARCHAR(MAX)

-- 游标遍历所有符合条件的用户数据库
DECLARE DB_Cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
  AND state_desc = 'ONLINE'

OPEN DB_Cursor
FETCH NEXT FROM DB_Cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 构造当前数据库的索引优化逻辑
    SET @SQL = N'USE [' + @DBName + N'];
    DECLARE @Objectid INT, @Indexid INT,
            @schemaname VARCHAR(100), @tablename VARCHAR(300),
            @ixname VARCHAR(500),
            @avg_fragment float, @command VARCHAR(4000)

    DECLARE AWS_Cursor CURSOR FOR
        SELECT 
            A.object_id, A.index_id,
            QUOTENAME(SS.NAME) AS schemaname,
            QUOTENAME(OBJECT_NAME(B.object_id, B.database_id)) AS tablename,
            QUOTENAME(A.name) AS ixname,
            B.avg_fragmentation_in_percent AS avg_fragment 
        FROM 
            sys.indexes A 
        INNER JOIN 
            sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''LIMITED'') AS B ON A.object_id = B.object_id 
                                                                                      AND A.index_id = B.index_id
        INNER JOIN 
            sys.OBJECTS OS ON A.object_id = OS.object_id
        INNER JOIN 
            sys.schemas SS ON OS.schema_id = SS.schema_id
        WHERE 
            B.avg_fragmentation_in_percent > 5  -- 调整碎片阈值，覆盖需要优化的范围
            AND A.index_id > 0 
            AND A.IS_DISABLED <> 1
        ORDER BY 
            tablename,ixname

    OPEN AWS_Cursor

    FETCH NEXT FROM AWS_Cursor INTO @Objectid, @Indexid, @schemaname, @tablename, @ixname, @avg_fragment

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 按碎片比例选择优化方式：>30%重建，5%-30%重组
        IF @avg_fragment >= 30.0
        BEGIN
            SET @command = N''ALTER INDEX ''+@ixname+N'' ON ''+@schemaname+N''.''+ @tablename+N'' REBUILD WITH (ONLINE = ON)'';
        END
        ELSE IF @avg_fragment BETWEEN 5.0 AND 29.9
        BEGIN
            SET @command = N''ALTER INDEX ''+@ixname+N'' ON ''+@schemaname+N''.''+ @tablename+N'' REORGANIZE'';
        END

        IF @command IS NOT NULL
        BEGIN
            EXEC(@command)
            SET @command = NULL
        END

        FETCH NEXT FROM AWS_Cursor INTO @Objectid, @Indexid, @schemaname, @tablename, @ixname, @avg_fragment
    END

    CLOSE AWS_Cursor
    DEALLOCATE AWS_Cursor'

    -- 执行当前数据库的优化脚本
    EXEC sp_executesql @SQL

    FETCH NEXT FROM DB_Cursor INTO @DBName
END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

2. 调整SQL Agent作业配置 #

• 打开作业的步骤编辑界面，将数据库选择为master（或任意系统库，脚本会自行切换目标库）
• 确保步骤类型为Transact-SQL (T-SQL)，将上述修正后的脚本粘贴到命令框中保存

重要提示 #

• 原脚本存在逻辑错误：同时赋值REBUILD和REORGANIZE给@command，最终只会执行后者，修正后按碎片比例区分操作
• ONLINE = ON仅支持SQL Server企业版，若使用其他版本请移除该参数，避免执行报错
• 建议在业务低峰时段执行作业，减少对正常业务的影响

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