SQL SERVER – An Interesting Case of Redundant Indexes – Index on Col1 and Included Columns Col2 and Col3 – Part 5

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

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, you have been asking me many difficult questions. Let me simply ask you following question and instead of long answer – I would like you to give me a direct question answer.

Jon – Well, we can for sure try that way as well. What is your question?

Mike – When I see following columns as part of the index key, they are duplicate index correct?

Index A1: Col1 ASC
Index B1: Col1 ASC

In this case, you can see I have both the indexes are same and their order of the column is same as well. I think this means that they are redundant and duplicate index correct?

Jon – Mike, indeed a great question. If the included columns are also the same, they are indeed a same index. Now you have a simple answer.

Mike – No it is not a simple answer. You inserted the curveball of included column index – what is that now?

Jon – Ha ha – well SQL Server is not simple – here is the article you should read about Included Column Index it explains the concept quite well: Understanding Included Column Index along with Clustered Index and Non-clustered Index.

Once you read it lets us run a quick demo script. The script is very similar as earlier script but this time the index is set up as follows:

Index A1: Col1 ASC INCLUDE Col2 ASC
Index A1: Col1 ASC INCLUDE Col3 ASC

You can see that the key column (Col1) is same in both the indexes. However, the Included Columns are different. Now let us run following script.

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.

Now you can clean up using the following script:

-- Clean Up
DROP TABLE SampleTable1
GO

Mike – Jon this is getting indeed complicated for me. I think this subject looks very easy but indeed it keeps on getting confusing. I think I will go back to the professional efficiency tool which you mentioned last time. I have yet to download it, can you provide me more details about it.

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

Mike – Thanks Jon! I still have one more question but I will ask you about it next time.

Reference: Pinal Dave (https://blog.sqlauthority.com)