SQL重建索引处理

Question

我有一个有25个大型数据库的例子。

我已经设置了一个rebuild_all_indexes作业来重建每个数据库上的所有索引。

由于数据库的大容量和OLTP环境，这项工作大约需要3,4天才能完成。

有人能帮我解决这个问题吗？

作为作业步骤，我使用了一些存储过程，调用如下：

exec database1.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database2.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database3.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database4.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database5.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database6.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database7.dbo.SP_JOBS_REBUILD_DB_INDEXES
GO
exec database8.dbo.SP_JOBS_REBUILD_DB_INDEXES
go

...

exec database25.dbo.sp_jobs_rebuild_db_indexes
go

Answer 1

尝试使用Ola Halengren的Server维护解决方案。

它有很多选项，最重要的是-它跳过不需要维护的索引(取决于您如何配置它)。

索引维护几乎没有什么值得记住的事情。根据我的经验，在更大的数据库上进行全面重建的3-4天并不多，这取决于您的存储。

在某些情况下，不需要rebuild，index reorganize通常足以分割5%到30% (如微软在网上图书中建议的那样)，任何类型的索引维护建议只对大于1000页的索引进行，而对于大于50000页的索引(如声明的这里)，性能差异可能是显而易见的。

在大多数情况下，索引碎片整理也不是性能问题的根源，它只是掩盖了隐藏在下面的其他问题。经常对它们进行碎片整理不会提高您的性能，而且会扩展您的维护窗口。

另一件事是，维护在IO和CPU上造成了明显的负载，重建/重组操作是在事务日志中编写的(对于FULL RECOVERY模式下的数据库)。所以您的日志文件将变得更大，与日志备份一样。

更多关于Brent Ozar博客文章中索引维护的文章：

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

https://www.brentozar.com/archive/2014/04/index-fragmentation-bad-statistics-arent-always-problem-video/

Answer 2

我知道人们会推荐Ola Halengren的脚本，但我总是回避它们(尽管它们是资源)，因为我想成为我们的维护脚本的架构师，并自己创建它们，从而加深了我对索引和维护计划的总体理解。

因此，考虑到这一点，这里有一个非常灵活和轻量级的脚本，它只在必要时重新构建(或重新组织)索引，这是由碎片百分比阈值定义的。

使用如下所示的更具选择性的脚本，我已经能够大幅度减少索引维护时间。我们在大型数据库上的Management中设计的一些旧的MP已经减少了15个小时。

--Fragmentation Thresholds
DECLARE @lower AS int = 10
DECLARE @upper AS int = 30

--Options
DECLARE @fillfactor AS int = 98
DECLARE @onlinestatus AS varchar(3) = 'OFF'

--Assess index fragmentation and determine required action based on thresholds
DECLARE @Indexes AS TABLE (
    ID INT IDENTITY(1, 1) NOT NULL
    ,TableNm VARCHAR(500) NOT NULL
    ,IndexNm VARCHAR(500) NOT NULL
    ,FragPerc DECIMAL(16, 3) NOT NULL
    ,RecAction VARCHAR(50) NOT NULL
    ,PageCount INT NOT NULL
    )

INSERT INTO @Indexes
SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE 
        WHEN indexstats.avg_fragmentation_in_percent < @lower
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= @lower
            AND indexstats.avg_fragmentation_in_percent < @upper
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= @upper
            THEN 'REBUILD'
        END AS RecAction
    ,indexstats.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
    AND page_count > 1000 --because there is next to no value doing anything about indexes with less than 1000 pages.
    AND dbindexes.NAME IS NOT NULL --don't worry about heaps
ORDER BY indexstats.avg_fragmentation_in_percent DESC

DELETE FROM @Indexes WHERE RecAction = 'NOTHING'

--Iterate through list and perform required action

DECLARE @sql AS VARCHAR(MAX)
DECLARE @whilecount AS INT = 1
DECLARE @rowcount AS INT = (
        SELECT MAX(ID)
        FROM @Indexes
        )
DECLARE @IndexNm AS VARCHAR(500)
DECLARE @TableNm AS VARCHAR(500)
DECLARE @RecAction AS VARCHAR(50)

WHILE @whilecount <= @rowcount
BEGIN
    SET @IndexNm = (
            SELECT IndexNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @TableNm = (
            SELECT TableNm
            FROM @Indexes
            WHERE ID = @whilecount
            )
    SET @RecAction = (
            SELECT RecAction
            FROM @Indexes
            WHERE ID = @whilecount
            )

    IF @RecAction = 'REBUILD'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REBUILD WITH (FILLFACTOR = ' + @fillfactor + ', ONLINE = ' + @onlinestatus + ');'
    END
    ELSE IF @RecAction = 'REORGANIZE'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REORGANIZE;'
    END

    EXECUTE (@sql);

    SET @whilecount += 1
END