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.
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
(
ID int identity(1,1),
[objectid][int] NULL,
[indexid][int] NULL,
[partitionnum][int] NULL,
[frag] [float] NULL
)
Declare @Count int
Declare @i tinyint=1
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand as Nvarchar(3000)
insert into @frag_Temp
SELECT
object_idAS objectid,
index_id AS indexid,
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
While(@i<=@Count)
Begin
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
sys.objects o
inner join sys.schemas c on o.schema_ID=c.schema_ID
where o.object_id=@objectid
--Get Index Name
select@indexname=name
fromsys.indexes
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'
IF(@partitioncount>1)
SELECT @SQLCommand=@SQLCommand + ' PARTITION=' + convert(Char,@partitionnum);
EXEC(@SQLCommand);
--Increment Count
set @i=@i+1
End
