1

On production servers we are running below query as part of SQL Server Agent Job to rebuild / reorganizes indexes regularly. Almost all servers have more than 100 DBs as volunteer for this script.
Problem is, Job always says I run successfully but when I check major tables manually they usually contain more than 40 percent of fragmentation.

Is something wrong with our script or there are other parameters we are missing?

USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO

    DECLARE @DBName NVARCHAR(255),
        @TableName NVARCHAR(255),
        @SchemaName NVARCHAR(255),
        @IndexName NVARCHAR(255),
        @PctFragmentation DECIMAL,
        @DBID VARCHAR(30),
        @physical_statcmode VARCHAR(20)


SET @physical_statcmode= CASE DATEPART(WEEKDAY,GETDATE()) WHEN 7 THEN 'DETAILED' ELSE 'SAMPLED' END
DECLARE @Defrag NVARCHAR(MAX)


    IF EXISTS ( SELECT  *
                FROM    sys.objects
                WHERE   OBJECT_ID = OBJECT_ID(N'#Frag') ) 
        DROP TABLE #Frag


    CREATE TABLE #Frag
        (
          DBName NVARCHAR(255),
          TableName NVARCHAR(255),
          SchemaName NVARCHAR(255),
          IndexName NVARCHAR(255),
          AvgFragment DECIMAL
        )

DECLARE DatabaseList CURSOR STATIC READ_ONLY
        FOR 
        SELECT  name
            FROM   sys.databases
            WHERE (name LIKE  '%_ProductionDB' )
            AND state_desc = 'ONLINE'
            AND is_read_only = 0
           ORDER BY name

OPEN DatabaseList
    FETCH NEXT FROM DatabaseList INTO @DBName
    WHILE @@FETCH_STATUS = 0
        BEGIN

    SELECT @DBID =DB_ID(@DBName)    





    EXEC ('INSERT INTO #Frag(
            DBName,
            TableName,
            SchemaName,
            IndexName,
            AvgFragment
            )
            SELECT '''+@DBName+''' AS DBName
            ,t.Name AS TableName
            ,sc.Name AS SchemaName
            ,i.name AS IndexName
            ,s.avg_fragmentation_in_percent
            FROM ['+@DBName+'].sys.dm_db_index_physical_stats('+@DBID+',NULL,NULL,NULL,'''+@physical_statcmode+''') AS s
            JOIN ['+@DBName+'].sys.indexes i
            ON s.Object_Id = i.Object_Id
            AND s.Index_id = i.Index_id
            JOIN ['+@DBName+'].sys.tables t
            ON i.Object_Id = t.Object_Id
            JOIN ['+@DBName+'].sys.schemas sc
            ON t.schema_id = sc.Schema_Id
            WHERE s.page_count > 8
            AND s.avg_fragmentation_in_percent > 15
            AND t.TYPE = ''U''           
            ORDER BY TableName,IndexName')

    DECLARE cList CURSOR STATIC READ_ONLY
        FOR 
        SELECT  *
            FROM    #Frag

    OPEN cList
    FETCH NEXT FROM cList INTO @DBName, @TableName, @SchemaName, @IndexName,
        @PctFragmentation
    WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @PctFragmentation > 15.0 AND @PctFragmentation < 30.0 
                BEGIN
                    SET @Defrag = N'ALTER INDEX [' + @IndexName + '] ON ['
                        + @DBName + '].[' + @SchemaName + '].[' + @TableName
                        + '] REORGANIZE'
                    EXEC sp_executesql @Defrag
                    PRINT 'Reorganize index: ' + @DBName + '.' + @SchemaName
                        + '.' + @TableName + '.' + @IndexName
                END
            ELSE
                BEGIN 
                IF @PctFragmentation > 30.0 
                    BEGIN
                        SET @DeFrag = N'ALTER INDEX [' + @IndexName + '] ON ['
                            + @DBName + '].[' + @SchemaName + '].[' + @TableName
                            + '] REBUILD'
                        EXEC sp_executesql @Defrag
                        PRINT 'Rebuild index: ' + @DBName + '.' + @SchemaName
                            + '.' + @TableName + '.' + @IndexName
                    END
                ELSE
                    print 'noneed'
                END

            FETCH NEXT FROM cList INTO @DBName, @TableName, @SchemaName,
                @IndexName, @PctFragmentation

        END
    CLOSE cList
    DEALLOCATE cList

            PRINT @DBName 
            TRUNCATE TABLE #Frag

            FETCH NEXT FROM DatabaseList INTO @DBName
        END
    CLOSE DatabaseList
    DEALLOCATE DatabaseList

    DROP TABLE #Frag
  • 2
    The script is not accurate because it does not includes column page_count from sys.dm_db_index_physical_stats. If page_count <1500 you just don't need to rebuild/reorganize/update stats for that index. Even if you do the operations the fragmentation level would remain same or might sometime increase. This is my custom script which deals with issue I pointed or you can use Ola's script – Shanky Apr 7 '16 at 12:46
  • 1
    Page_count is total number of index or data pages while PctFragmentation is your logical fragmentation percentage. I hope this is clear. As a accepted rule you rebuild index when PctFragmentation >30 and reorganize when PctFragmentation is between 10 and 30. – Shanky Apr 7 '16 at 13:01
  • 1
    I'd suggest using this script instead ola.hallengren.com/… – gbn Apr 7 '16 at 13:22
  • 1
    Just a recommendation...go get this book: Expert Performance Indexing for SQL Server 2012. It is a good book on understanding indexes and the maintenance behind them. – Shawn Melton Apr 7 '16 at 13:23
  • 1
    Did you read my first comment, I pointed out that exactly – Shanky Apr 7 '16 at 13:28
1

Your script is checking for indexes with 8 or more pages (64kb). This is far too low. Small tables can easily have a high percentage of fragmentation. Also fragmentation wont have much impact on scanning a table that small. It's is most likely these small tables that you are seeing with 40% fragmentation after the script has ran.

The script is doing a REORGANIZE on indexes with fragmentation between 15% and 30%. Anything over 30% receives the REBUILD command. This is the Microsoft stated best practice but IMHO it is too aggressive. I tend to have something like 30% and 50% but it's really down to your system and requirements.

I would highly recommend Ola Hallengren's Maintenance Solution as a replacement for this script as it covers much more.

  • My major problem is why it never run for all DBs? even though DBs contain major heavy tables. – AA.SC Apr 7 '16 at 13:12
  • 1
    Do the DBs that are missed have _ProductionDB at the end of their name? – James Anderson Apr 7 '16 at 13:16
  • 3
    That's why. Your script only insert Dbs into the cursor that have _productionDb at the end of their name. If you want to include the other Dbs then remove the LIKE expression in the WHERE clause – James Anderson Apr 7 '16 at 13:26
  • James I verified that DBs are not missing in cursor selection. – AA.SC Apr 8 '16 at 5:57
  • Can you add the names of the missing DBs, with a brief description, to your question please? – James Anderson Apr 8 '16 at 6:22
1

Are you thinking about internal fragmentation? Or external? Internal fragmentation might stay at higher level, mostly because of structure of your data - rows too big to fit more than one into one page. So even if your indexes might get reorganized fragmentation might stay. Don't fret too much about it, statistics are more important anyway. https://www.brentozar.com/archive/2016/01/should-i-worry-about-index-fragmentation/

Also, as James Anderson suggested - don't reinvent the wheel, use trusted solution like Ola Hallengren's one.

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.