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

SQL Server : Search Arguments (SARG)

Always try to make the queries sargable to optimize the query. If the query is not sargable the query optimizer has to scan all the rows in the table even if that column has an index.
Here are some examples of non-sargable queries and how they can be fixed.
The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:
 
SELECT ... FROM ...
WHERE Year(myDate) = 2008
 
The SQL optimizer cant use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'
 
Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())