Rebuild of indexes is the prime job of a DBA as heavily fragmented indexes can degrade query performance and cause your application to respond slowly. We should reorganize indexed with fragmentation between > 5% and < 30% and rebuild indexes if fragmentation more than 30%. To rebuild indexes we have a Rebuild indexes task in maintenance Plan.
If we see the T-SQL of this task, it will rebuild all indexes. As rebuilding all indexes would be nightmare for the server because as it take much resources. This is drawback of Rebuild Index Task.
To overcome this we write a custom script that only select indexes to rebuild that have Fragmentation more than 30%.
Sys.dm_db_index_physical_stats database management function return handy information about index fragmentation. We will use this function in script below. This script use table variable and does not require any cursor or temp table.
declare @frag_Temp as Table
[partitionnum][int] NULL,
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand as Nvarchar(3000)
partition_numberAS partitionnum,
avg_fragmentation_in_percentAS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
--Arguments 1(Database_ID,object_Id,Index_ID,partition,mode
WHERE avg_fragmentation_in_percent >=30.0 AND index_id> 0;
select @Count=Count(*) from @frag_Temp --Get Total Count
select @objectid=objectid,@indexid=indexid,@partitionnum=partitionnum
from @frag_Tempwhere ID=@i
--Get tableName and its schema
select @objectname=o.name,@schemaname=c.name from
inner join sys.schemas c on o.schema_ID=c.schema_ID
where o.object_id=@objectid
--Get Index Name
whereindex_id=@indexid and object_id=@objectid
--Get Partition Count
select @partitioncount=count(*) from sys.partitions
where object_id=@objectid and index_id=@indexid
SELECT @SQLCommand= 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD'
SELECT @SQLCommand=@SQLCommand + ' PARTITION=' + convert(Char,@partitionnum);
--Increment Count