Multiple indices on a table I have run SQL Profiler and I can see that I'm "missing" some indexes (non-clustered) on a table. How is it best to handle indexes that can be a "subset" of another index?

Index 1

UserId, InstanceId, Created


Index 2 (Subset of Index 1)

InstanceId, Created


Is index 2 necessary in this case?

Can't really found the query that caused Index 2

Query (using Entity framework):

SELECT 1 AS [C1], [Limit1].[InputForm] AS [InputForm], [Project2].[UserId] AS [UserId]
FROM (SELECT [Distinct1].[UserId] AS [UserId]
    FROM (SELECT DISTINCT 
        [Extent1].[UserId] AS [UserId]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent1]
        WHERE ([Extent1].[InstanceId] = @p__linq__0) AND ([Extent1].[Created] &gt;= @p__linq__1)
    )  AS [Distinct1] ) AS [Project2]
OUTER APPLY  (SELECT TOP (1) [Project3].[InputForm] AS [InputForm]
    FROM ( SELECT 
        [Extent2].[InputForm] AS [InputForm], 
        [Extent2].[Created] AS [Created]
        FROM [dbo].[Dat_GameCompletionData] AS [Extent2]
        WHERE ([Extent2].[InstanceId] = @p__linq__0) AND ([Extent2].[Created] &gt;= @p__linq__1) AND ([Project2].[UserId] = [Extent2].[UserId])
    )  AS [Project3]
    ORDER BY [Project3].[Created] DESC ) AS [Limit1]
I wouldn't say that index 2 is a subset of index 1. 

An index that had the columns UserId, InstanceId would be. 

This could still be useful to queries that do not require the created column as they would be accessing a more narrow index which would require less IO and memory.

Your best bet is to check if your indexes are actually being used by queries. The scripts below will help with this.

SELECT  d.name as [Database]
        , OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME]          
        , I.[NAME] AS [INDEX NAME]         
        , USER_SEEKS          
        , USER_SCANS        
        , USER_LOOKUPS          
        , USER_UPDATES 

FROM    SYS.DM_DB_INDEX_USAGE_STATS AS S          
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]               
                            AND I.INDEX_ID = S.INDEX_ID 
JOIN    sys.Databases d on s.database_id = d.database_id

WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

ORDER BY USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES DESC


SELECT  d.name
        , t.name
        , OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME]
        , I.[NAME] AS [INDEX NAME]
        , A.LEAF_INSERT_COUNT        
        , A.LEAF_UPDATE_COUNT
        , A.LEAF_DELETE_COUNT

FROM    SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A        
JOIN    SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID]   
JOIN    sys.tables t ON i.object_id = t.object_id      
JOIN    sys.databases d ON a.database_id = d.database_id
                        AND I.INDEX_ID = A.INDEX_ID 

WHERE   OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

ORDER BY    A.LEAF_INSERT_COUNT + A.LEAF_UPDATE_COUNT + A.LEAF_DELETE_COUNT DESC