Question: Can an Index Reduce Performance of SELECT Query?
Answer: Of course!
I have discussed this subject multiple times on this blog. Of course, Index can reduce the performance of any query and particularly of SELECT queries without even being that indexed utilized directly in the query.
There is a huge misconception that Indexes only reduces the performance of INSERT, UPDATE and DELETE statement. The truth is that Indexes also negatively impacts on the SELECT queries as well and the performance penalty sometimes extremely huge!
There are not many people talking about the indexes negatively impacting the SELECT queries because they have not been looking for that particular impact on the queries or they are not measuring the impact in such manner.
I recently asked this question in my newsletter why no one is talking about this subject and I had received many answers. However, the most interesting answer I had received from the renowned SQL Server Expert – “Most of the SQL Server Performance Tuning Experts do not want to talk about this topic is because they do not know why actually SELECT is negatively impacted by Index which is not used for their query. Yeah! Ignorance is the real reason!“
Here is the blog post which I have written on the subject and I suggest you read the blog post and watch the video. SQL SERVER – An Index Reduces Performance of SELECT Queries
Here is another blog post which talks about three most asked question following watching the video mentioned in the above blog post 3 Questions: An Index Reduces Performance of SELECT Queries.
Question 1: Why did you create an index if that was not a great idea?
Question 2: What are the other ways to improve the query performance besides indexes?
Question 3: How to identify indexes which are not used for any queries?
Following the above two blog post, I received a very interesting Coffee Mug from my friend Harvey Powell. You can read about that blog post here: Coffee Mug – Indexes and SELECT Queries.
Finally, here is the video which demonstrates the video that index, reduces the performance of SELECT query.
Let me know your thoughts in the comments section.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Dear Pinal,
I’ve debugged this one and can explain to you why this is happening. It is a corner case which only occurs when QO evaluates the JN -> SM optimization rule for a join operation with multiple join predicate keys, and when there exist multiple indexes that can provide a “natural sort” for the merge operation (ie any index that can provide data sorted by ANY combination of the multiple columns used in the join predicate.) The one index with the highest index id which also matches that requirement is the one whose composed key is used as the pattern to later decide which of the indexes can be used. In the case you’re presenting, for the merge join alone, it is equally efficient to receive the data sorted by SalesOrderId, and then by ProductId than it would be to receive it sorted by ProductId and then by SalesOrderId. However, for the outer set in your case, the stream aggregation to calculate the average needs the data sorted by SalesOrderId, so the second option is not viable. The alternative (hash aggregation followed by a sort by ProductID, and then SalesOrderId to satisfy the requirement of the merge join) would be to costly and therefore is dismissed.
Regards,
Nacho
I really love how you think Nacho. Great work and thank you for doing this exercise.