SQL Server Database can be very slow if the database is not maintained and monitored properly. One of the maintenance plan is to optimize the indexes in the system periodically.
I have recently created a stored procedure to optimize the indexes in the database and scheduled it to run every week.
There are two ways the indexes can be optimized.
- Rebuild Index
- Reorganize Index
It is not a good idea to rebuild all the indexes in the database. After reading a lot of materials online and from my experience I would recommend rebuild indexes where fragmentation is > 30% and reorganize indexes if the fragmentation is between 10% and 30%.
There are DMVs to find missing indexes, unused indexes etc…. However I am focusing only on optimizing the indexes in this post.
Rebuilding indexes can be done in online mode or offline mode. If it is done in offline mode, then the server will lock the table from any read/writes. If it is online, then it allows the users access the table but can take little longer to rebuild the indexes as it has to create a copy of the old index so the users can continue to access the indexes.
Note: Online index operations can only be performed in Enterprise edition of SQL Server.
The above query returns all the indexes with fragmentation %, table name, schema name, row count and page count.
So you can filter the indexes you want to optimize using the above query. I wouldn’t optimize indexes that has few pages or few records. Index_Id 0 is a heap so I have excluded it from my query.
I have written two procedures for reorganize and rebuild just to avoid complexity and it can be merged into one as it shares most of the code.
Once you created the procedures and table place the procedures in a job and schedule to run weekly or monthly based on your database needs. Monitor the audit table regularly and make amendments to the schedule of this job.
Here is the code for audit table and two procedures one for index rebuild and the other for reorganize.
Here is the code to copy:
--create schema dba
--go
if object_id('dba.IndexMaintenanceAudit','U') is null
begin
create table dba.IndexMaintenanceAudit
(
Id int identity(1,1),
SchemaName varchar(200),
TableName varchar(200),
IndexName varchar(200),
IndexFillFactor tinyint,
Row_Count int,
AvgFragmentationInPercent float,
Page_Count int,
MaintenanceAction varchar(10),
StartTime datetime,
EndTime datetime
)
end
go
if object_id('dba.IndexOptimize_Rebuild','P') is not null
drop procedure dba.IndexOptimize_Rebuild
go
create procedure dba.IndexOptimize_Rebuild
(
@fragmentation_rebuild float = 30.0,
@fragmentation_reorganize float = 10.0,
@pageCount int = 5,
@rowCount int = 5000,
@tables int = 10
)
as
begin
set nocount on;
-- drop table #RebuildIndex
create table #RebuildIndex
(
Id int identity(1,1),
SchemaName varchar(200),
TableName varchar(200),
IndexName varchar(200),
IndexFillFactor tinyint,
Row_Count int,
AvgFragmentationInPercent float,
Page_Count int,
StartTime datetime,
EndTime datetime
)
insert #RebuildIndex
(
SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count
)
select top(@tables)
s.name as SchemaName,
b.Name as TableName,
c.Name as IndexName,
c.Fill_factor as IndexFillFactor,
d.[Rows] as RowsCount,
a.Avg_fragmentation_in_percent,
a.Page_count
from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) a
join sys.tables b on a.object_id = b.object_id
join sys.schemas s on b.schema_id = s.schema_id
join sys.indexes c on b.object_id = c.object_id and a.index_id = c.index_id
join sys.partitions d on b.object_id = d.object_id and a.index_id = d.index_id
where c.Index_id > 0
and a.Avg_fragmentation_in_percent > @fragmentation_rebuild
and Page_count > @pageCount
and D.[Rows] > @rowCount
order by
a.Avg_fragmentation_in_percent desc
declare @i int,
@count int,
@cmd nvarchar(4000)
set @i = 1;
select @count = max(Id) from #RebuildIndex;
while (@i <= @count)
begin
update #RebuildIndex
set StartTime = getdate()
where Id = @i;
-- Online index operations can only be performed in Enterprise edition of SQL Server.
select @cmd = 'alter index [' + IndexName + '] on [' + SchemaName + '].[' + TableName + '] rebuild with (online = off)'
from #RebuildIndex
where Id = @i;
--print @cmd
execute sp_executesql @cmd;
update #RebuildIndex
set EndTime = getdate()
where Id = @i;
insert dba.IndexMaintenanceAudit
(
SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count,
StartTime,
EndTime,
MaintenanceAction
)
select SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count,
StartTime,
EndTime,
'REBUILD'
from #RebuildIndex
where Id = @i;
set @i = @i + 1;
end
end
go
if object_id('dba.IndexOptimize_Reorganize','P') is not null
drop procedure dba.IndexOptimize_Reorganize
go
create procedure dba.IndexOptimize_Reorganize
(
@fragmentation_rebuild float = 30.0,
@fragmentation_reorganize float = 10.0,
@pageCount int = 5,
@rowCount int = 5000,
@tables int = 5
)
as
begin
set nocount on;
-- drop table #RebuildIndex
create table #RebuildIndex
(
Id int identity(1,1),
SchemaName varchar(200),
TableName varchar(200),
IndexName varchar(200),
IndexFillFactor tinyint,
Row_Count int,
AvgFragmentationInPercent float,
Page_Count int,
StartTime datetime,
EndTime datetime
)
insert #RebuildIndex
(
SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count
)
select top(@tables)
s.name as SchemaName,
b.Name as TableName,
c.Name as IndexName,
c.Fill_factor as IndexFillFactor,
d.[Rows] as RowsCount,
a.Avg_fragmentation_in_percent,
a.Page_count
from sys.dm_db_index_physical_stats(db_id(),null,null,null,null) a
join sys.tables b on a.object_id = b.object_id
join sys.schemas s on b.schema_id = s.schema_id
join sys.indexes c on b.object_id = c.object_id and a.index_id = c.index_id
join sys.partitions d on b.object_id = d.object_id and a.index_id = d.index_id
where c.Index_id > 0
and a.Avg_fragmentation_in_percent < @fragmentation_rebuild
and a.Avg_fragmentation_in_percent > @fragmentation_reorganize
and Page_count > @pageCount
and D.[Rows] > @rowCount
order by
a.Avg_fragmentation_in_percent desc;
--select * from #RebuildIndex
declare @i int,
@count int,
@cmd nvarchar(4000);
set @i = 1;
select @count = max(Id) from #RebuildIndex;
while (@i <= @count)
begin
update #RebuildIndex
set StartTime = getdate()
where Id = @i;
-- Online index operations can only be performed in Enterprise edition of SQL Server.
select @cmd = 'alter index [' + IndexName + '] on [' + SchemaName + '].[' + TableName + '] reorganize'
from #RebuildIndex
where Id = @i;
--print @cmd
execute sp_executesql @cmd;
update #RebuildIndex
set EndTime = getdate()
where Id = @i;
insert dba.IndexMaintenanceAudit
(
SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count,
StartTime,
EndTime,
MaintenanceAction
)
select SchemaName,
TableName,
IndexName,
IndexFillFactor,
Row_Count,
AvgFragmentationInPercent,
Page_Count,
StartTime,
EndTime,
'REORGANIZE'
from #RebuildIndex
where Id = @i;
set @i = @i + 1;
end
end
go
SQL Problems and Solutions 