Optimization of SQL Server Indexes

Question

I have a table with several non PK-related indexes. Unfortunately there's some duplicity in that several indexes reference the same column with the same sort order. I typically create covering indexes which represent aggregations of non PK-related indexes for my tables because as long as the column is indexed it will use it if appropriate during queries. My question is simple: does indexing the same column in several indexes in the same sort order waste resources or does SQL Server know that a column is already indexed and just cross reference for optimization purposes?

UPDATE: A future point would be to ask if having duplicate indexes with slight variations improves ORDER BY activities. For example, if I order by A, B DESC, D would a special index with that order actually increase performance over a single covering index that include these columns with the same sort order. I was under the impression that ORDER BY would just rely on indexes and didn't need special indexes to be present for performance reasons.

Answer 1

Each index will be separate - there's no cross referencing and so forth. So yes, if those indices are duplicates, you might end up wasting some time. But: it can totally make sense to include a single column in multiple indices - things like compound indices (several fields) and so on might make sense to be present side by side.

SQL Server as of 2005 has a really nice feature called the DMV (Dynamic Management View) which allow you to check for

• indices not being used at all
• missing indices that might speed up your query load

Find missing indices:

SELECT  
    object_name(object_id), d.*, s.*
FROM
    sys.dm_db_missing_index_details d 
INNER JOIN 
    sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN 
    sys.dm_db_missing_index_group_stats s ON    g.index_group_handle = s.group_handle
WHERE   
    database_id = db_id()
ORDER BY  
    object_id

Find unused indices:

DECLARE  @dbid INT

SELECT @dbid = DB_ID(DB_NAME())

SELECT   
    OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
    INDEXNAME = I.NAME,
    I.INDEX_ID
FROM     
    SYS.INDEXES I
JOIN 
    SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE    
    OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1
    AND I.INDEX_ID NOT IN (SELECT S.INDEX_ID
                            FROM SYS.DM_DB_INDEX_USAGE_STATS S
                            WHERE S.OBJECT_ID = I.OBJECT_ID
                                    AND I.INDEX_ID = S.INDEX_ID
                                    AND DATABASE_ID = @dbid)
ORDER BY 
    OBJECTNAME, I.INDEX_ID, INDEXNAME ASC

Answer 2

It will use up the space twice, but more importantly it will slow down inserts while it updates all the indexes.