SQL SERVER INDEX (Part 3): Concatenated Index

Firstly, I recommend watching this How to Think Like the Engine - YouTube by Brent Ozar on YouTube (all parts). It is incredibly helpful foundational material before you begin your SQL performance tuning journey.

The most important consideration when defining a concatenated index is how to choose the column order so the index can be used as often as possible.

To demonstrate, I will use the StackOverflow2010 database. Feel free to follow along with my examples. If you haven't, please checkout Tools I Used During Query Performance Troubleshooting to see how to setup to obtain execution plan and the number of logical reads.

Let's consider two queries:

Query 1:

SELECT ID
FROM   dbo.Posts
WHERE  OwnerUserId  = 26837
       AND PostTypeId  = 2 

Query 2:

SELECT ID
FROM   dbo.Posts
WHERE  OwnerUserId = 26837 

How do we create an index that can benefit both queries? There are two choices:

Choice 1:

CREATE NONCLUSTERED INDEX [IX_OwnerUserID_PostypeId]
  ON [dbo].[Posts] ( [owneruserid], [posttypeid] ) 

Choice 2:

CREATE NONCLUSTERED INDEX [IX_PostypeId_OwnerUserID]
  ON [dbo].[Posts]( [posttypeid], [owneruserid] ) 

Now, let's examine the execution plans of the two queries when one of these indexes is available.

Choice 1 (Index IX_OwnerUserID_PostTypeID):

With this index, both queries benefit, as they can perform index seeks, resulting in small number of logical reads for both.

Choice 2 (Index IX_PostTypeID_OwnerUserID):

In this case, the second query cannot perform an index seek on IX_PostypeID_OwnerUserID. It can only perform an index scan, leading to a higher number of logical reads.

Although the index contains the OwnerUserID column, it isn't the first key column.

For an index to be useful in a query (from index seek), one of the columns in the predicate must be the first key column in the index. Thus, the IX_OwnerUserID_PostTypeID qualifies to use but IX_PostypeID_OwnerUserID doesn't for two given queries.

Consultants or DBAs often face challenges when defining concatenated indexes, as they might lack detailed knowledge of the applications and its access patterns.

The developers, on the other hand, have the business domain knowledge need to design indexes that best support the application's performance.

Cleanup

DROP INDEX [IX_PostypeId_OwnerUserID] ON [dbo].[Posts];

DROP INDEX [IX_OwnerUserID_PostypeId] ON [dbo].[Posts];