If you are reading this article, I assume
that you have basic knowledge of Index, its types and usage.
Over the years of development and chasing
high performance we build and update many indexes. Due to shear number or
complexity for forget to keep track of number, quantity and utilization of the indexes.
Let’s get back to present; now our application
is working fine and our Project Manager comes and says “Guys it time for
cleanup”. He means find out the unused and duplicate index and remove them. Why
, because good indexing provides good performance and poor implementation of
indexing results in poor performance. Pretty straight forward.
As I mentioned above for index utilization we
have a separate article (Index Utilization Report).
So let’s focus on duplicate index. First
question is how we will decide two indexes are identical or duplicate.
Here I am using a very simple concept, if
two indexes are using the same columns (Table.Column) irrespective of their order
and index type (Clustered or Non Clustered) are considered duplicate.
For testing purpose I have created three
duplicate indexes, marked in Yellow, Orange and Green.
Case Yellow – Here both indexes are of
same type non clustered and same column only their names are different, so
basically both of them are same index hence duplicate.
Case Orange – Both indexes are using the
same column but they are different kind (one is clustered and other is non
clustered) and different name. So as per our logic both index are using same columns
so they are duplicate.
Case Green – Here we have same set of
columns but in different order and index types are different. So as per our logic both indexes are duplicate,
even though columns order and index types are same.
Here is the script to gather the
information for analysis, you may have different criteria to decide the duplicity.
So feel free to modify the script or use the power of MS excels. If you need my
help drop a comment and I will try my best to help you.
Begin
Create Table #AllIndex
(
TableName varchar(256),
TableType varchar(256),
IndexName varchar(256),
IndexType varchar(256),
IndexId int
)
insert into #AllIndex
select Distinct
O.name As 'TableName',
O.type_desc As 'TableType',
I.name As 'IndexName',
I.type_desc As 'IndexType',
I.index_id As 'IndexId'
FROM sys.indexes I
INNER JOIN sys.objects O ON I.[object_id] = O.[object_id]
INNER JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE index_id > 0 and (O.type_desc='USER_TABLE' or O.type_desc='VIEW')
Select T.TableType,T.IndexId,T.TableName,T.IndexName,
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 1) as 'ColumnName1',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 2) as 'ColumnName2',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 3) as 'ColumnName3',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 4) as 'ColumnName4',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 5) as 'ColumnName5',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 6) as 'ColumnName6',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 7) as 'ColumnName7',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 8) as 'ColumnName8',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 9) as 'ColumnName9',
INDEX_COL('dbo' +
'.' +T.TableName, T.IndexId, 10) as 'ColumnName10'
/* If you have more than 10 columns in
your indexes then add this
INDEX_COL('dbo' + '.' +T.TableName,
T.IndexId, @Count) as 'ColumnName@Count',
and replcase the @Count with next
number. */
From #AllIndex T
Order By T.TableName,T.IndexName,ColumnName1,ColumnName2,ColumnName3,ColumnName4,ColumnName5
Drop Table #AllIndex
End
Once you are ready with the list of duplicate index, you are
ready to delete them. Here is the syntax to delete an index –
USE [YourDatabaseName]
GO
ALTER TABLE [dbo].[YourTableName] DROP
CONSTRAINT [IndexName]
GO
Please note before deleting, do not delete Primary Key and
clustered index. First delete non clustered index because non clustered index
are build on clustered index. So do some research if you are not sure about the
reason before taking any action.
Thanks & Cheers
Please provide your feedback for continuous improvement.
