What are redundant indexes in SQL Server?
Identifying redundant indexes is a very important task for a DBA. I personally do not find any reason of having redundant indexes on databases. Otherwise, it is an over head to the system like maintaining the index/storage space etc.
The challenge is to identify a redundant/duplicate index in SQL Server? If we do not understand the meaning of redundant/duplicate, then we might end up with removing important indexes and it can lead to
performance degradation.
I would like to list out few considerations to categorize indexes as redundant; if:
1. An index has Same key columns in the same order with another index
2. An index has Key columns those are left based subset of another index
3. Meeting all of the above, ONLY for similar index types.(Clustered and no clustered are not to be considered as duplicate indexes)
4. Meeting all of the above, And the Key Columns specified with same ordering (ASC/DESC)
The below query does not identify the duplicate index, but it gives you enough information with which we can easily identify the duplicate indexes in your system.
Note:(String_AGG is new function in SQL Server 2017)
Select Object_Schema_name(ix.object_id) Schema_Name,
Object_name(ix.object_id) Object_Name,ix.name,ix.type_desc ,
string_agg(Cast(c.name as nvarchar(MAX)) + ' (' + case when is_descending_key = 0 then 'ASC' Else 'DESC' END + ') ',',')
within group(Order by ixc.key_ordinal asc) As KeyCols
From
sys.indexes ix
inner join sys.index_columns ixc on ix.index_id = ixc.index_id and ix.object_id = ixc.object_id
inner join sys.columns c on ixc.object_id = c.object_id and c.column_id = ixc.column_id
Where objectpropertyex(ix.object_id,'IsMSShipped') =0
And ixc.is_included_column=0 and ix.index_id <> 1
Group by ix.object_id,ix.name,ix.type_desc
Order by 1 asc,2 asc
Like this:
Like Loading...
Related
What are redundant indexes in SQL Server?
Identifying redundant indexes is a very important task for a DBA. I personally do not find any reason of having redundant indexes on databases. Otherwise, it is an over head to the system like maintaining the index/storage space etc.
The challenge is to identify a redundant/duplicate index in SQL Server? If we do not understand the meaning of redundant/duplicate, then we might end up with removing important indexes and it can lead to
performance degradation.
I would like to list out few considerations to categorize indexes as redundant; if:
1. An index has Same key columns in the same order with another index
2. An index has Key columns those are left based subset of another index
3. Meeting all of the above, ONLY for similar index types.(Clustered and no clustered are not to be considered as duplicate indexes)
4. Meeting all of the above, And the Key Columns specified with same ordering (ASC/DESC)
The below query does not identify the duplicate index, but it gives you enough information with which we can easily identify the duplicate indexes in your system.
Note:(String_AGG is new function in SQL Server 2017)
SQLZealots 
Hi, excelent SQL, I added a count over to determ the duplicate indexes:
select * from (
select count (b.name) over (partition by b.keycols,b.object_name ) sortorder
,b.*
from
(
Select Object_Schema_name(ix.object_id) Schema_Name,
Object_name(ix.object_id) Object_Name,ix.name,ix.type_desc ,
string_agg(Cast(c.name as nvarchar(MAX)) + ‘ (‘ + case when is_descending_key = 0 then ‘ASC’ Else ‘DESC’ END + ‘) ‘,’,’)
within group(Order by ixc.key_ordinal asc) As KeyCols
From
sys.indexes ix
inner join sys.index_columns ixc on ix.index_id = ixc.index_id and ix.object_id = ixc.object_id
inner join sys.columns c on ixc.object_id = c.object_id and c.column_id = ixc.column_id
Where objectpropertyex(ix.object_id,’IsMSShipped’) =0
And ixc.is_included_column=0 and ix.index_id 1
Group by ix.object_id,ix.name,ix.type_desc
) b
) b
where b.sortorder > 1
Order by b.schema_name asc,b.object_name asc
LikeLike