0

I have an instance which having 25 large databases.

I have set a rebuild_all_indexes job to rebuild all indexes on every database.

Due to database large size and OLTP environment this job takes about more than 3,4 days to its completion.

Can any one help me out to handle this situation?

As job step I used some store procedures calling like this:

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
4
  • I need to know few thing from you before I suggest something. How many big tables there are in your database? Are those table partitioned?? Are you users view reports based on old data?? Are you rebuilding all indexes?? Sep 6, 2016 at 7:29
  • also please add which edition of sqlserver you are using Sep 6, 2016 at 7:52
  • Assuming you have enough tempdb space, you should consider splitting index maintenance tasks up into separate JOBS so they can run in parallel - they will definitely use more resources, but will most likely finish sooner than running synchronously - divide and conquer. Sep 6, 2016 at 9:26
  • The first question I'd ask myself is whether the indexes REALLY need to be rebuilt, or whether simply updating statistics would accomplish what you need.
    – R Evans
    Sep 8, 2016 at 21:36

2 Answers 2

4

Try using Ola Halengren's SQL Server Maintenance Solution.

It has a lot of options, most important thing - it skips indexes that doesn't need maintenance (depending how You configure it).

There are few thing about index maintenance to remember. From my experience 3-4 days for full rebuilds on bigger databases isn't that much, depending on Your storage.

In some cases rebuild isn't needed, index reorganize is often enough for fragmentation between 5% and 30% (as stated in Microsoft recommendations in Books Online, and any kind of index maintenance is recommended only for indexes bigger than 1000 pages, while difference in performance might be noticable on indexes bigger than 50000 pages (as stated here).

In most cases index defragmentation isn't source of performance problems either, it just masks other issues hidden beneath. Defragmenting them too often won't improve Your performance and will stretch Your maintenance windows.

Another thing is, that maintenance creates noticeable load on IO and CPU, rebuild/reorganize operations are written in transaction log (for databases in FULL RECOVERY mode). So Your log files will get bigger, same with log backups.

More read about index maintenance on Brent Ozar's blog posts:

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/

1

I know people will recommend Ola Halengren's scripts but I've always sidestepped them (as good of a resource as they are) simply because I want to be the architect of our maintenance scripts and creating them by myself furthered my understanding of indexes and maintenance plans in general.

So with that in mind, here is a pretty flexible and lightweight script that will rebuild (or reorganize) indexes only if necessary, as defined by the percentage fragmentation thresholds.

Using a more selective script as per the below, I have been able to reduce index maintenance times massively. We've seen reductions of 15 hours in some of old MP's designed in Management Studio on large databases.

--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
2
  • 1
    +1 for saying you believe in creating your own script. I love this idea. This is small script I created for myself.
    – Shanky
    Sep 6, 2016 at 14:37
  • I've just stolen your Compat Level 80 bit @Shanky... Seriously though I believe you live or die by your own scripting - plus its FUN!
    – Molenpad
    Sep 6, 2016 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.