0

[dbo].[sp_BlitzIndex] is showing a Indexaphobia: High Value Missing Index for this table but the table has a non clustered index on both columns. Is this still needed?

INEQUALITY:  [TeamId]  {int} INCLUDE:  [PlayerId]  {int}
TT.
  • 15,774
  • 6
  • 47
  • 88
Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • 1
    "needed" and "useful" are not the same things. Likewise, everything has a cost. So is there a (significant or any) benefit and does that benefit outweigh the cost by adding the index? Only you can evaluate that and decide. You can't just evaluate the queries that benefit from this index; you need to consider the costs of maintaining it as well. If the benefit is for a rarely used query that is not vital to your system, then it might not be "needed" even if "useful". – SMor Apr 23 '21 at 11:28

1 Answers1

1

This is a suggestion based on the info provided - but to confirm

  • You have one index on TeamID
  • You have another index on PlayerId

If this is the case, I think the suggested index wants both fields in the one index.

If it's in two indexes, the query would need to do a lot of reads to 'join' both indexes. Instead, if it's in one index (e.g., index on TeamID, including PlayerID) then it won't need to go through that matching process.

I suggest modifying your index on TeamID to also include PlayerID. It's only an int so it won't add much to the size.

It could also be useful trying to find the query that looks at those fields so you can test before-and-after (potentially a query joining Teams with Players).

Edit: To understand how this works, the developer of sp_Blitz (Brent Ozar) has a fantastic video called How to think like the SQL Server Engine. I highly recommend it - it took my understanding of indexes to a much higher level.

Edit2: I suggest trying having it not just as an 'include' but as the second field in the index - so it would be sorted by PlayerID as well. Also note that your question, on re-reading, suggests the query that could use the index is likely one where you are looking for players not in a given team.

seanb
  • 6,272
  • 2
  • 4
  • 22