dropping an index speeds up query: why?

Posted: - Source : stackoverflow

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.