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.