dropping an index speeds up query: why?

Question

By trial and error, i found that an index on a table on my DB was drastically affecting the performance of a query.

With index: execution time 30sec

Without index: execution time <1sec

It is the first time I see something similar, anyway the index makes sense since it is a non-unique non-clustered index on a Foreign key.

The table is the same I describe in this post (thanks to which I was able to create the FK). The next step was creating the index:

CREATE NONCLUSTERED INDEX [IX_RDATA_COMBO_VALUES] ON [dbo].[RDATA_COMBO_VALUES]
(
    [ID_REFERENCES] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

When this index exists a query becomes super slow, as I drop it the query is fast. The slow join in the query is about the VALUE field in this table and not the key field in the index (ID_REFERENCES), this is why I do not understand.

I suspect there is something about the inner workings of SQL server.

Which could be the reason why this happen?

I also noticed that if I create one index per table field the query becomes fast again (this is something I tried acting by "trial and error"). Thanks.

Answer 1

Adding an index is not a magical potion which automagically makes your queries run faster. Based on your question, and based on the fact that you were doing lots of trial and error, it seems as if some more knowledge in indexing will get you a long way.
Looking at the execution plan and seeing what your query is actually doing in the background will help you a lot in understanding why your index was messing things up.
There's a free video out there from a world class DBA, Brent Ozar (website is brentozar.com), which explains the basics of indexing and SQL Server performance.

Here's the link: https://www.brentozar.com/archive/2016/10/think-like-engine-class-now-free-open-source/