Index comparison

Question

On my Production server (SQL2008) I have a query that returns suggestions of indexes.

SELECT * FROM sys.dm_db_missing_index_groups g  
INNER JOIN sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle  
INNER JOIN sys.dm_db_missing_index_details d on g.index_handle = d.index_handle  

Now, the most recent suggestions on it is this:

CREATE NONCLUSTERED INDEX IX_ACTIF_INC_NUMBER_ROUTE ON [dbname].[dbo].[CUSTOMER] ([ACTIF]) INCLUDE (NUMBER,ROUTE) WITH (ONLINE = ON)
CREATE NONCLUSTERED INDEX IX_ACTIF_ROUTE_INC_NUMBER ON [dbname].[dbo].[CUSTOMER] ([ACTIF], [ROUTE]) INCLUDE (NUMBER) WITH (ONLINE = ON)

Considering I don't know the queries in question, should I create the 2 Indexes, or only the second one as SQL would implicitly use it to cover the first one?

They both have similar numbers in USER_SEEKS (67k), USER_COST (0.95) and USER_IMPACT(64).

Any good and easy-to-understand documentation on how multi-column indexes should be optimized/implemented?

Thanks.

Answer 1

superset/subset

The second index is likely a better choice, since having additional key columns available allows for better b-tree navigation for any queries with predicates on both [ACTIF] and [ROUTE].

However, you should take all missing index requests with a grain of salt.

They are often quite useless.

• Don't show up when you'd help
• Show up when you wouldn't help

All sorts of things stop them from appearing:

• Eager Index Spools
• Trivial Plans
• Non-SARGable predicates
• Mediocre existing indexes
• Columns you select (sometimes)
• Predicates you use (sometimes)

I used to love missing index requests, just like I used to love the plan cache.

But I was misled too many times by them, and the right set of indexes wasn't obvious until I actually started looking at queries to tune and what would help them.

What you should pay attention to:

• Slow queries - wall clock time
• That users run (with some exceptions)
• Get actual execution plans
• Use operator times to find the slow parts
• Fix those issues

Don't fall for imaginary metrics:

• Logical reads
• Missing index impact
• Query/Operator costs