6

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.

  • 2
    Please add the table and index schema to the question. I suspect that if a table is getting that fragmented that you have a clustered index on the primary key which is a unique identifier. I hope I am wrong. – datagod Oct 4 '16 at 13:34
  • Can you customize this to run across all databases. – vicky Jun 15 '18 at 18:23
6

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 
  • 1
    I wouldn't set the threshold of 10% 30% in stone. It may make sense to rebuild indexes that > 50% fragmented. You have to balance the work of rebuilding to benefit that work will give your queries. – James Anderson Sep 30 '16 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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