Sql-server – Database maintenance rebuilding indexes daily

index-maintenancesql serversql-server-2016

My database is around 2.5TB. Is it a good practice to run rebuild indexes daily?

We delete data daily around 20 GB & at the same time we delete partitions & we will add new partition. To run rebuild index is taking 8 hours, sometimes 24 hours. During this, the log file is increasing to 300 GB.

We need to send daily reports for database fragmentation to customer. Seeing 99 or 98% they are scared. Please suggest.

Tables size is 70GB & for this table fragmentation level daily it will be 99%. No idea what made customer request daily reports of fragmentation level, but he just need it daily.

Best Answer

Firstly you should take into account the page_count of the index. If the page_count is less than 1000 (or whatever you decide) then you should ignore the index.

We have a maintenance script which analyses the page_count and fragmentation of the indexes and follows the following guideline:-

  • less than 10% logical fragmentation, don’t do anything
  • between 10% and 30% logical fragmentation, reorganize it (using ALTER INDEX … REORGANIZE)
  • more than 30% logical fragmentation, rebuild it (using ALTER INDEX … REBUILD)(ONLINE= ON for Enterprise Editions)

This is a starter to find out where you are at present:-

SELECT dbtables.[name] AS 'Table'
    ,dbindexes.[name] AS 'Index'
    ,indexstats.avg_fragmentation_in_percent
    ,CASE 
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,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
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

It all depends on your requirements. How is performance effected when you only rebuild once a week? Could you send the report once a week straight after a rebuild? I would base line the performance in a test environment (if possible), and if it doesn't degrade too much, only perform the index maintenance once a week.

An agent job could be something like:-

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 < 10 --change all levels to your requirement
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END
    ,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
    AND dbindexes.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC

DELETE FROM @Indexes WHERE RecAction = 'NOTHING'

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 = 98, ONLINE = ON);' --change depending on SQL version
    END
    ELSE IF @RecAction = 'REORGANIZE'
    BEGIN
        SET @sql = 'ALTER INDEX [' + @IndexNm + '] ON [' + @TableNm + '] REORGANIZE;'
    END

    EXECUTE (@sql);

    SET @whilecount += 1
END
Related Question