1

I am running sp_BlitzIndex from Brent Ozar and get a number of these items.

Multiple Index Personalities: Borderline duplicate keys

I'm not 100% on what to do but here is an example below.

CREATE INDEX [IX_Test] ON [Test] ( [SportId] ) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

CREATE UNIQUE INDEX [IX_Test_2] ON [Test] ( [SportId], [AnotherId] ) WITH (FILLFACTOR=100, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

As you can see they seem similar. My question is does it hurt to remove the first index SportID but keep the dual index (SportId, TextId).

What is my best approach here?

Mike Flynn
  • 22,342
  • 54
  • 182
  • 341
  • 1
    Keeping the second index makes sense, the first might be chosen by some queries as it's slightly narrower, but the addition of presumably a single int column shouldn't matter. Check `sys.dm_db_index_usage_stats` to see how each are currently utilised. – Stu Apr 19 '21 at 15:51
  • 3
    I would suggest removing the first index. It is redundant with the second. – Gordon Linoff Apr 19 '21 at 16:27
  • Can you put an answer and state why its redundant? – Mike Flynn Apr 22 '21 at 13:56

1 Answers1

3

The first index, IX_Test, is redundant since its first, most selective column, is replicated by index IX_Test_2.

Both these indexes can satisfy a seek to specific rows by key or range for SportId, the second index also includes an additional column so is covering for queries that require both or to order by AnotherId.

In the absence of the first index, the optimizer can utilize the second index equally well and the addition of a single int column, while making the index slightly wider, will be negligible and more than offset by the reduced overhead of having to maintain both.

Stu
  • 30,392
  • 6
  • 14
  • 33
  • Once the wider one is in "memory"/plan/"cache" SQL will never load the first one. It just ads confusion to the mix. – Sql Surfer Apr 22 '21 at 18:24
  • 2
    @SqlSurfer I don't think that's right. AFAIK the compiler ignores indexes being in the cache/buffer pool, it only looks at theoretical cost. Therefore if a narrower index will suffice then it will be loaded into memory even if an already cached wider index will suffice. – Charlieface Apr 25 '21 at 01:43