When SQL Server has an optimal index that satisfies the search predicates of a query the optimizer performs an index SEEK operation as opposed to an index (or table) scan to retrieve the required rows; this is desirable. Based on this, one may be led to believe that having an index for every possible query predicate set would result in all the queries executing optimally. While true, one has to keep in mind that the indexes need to be maintained when the underlying table data in the column included in the index changes, which amounts to overhead for the database engine. So as you may guess, there are advantages of having indexes, but having too many can result in excessive overhead. This implies that you need to carefully evaluate the pros and cons before creating indexes.
In your first evaluation scenario you clearly want to avoid the case of having overlapping indexes as there is no additional value that an overlapping index provides. For example, consider a table ‘TabA’ and its three associated indexes created with the following definitions.
CREATE TABLE TabA
( Col1 INT, Col2 INT, Col3 INT, Col4 INT );
GO
CREATE INDEX idx1 ON TabA ( Col1, Col2, Col3 );
CREATE INDEX idx2 ON TabA ( Col1, Col2 );
CREATE INDEX idx3 ON TabA ( Col1 DESC, Col2 DESC );
GO
In the table structure above the index idx1 is a superset (overlap) of the index idx2, and therefore redundant. As can be expected any query that needs to perform a search on Col1 and Col2 could use index idx1 just as well as the index idx2 as seen in the graphical query plan below.
Figure1 Here
Such overlapping indexes are often a result of multiple developers working on the same product and not evaluating and understanding the existing schema before making additions. Once created, detecting such overlapping indexes in a database can often be a laborious task requiring detailed analysis. More importantly, most DBAs do not like to disable or drop indexes because they are not certain of the queries they were created to help with and fear the side effects the action may have.
The script below uses the new system catalog introduced in SQL Server 2005 to report all duplicate indexes in the current database context.
CREATE FUNCTION dbo.INDEX_COL_PROPERTIES (@TabName nvarchar(128), @IndexId INT, @ColId INT)
RETURNS INT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @IsDescending INT;
SELECT @IsDescending = is_descending_key
FROM sys.index_columns SYSIDXCOLS
WHERE OBJECT_ID(@TabName) = SYSIDXCOLS.object_id
AND @IndexId = SYSIDXCOLS.index_id
AND @ColId = SYSIDXCOLS.key_ordinal;
-- Return the value of @IsDescending as the property
RETURN(@IsDescending);
END;
GO
-- Find Duplicate Indexes in SQL Server Database
CREATE VIEW IndexList_VW AS
SELECT
SYSOBJ.[name] AS TableName,
SYSIDX.[name] AS IndexName,
SYSIDX.[is_unique] AS IndexIsUnique,
SYSIDX.[type_desc] AS IndexType,
SYSIDX.[is_disabled] AS IsDisabled,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 1 ) AS Column1,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 2 ) AS Column2,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 3 ) AS Column3,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 4 ) AS Column4,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 5 ) AS Column5,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 6 ) AS Column6,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 7 ) AS Column7,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 8 ) AS Column8,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 9 ) AS Column9,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 10 ) AS Column10,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 11 ) AS Column11,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 12 ) AS Column12,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 13 ) AS Column13,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 14 ) AS Column14,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 15 ) AS Column15,
INDEX_COL( SYSOBJ.[name], SYSIDX.index_id, 16 ) AS Column16,
dbo.INDEX_COL_PROPERTIES( SYSOBJ.[name], SYSIDX.index_id, 1 ) AS Column1_Prop,
dbo.INDEX_COL_PROPERTIES