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