SQL Server DBA : Optimize Index

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.

  1. Rebuild Index
  2. 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.

index

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.

Audit Table Query1 Query2 Query3 Query4 Query5 Query6

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

DBA : Reindex Database in Sql server

declare @database nvarchar(100)
set @database = N’DB Name’    — Insert Database Name here

set nocount on;

create table #tables
(
seq            int identity(0,1)
,    TableName    nvarchar(1000)
,    ObjectId    int
)

create table #TableStats
(
seq            int identity(0,1)
,    TableName    nvarchar(1000)
,    ObjectId    int
,    IndexId        int
,    IndexName    nvarchar(1000)
,    avg_fragmentation_in_percent numeric(5,2)
,    re_organize    bit
,    re_index    bit
,    processed    bit
)

declare @query nvarchar(4000), @i int, @count int, @objectId int, @tablename nvarchar(1000)

insert into #tables (TableName,ObjectId)
select name,object_id from sys.tables

set @count = @@rowcount
set @i = 0

while    @i < @count
begin

select @objectId = ObjectId, @tablename = TableName
from #tables
where seq = @i

insert into #TableStats (ObjectId,TableName,IndexId,IndexName,avg_fragmentation_in_percent,re_organize,re_index,processed)
select    @objectId,@tablename,a.index_id, name, avg_fragmentation_in_percent,0,0,0
from    sys.dm_db_index_physical_stats (DB_ID(@database), OBJECT_ID(@tablename), NULL, NULL, NULL) AS a
join    sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where    avg_fragmentation_in_percent >= 5.0
and        name is not null

set @i = @i + 1
end

update #TableStats
set re_organize = 1
where avg_fragmentation_in_percent >= 5.0
and avg_fragmentation_in_percent < 30.0

update #TableStats
set re_index = 1
where avg_fragmentation_in_percent >= 30.0

select * from #TableStats
order by seq

set @i = 0
while 1 = 1
begin

if not exists (select null from #TableStats where re_organize = 1 and processed = 0)
break;

select top 1 @query = ‘alter index ‘ + IndexName + ‘ on ‘ + TableName + ‘ reorganize;’, @i = seq
from #TableStats
where re_organize = 1
and processed = 0
order by seq

print @query
—exec(@query)

update #TableStats
set processed = 1
where seq = @i
end

set @i = 0
while 1 = 1
begin

if not exists (select null from #TableStats where re_index = 1 and processed = 0)
break;

select top 1 @query = ‘alter index ‘ + IndexName + ‘ on ‘ + TableName + ‘ rebuild;’, @i = seq
from #TableStats
where re_index = 1
and processed = 0
order by seq

print @query
–exec(@query)

update #TableStats
set processed = 1
where seq = @i
end

drop table #tables
drop table #TableStats