sp_BlitzCache and missing index but index columns are already there

I have this recommendation as a missing index on a table for a stored procedure that is ran from the script tool sp_BlitzCache. However I have an index on the columns TeamId, PlayerId, and Active already. Shouldn’t this be used instead and not duplicate indexes with the same columns?

The query must be this inline select

SELECT 
        (SELECT COUNT(*) FROM Schema.TeamPlayer WHERE TeamId = dt.Id AND Active = 1) AS PlayerCount

Current Index

CREATE NONCLUSTERED INDEX ix_TeamPlayerActive
 ON [Schema].[TeamPlayer] ([TeamId], [PlayerId], [Active]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);
GO

Missing Index Message

The Query Processor estimates that implementing the following index could improve query cost (34.7976)
by 12.5436% for 12196 executions of the query over the last 6 hours


CREATE NONCLUSTERED INDEX ix_Active_Includes
 ON [Schema].[TeamPlayer] ([Active])
INCLUDE ([TeamId]) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);
GO

Answer

At first glance it seems, that you want to look up TeamId and Active. Index on (TeamId, PlayerId, Active) is not exact match. You can seek the TeamId but then you have to scan through all PlayerIDs in that team. Both active and inactive, which is a bit wastefull. How much wastefull depends on the ratio of active records. For exact seek you would need index on (TeamId, Active, ...) or (Active, TeamId, ...).

Given the suggested index the situation is probably a bit different. The server seems to look for all active records, you are probably looking for all teams in one query. In that case the best index is (Active, TeamId) or, as suggested, (Active) with TeamId included. All active records are stored consecutively here. (Active, TeamId) is more versatile though, as it is already pre-grouped by TeamId. Index on (TeamId, Active) is in principle usable, but you would have to seek for all teams individually, which is suboptimal and chances are, the server would revert to scan anyway, so you would end up where you already are.

Index on (Active, TeamId) is clearly best here. I would make it filtered on only active records if you do not need to look for inactive records frequently.