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_id
AS objectid,
index_id AS indexid,
partition_number
AS partitionnum,
avg_fragmentation_in_percent
AS 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_Temp
where 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
from
sys.indexes
where
index_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
