This is the seventh part of a series regarding Redundant Indexes. If you have not read the earlier parts – there is quite a good chance that you will miss the context of this part. I suggest that you should quickly read the earlier four parts. On my production server I personally use Embarcadero DB Optimizer for all performance tuning and routine health check ups. I will be interested to know your feedback about the product.
Part 1: What are Redundant Indexes?
Conversation between Mike and Jon – where they discuss about the fundamentals of Redundant Indexes.
Part 2: Demo – What kind of Redundant Indexes are Useful with a smaller result set
Here Jon demonstrates a script where even though Redundant Indexes exist they are extremely useful and should not be dropped. The result set contains a very small result set.
Part 3: Demo – What kind of Redundant Indexes are Useful with a larger result set
Here Jon demonstrates a script where even though Redundant Indexes exist they are extremely useful and should not be dropped. The result set contains a very large result set.
Part 4: An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3
In this part Jon demonstrates a script where the direction or order clause of the index is different, making the index behave very differently. To quality any index redundant, the order of the index should also be considered.
Part 5: An Interesting Case of Redundant Indexes – Index on Col1 and Included Columns Col2 and Col3
In this part Jon demonstrates how included columns also take part in the decision making if the column is redundant and duplicate or not.
Part 6: An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3
In this part Jon and Mike discuss how Included Columns play a role with Redundant Indexes. It is important to know if the index is redundant or not.
Once again if you have not read the earlier blog posts, there is a good chance you will miss the core point of this blog post. I suggest you at least read the conversation between Mike and Jon to get familiar with their personalities.
Jon and Mike continue their conversation from an earlier blog post.
Mike – This is great, I learned a lot about duplicate indexes in the last few series of questions and answers. I even tried out all the scripts which we have discussed so far. Here is one final question to you Jon. Are you ready?
Jon – Mike, I am always ready. What is your question?
Mike – Jon, this time question is very simple – Are redundant indexes are good or bad? Please answer this question with “Good” or “Bad.” I do not want your “It Depends” answers over here.
Jon – Well, actually It De…., hm… You want an answer in one word. So here is the answer in one word: Bad. Now I gave you my answer, can I elaborate a bit more?
Mike – Sure you can.
Jon – Well, if you are beginner in the field of performance tuning and have no idea what to do when you encounter redundant indexes or duplicate indexes, just go ahead and drop the duplicate indexes, as in most of the cases, duplicate indexes are bad. They have several disadvantages but I would like to highlight two of the biggest disadvantages here –
1) All the duplicate indexes are updated when data in one of the index changes and this wastes CPU, memory and IO resources.
2) If you have redundant indexes, when any query has to use the indexes they will have to use one of those indexes. It is not possible to use all the duplicate indexes. When SQL Server engine is going to use only one indexes at the end, why have them from the beginning?
Additionally, do not forget that duplicate indexes also confuse users.
Mike – Thanks Jon, here is the follow up question: Is there any scenario where an exact duplicate index with the exact order of the columns and exact order of included indexes can be helpful? What I mean is that if there are two indexes which are exactly same to each other – are they ever useful?
Jon – No.
Mike – I got it. Well, so now we know about Duplicate Indexes or Redundant indexes, besides this what are the other challenges related to Indexes?
Jon – The real challenge with Indexes is to know what kind of index is to be created which will be the most beneficial to your workload. It is easy to create an index for one query or two queries but the key is to create one or a few indexes which can generically improve the performance of most of the queries running in your server.
Mike – Oh, I see. You are correct, we need to come up with indexes which are most helpful to your workload and not just fewer queries. If we create one index per query, we will end up with hundreds of indexes, which is a pretty bad idea. Jon, I think it is a pretty big challenge to come up with indexes for workload. I believe we need to automate this process. Do you know how we accomplish this easily?
Jon – I knew you would ask that. We have discussed this earlier. The database optimization tool Embarcadero DB Optimizer is your best friend. It helps users find the precise details related to index usage. It also indicates if Indexes are useful or useless. This tool is very easy to use and helps in various aspects of performance tuning. This tool can help you with writing proper queries, do index maintenance and optimize the overall health of the server.
Mike – Thanks Jon!
Reference: Pinal Dave (https://blog.sqlauthority.com)
