1

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.

  • 4
    Given the information available, I would only implement the second index; it will probably satisfy the query that produced the first suggestion. But I typically do my performance tuning the other way, looking at the plan cache for the most expensive or the most frequently used queries and then trying to tune those. Then coming back to look at the missing indexes to see if any of them might be applicable. – Jonathan Fite Oct 26 '15 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.