This is the fourth part of the series regarding Redundant Indexes. If have not read earlier part – there is quite a good chance that you will miss the context of this part. I quickly suggest you to read earlier three parts. On my production server I personally use embarcadero DB Optimizer for all performance tuning and routine health check up. I will be interested to know what is your feedback about the product.
Part 1: What is 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 the 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 the very large result set.
Once again if you have not read the earlier blog post, there are good chances 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 last week.
Mike – Jon, In very first day we discussed that we should also pay attention to the Order By clause of the columns used in the Indexes. Do you have any working demonstration example where we can understand that concept in depth.
Jon – Sure, Mike. I think you are referring to following example which we have discussed in the conversation.
Index 5: Col1 ASC, Col2 DESC
Index 6: Col1 DESC, Col2 DESC, Col3 ASC
Am I correct?
Mike – Yes, would you please explain how ASC and DESC works with the case of Redundant Indexes.
In normal case, we will not see the direction of the Columns used and consider Indexes as a redudant. For example when I see Index 5 and Index 6 I would see them as following.
Index 5: Col1 ASC, Col2 DESC
Index 6: Col1 DESC, Col2 DESC, Col3 ASC
I will just see that Index 5 has two columns (Col1 and Col2), whereas Index 6 have three columns (Col1, Col2, Col3) and they are redundant (read the very first blog post for why they are considered as a redundant).
However, from our conversation I do understand that the columns used to build indexes have a direction and we should consider them as an important factor before we make these indexes redundant.
Jon – Great Point Mike! You are absolutely correct. Let us understand the same using example.
We will create a single table with two different Indexes. Later we will run two different but similar looking queries on the table. We will observe how their index usage pattern is.
First create a table called SampleTable1.
USE tempdb GO -- Create table CREATE TABLE SampleTable1 (ID INT, Col1 INT, Col2 INT, Col3 INT) GO
Now let us create indexes on Sample Table.
Clustered Indexes are just created for reference – the results of this test will not be affected by its presence or absence of it. I have just created to rule out few doubts I anticipate by their absence. The important part is non clustered indexes. One of the non-clustered index is created on Col1 & Col2 DESC and another one is created on Col1 DESC, Col2 DESC, and Col3.
Table1: SampleTable1
Index on SampleTable1: IX_ST_Col1_Col2
Index on SampleTable1: IX_ST_Col1_Col2_Col3
-- Create Indexes on Sample Table1 CREATE CLUSTERED INDEX [CX_ST] ON SampleTable1 (ID) GO CREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2] ON SampleTable1 (Col1 ASC, Col2 DESC) GO CREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2_Col3] ON SampleTable1 (Col1 DESC, Col2 DESC, Col3 ASC) GO
Now let us populate both the tables. Both the tables have absolutely same data.
-- Populate tables INSERT INTO SampleTable1 (ID, Col1, Col2, Col3) SELECT RAND()*10000, RAND()*1000, RAND()*100, RAND()*10 GO 100000
Now is the most interesting part. For this first enable the execution plan in SSMS (shortcut key – CTRL + M).
In this test we will run two different queries on both the tables.
Let us first run two scripts for SampleTable1
-- Select from SampleTable1 SELECT Col1, Col2 FROM SampleTable1 st WHERE st.Col1 > 10 AND st.Col1 < 20 ORDER BY st.Col1 DESC, st.Col2 DESC GO -- Select from SampleTable1 SELECT Col1, Col2 FROM SampleTable1 st WHERE st.Col1 > 10 AND st.Col1 < 20 ORDER BY st.Col1 ASC, st.Col2 DESC GO
Let us check the execution plan:
Now when you see the execution plan, you will notice that both the queries are taking an equal amount of the resources to execute both the queries. They each take 50% of the time compared to the each other. However, we need to pay extra attention to the queries and see which indexes each of the query is using.
Let us examine very first query individually.
We can see from the execution plan – it is using the index – IX_ST_Col1_Col2_Col3.
Now let us examine second query individually.
We can see from the execution plan – it is using the index – IX_ST_Col1_Col2.
Now let us put a everything together quickly.
When WHERE Conditions are st.Col1 DESC, st.Col2 DESC they use the Index with Columns Col1 DESC, Col2 DESC, Col3 ASC.
When WHERE Conditions are st.Col1 ASC, st.Col2 DESC they use the Index with ColumnsCol1 ASC, Col2 DESC.
I think by for you must have guessed that there is a relationship between the order of the columns with the ORDER by Clause used in the WHERE condition.
Even though, Index IX_ST_Col1_Col2 looks like a Redundant Index, it is not a Redundant Index of all. They are matter of the fact very different Indexes. They should be both kept if both are used in the queries.
There are a lot more things we can discuss over here – but this is just a starter. Any question so far.
Mike – Thanks Jon, I understood so far. Here is the question in my mind – If I am following two Indexes they are considered as a different Index am I correct?
Index AA: Col1 DESC, Col2 ASC
Index BB: Col1 ASC, Col2 ASC
Am I correct?
Jon – Yes, they are different Indexes for sure.
Mike – Oh okey I perfectly got it. Let me quickly summarize it.
Even though Indexes look redundant there may be some queries which may find them useful. ORDER of the columns used in the Indexes is very important when considering the redundancy of the indexes. If the column order is different the Indexes are also considered as a different indexes. Before dropping the indexes one should properly validate the usage patterns of the indexes and query workloads. One should properly test everything before taking any actions.
Jon – Good summary. Let us do a quick clean up.
-- Clean Up DROP TABLE SampleTable1 GO
Mike – Perfect, I think I know a lot now.
Jon – Do you really think you know everything?
Mike – Well, sort of. Right?
Jon – If you think so answer me following question –
Are following two indexes should be considered as a redundant indexes or are they different indexes?
Index AA: Col1 DESC, Col2 DESC
Index BB: Col1 ASC, Col2 ASC
Mike – From what I understood today – they are Not Redundant Index.
Jon – Glad I asked this question. You are WRONG!
Mike – What? How come?
Jon – Let us talk about this future blog posts – I think I need to teach you a lot of things!
Mike – Thanks, I am indeed looking forward to learn more. So what I understand is that it is quite possible that the indexes may look redundant but they may not be redundant indexes. What I understand is that before dropping any indexes I need to identify if the indexes are used or not. Is there any professional tool which can help me?
Jon – Absolutely, I suggest that you try embarcadero DB Optimizer. It helps finding users the precise details related to index usage. It also indicates if Indexes are useful or useless. This tool is very easy to use and help in various aspects of the performance tuning. Here is detailed articles on this subject Part 1 and Part 2. This tool can help you with writing proper queries, do indexes maintenance and optimize overall health of the server.
Mike – Thanks Jon!
Reference: Pinal Dave (https://blog.sqlauthority.com)
