Lesson Learned #485: Index Recomendation or the Importance of Index Selection in SQL Server

[Jose_Manuel_Jurado]

Today, I worked on a service request that caught my attention regarding index missing recommendations given by SQL Server. I want to share some findings and lessons learned about creating and optimizing indexes.

We have the following script: 

CREATE Table Review
( ID INT Primary Key Identity(1,1), 
  Age INT, 
  TypeMember CHAR(4))

INSERT INTO Review (Age, TypeMember) values(1,'TYP1')
INSERT INTO Review (Age, TypeMember) values(2,'TYP2')
INSERT INTO Review (Age, TypeMember) values(1,'TYP0')

INSERT INTO Review (Age, TypeMember) select Age, TypeMember from Review

Afer running the last 'INSERT' multiple times, we ended up with 12 million of rows running in the review table. 

When we executed the following query with some conditions: 

select top 100 * from Review 
WHERE TypeMember='TYP0' and Age=1

I assumed that the recommended index would be :

CREATE NONCLUSTERED INDEX [TypeMember_Age]
ON [dbo].[Review] ([TypeMember],[Age])

However, the recommendation was instead of using the Age column first and second the column TypeMember.

CREATE NONCLUSTERED INDEX [Age_TypeMember] 
ON [dbo].[Review] ([Age],[TypeMember])

This led me to investigate more about the effectiveness of composite indexes. 

When defining indexes, especially composite ones, it is important to consider several factors:

1. Unique Values in Columns (Cardinality):

   • Cardinality refers to the number of unique values in a column. Columns with high cardinality (many unique values) are usually more effective for indexing.

2. Column Size/Width:

   • Columns with smaller size or width are more efficient to index. This is because they occupy less space and search operations can be faster.

3. Column Data Type:

   • Data types can influence performance. For example, searching in an INT column generally has a lower cost than searching in a CHAR column.

This case underscores the importance of considering these factors when defining our indexes. Our initial intuition about the index structure might not always be correct; instead, we should rely on analysis and statistics provided by SQL Server.

In the end, choosing the right index can have a significant impact on the performance of our queries. Pay attention to SQL Server's recommendations and adjust your indexes based on tests and observations.

I hope you find this information useful!

Enjoy!