1

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.

Community
  • 1
  • 1
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
  • What type of query is optimized? Which one of the CRUD operations? – gotqn May 19 '17 at 11:22
  • Can you give us the Execution Query Plan of your request, before and after the Index is deleted ? Also make sure to update Statistics before each test. – Hybris95 May 19 '17 at 11:22
  • 1
    Can you provide the with index enabled and after index is dropped execution plan using this link, https://www.brentozar.com/pastetheplan/ – Mazhar May 19 '17 at 11:36
  • Can you paste the query itself or relevant parts? It is not always good to use an index, but without the execution plans or at least the query it is hard to provide details. – ewramner May 19 '17 at 11:50
  • @Cool_Br33ze i updated statistics and here the plans with (Fast) and without (SLow) index: https://www.brentozar.com/pastetheplan/?id=S174yP2e- https://www.brentozar.com/pastetheplan/?id=S174yP2e- – UnDiUdin May 19 '17 at 12:05
  • 1
    you've linked the same plan twice – Mazhar May 19 '17 at 13:49

1 Answers1

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/

Eli
  • 2,538
  • 1
  • 25
  • 36