SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 6

This is the sixth 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 four 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.

Part 4: An Interesting Case of Redundant Indexes – Index on Col1, Col2 and Index on Col1, Col2, Col3 – Part 4
In this part Jon demonstrates a script where the direction or order clause of the index is different making the index behaves 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 – Part 5
In this part Jon demonstrates how included columns also take part in the decision making of if the column is redundant and duplicate or not.

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 earlier blog post.

Mike – Jon, I think I am now more clear with the redundant indexes scenario. I have understood that following indexes are not redudant indexes even though they have key column exactly same becasue of the included columns are different from each other. Am I correct to understand the same?

Table1: SampleTable1 
Index on SampleTable1: Col1 ASC INCLUDE Col2 ASC
Index on SampleTable1: Col1 ASC INCLUDE Col3 ASC

Jon – Excellent Mike, I think you got this one correct. It makes me happy that we are not the right path now. Let me know if you have any further question.

Mike - The matter of the fact, I do have a question. I do understand that both the above indexes are different kind of index and they don't duplicate but is there any possibility to create  a single index which can be as effective as both the indexes. I think it should be possible as both of the above index have the same keys.

Jon – Indeed a great question, actually you can do that as well.

Mike – Really! If that is the case, would you please explain me how to do that.

Jon – Sure, it is actually very easy to do so. Instead of above two indexes, you can just create a single index which has both the columns included in the INCLUDED clause. Here is the example:

Table1: SampleTable1 
Index on SampleTable1: Col1 ASC INCLUDE Col2, Col3

Here you can see that you have created a single index which is effective for Col1 key column and Col2 and Col3 in an included column. Remember, order of the column always mattered however, it does not matter when we are discussing INCLUDED Columns. In this case, it would have not matter if we have written INCLUDE Col3, Col2 instead of INCLUDE Col2, Col3 in the create index.

Mike – Oh, so this is interesting to know that column order does not matter in the Included clause. Would it be possible to see a demo where you demonstrate that this single Index can be replacement of the above two indexes.

Jon – Absolutely – let us check.

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 ASC INCLUDE Col2 ASC and another one is created on Col1 ASC INCLUDE Col2 ASC

Table1: SampleTable1 
Index on SampleTable1: Col1 ASC INCLUDE Col2 ASC
Index on SampleTable1: Col1 ASC INCLUDE Col3 ASC

-- Create Indexes on Sample Table1
CREATE CLUSTERED INDEX [CX_ST]
ON SampleTable1 (ID)
GO
CREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2]
ON SampleTable1 (Col1)
INCLUDE (Col2)
GO
CREATE NONCLUSTERED INDEX [IX_ST_Col1_Col3]
ON SampleTable1 (Col1)
INCLUDE (Col3)
GO

Now let us populate the table. 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, Col3
FROM SampleTable1 st
WHERE st.Col1 > 10 AND st.Col1 < 20
ORDER BY st.Col1 ASC, st.Col3 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.

Now let us examine second query individually.



We can see from the execution plan – it is using the index – IX_ST_Col1_Col3.

Now you can see how the included columns impact the usage of the index. Even though the key column is the same – included columns are integral part of the index and they need to be evaluated.

Creating Included Index

So far everything is very similar to our earlier blog posts. Now let us create a new included column index where we have Col2 and Col3 are included indexes.

-- Create Included Index
CREATE NONCLUSTERED INDEX [IX_ST_Col1_Col2_Col3]
ON SampleTable1 (Col1)
INCLUDE (Col2,Col3)
GO

As discussed you can change the order of the columns in the Included clause and it will not matter. Now let us run the SELECT statement which we have run earlier and see which index each of the SELECT statement is using.

-- 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, Col3
FROM SampleTable1 st
WHERE st.Col1 > 10 AND st.Col1 < 20
ORDER BY st.Col1 ASC, st.Col3 DESC
GO

You can notice that both the SELECT statement is using newly created index IX_ST_Col1_Col2_Col3 as this single index now satisfies the need of the both the indexes created earlier. Now when you create this new index, your earlier two indexes IX_ST_Col1_Col2 and IX_ST_Col1_Col3 are not useful and they should be dropped eventually.



     

Now you can clean up using the following script:

-- Clean Up
DROP TABLE SampleTable1
GO

Mike – Wow! Jon, more I learn about indexes, I feel more I do not know about them. I think I am going to rely on embarcadero DB Optimizer for most of my performance tuning needs.

Jon – Absolutely,  embarcadero DB Optimizer is your best friend.  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. This tool can help you with writing proper queries, do indexes maintenance and optimize overall health of the server.

Filed under: PostADay, SQL, SQL Authority, SQL Index, SQL Query, SQL