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)