MS-SQL Server - Indexing 'Kakee' - A Performance Script

Index Rebuild

A different approach can sometime teach us to another level.

Here, instead of automating USE Database, I have created a consolidated table for all indexes from all the databases in one Instance and rebuilding them all one by one.

Please configure your Fragmentation Percent and Page Count as per your requirement.

------------ Author 'Amit Vaid' Dated - 16-11-2022
BEGIN
    CREATE TABLE #12_1981_indexmaintenance
      (
         currentdatabase NVARCHAR(50),
         tablename       NVARCHAR(150),
         indexname       NVARCHAR(150),
         indextype       NVARCHAR(100),
         percentfragment DECIMAL(11, 2)
      );

    INSERT INTO #12_1981_indexmaintenance
                (currentdatabase,
                 tablename,
                 indexname,
                 indextype,
                 percentfragment)
    EXEC Sp_msforeachdb
      'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? SELECT      DB_NAME() AS [CurrentDatabase], OBJECT_NAME(ind.OBJECT_ID) AS TableName,  ind.name AS IndexName,  indexstats.index_type_desc AS IndexType,  indexstats.avg_fragmentation_in_percent FROM  sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats  JOIN  sys.indexes ind  ON ind.object_id = indexstats.object_id          AND ind.index_id = indexstats.index_id  JOIN sys.tables t ON ind.object_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE indexstats.avg_fragmentation_in_percent > 10 And ind.Name IS NOT NULL and indexstats.page_count > 1000 ORDER BY  indexstats.avg_fragmentation_in_percent DESC end';
END

DECLARE @fragPercentThresholdREORG   DECIMAL(11, 2),
        @fragPercentThresholdREBUILD DECIMAL(11, 2),
        @schemaName                  NVARCHAR(128),
        @commandrbld                 VARCHAR(1000),
        @commandreorg                VARCHAR(1000),
        @tableName                   NVARCHAR(500),
        @indexName                   NVARCHAR(500),
        @indexType                   NVARCHAR(55),
        @percentFragment             DECIMAL(11, 2),
        @database                    NVARCHAR(50);

-- Determine maximum fragmentation threshold and the schema to operate against
SET @fragPercentThresholdREORG = 30;
SET @fragPercentThresholdREBUILD = 30;
SET @schemaName = N'dbo';

DECLARE fragmentedtablelist CURSOR FOR
  SELECT [currentdatabase],
         tablename,
         indexname,
         indextype,
         percentfragment
  FROM   #12_1981_indexmaintenance

OPEN fragmentedtablelist;

FETCH next FROM fragmentedtablelist INTO @database, @tableName, @indexName,
@indexType, @percentFragment;

WHILE @@FETCH_STATUS = 0
  BEGIN
      PRINT 'Processing [' + @indexName + '] on database '
            + @database + ' for table ' + @tableName
            + ' which is '
            + Cast(@percentFragment AS NVARCHAR(50))
            + ' fragmented';

      IF ( @percentFragment >= @fragPercentThresholdREBUILD )
        BEGIN
            EXEC( 'ALTER INDEX [' + @indexName + '] ON [' + @database +
            '].[dbo].[' +
            @tableName + '] REBUILD; ')

            PRINT 'Finished reorganizing [' + @indexName
                  + '] on database [' + @database + '] for table '
                  + @tableName;
        END

      IF ( @percentFragment <= @fragPercentThresholdREORG )
        BEGIN
            EXEC( 'ALTER INDEX [' + @indexName + '] ON [' + @database +
            '].[dbo].[' +
            @tableName + '] REORGANIZE;')

            PRINT 'Finished rebuilding [' + @indexName
                  + '] on database [' + @database + '] for table '
                  + @tableName;
        END

      FETCH next FROM fragmentedtablelist INTO @database, @tableName, @indexName
      ,
      @indexType, @percentFragment;
  END

CLOSE fragmentedtablelist;

DEALLOCATE fragmentedtablelist;

DROP TABLE #12_1981_indexmaintenance 

--------------------------------------------

Thanks with Regards

T. Amit Vaid S.