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…
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:
1 2 3 4 5 6 7 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 7 8 | 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.
