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

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

Answer 2

Every time a column is included in an index, space is used - even if you have other similar indexes.

Answer 3

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 4

I'm not clear on what you're asking.

Consider an index on a table with columns A,B, C, & D. You have an indexes on (A,B) and (B,A), all sorted ascending. In that case, yes, that will build two indexes, but the additional index is not wasted because the additional columns in an index only help from a searching perspective when there are duplicate rows for all the preceding columns in the index.

On the other hand an index on (A,B) with the additional "covering column" C and another index (A,B) with the additional "covering" column D will waste space. You should just use (A,B) + C,D.

Answer 5

Number of indexes to use is a choice between performance selects vs insert/update/delete. Disk space is less significant.

I met many times indexes for table t(A, B, C) like 'A', 'A,B', 'A,B,C'. Sure it is not useful. Some tools like to generate such indexes. Also there is not good idea to create keys for ALL possible queries.