Missing Index Recommendation Column Order

Missing index recommendations are helpful but are not to be 100% trusted.  Blindly creating every index that SQL recommends is a recipe for disaster.  The recommendations are one tool to use in a belt full of tools.

If you are testing indexes that are being recommended, it’s important to note the column order.  I had originally assumed (we all know what happens when you assume) that the recommended index would have the columns in the ideal order…

dikembe mutombo no GIF

Not so much.  The columns are actually based on the ordinal position of the column in the table.  Let’s work through an example to prove that the missing index recommendation will not have the best column order for the most efficient plan.

Let’s open SSMS, press Ctrl-M to include the execution plans, and start with this query against the StackOverflow2013 database:

SELECT DisplayName, Location, DownVotes, Reputation 
FROM Users
WHERE Reputation >= 100000
AND DownVotes >= 100;
GO

When we run the query above, we’ll get the execution plan below that includes a missing index suggestion:

We can right click on green Missing Index text and select Missing Index Details to open the script in a new query window to apply:

We’ll call this index IX_Suggested_Order and create it:

USE [StackOverflow2013]
GO
CREATE NONCLUSTERED INDEX [IX_Suggested_Order]
ON [dbo].[Users] ([DownVotes],[Reputation])
INCLUDE ([DisplayName],[Location])
GO

Now when we run our original SELECT query we no longer have a missing index suggestion and the index we created was used:

If we run SET STATISTICS IO ON and run the query, we can check the Messages tab to get more information on disk activity when the query runs:

To make this more readable, I always like plugging the text into https://statisticsparser.com/ which gives us:

With the suggested index in place, we are down to only 94 logical reads which is great.  But what if we created the index with the Reputation and DownVotes columns switched:

USE [StackOverflow2013]
GO
CREATE NONCLUSTERED INDEX [IX_Chosen_Order]
ON [dbo].[Users] ([Reputation],[DownVotes])
INCLUDE ([DisplayName],[Location])
GO

We run the query again and see the new IX_Chosen_Order was used:

And there were only 9 logical reads!  For this example, the totals are small either way.  If more data was involved though, that 10x difference in logical reads could be significant.

We’ve shown that missing index suggestions are helpful but far from perfect.  Keep column order in mind the next time you’re looking to test or apply one.

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s