on March 7, 2017
Ever see those little green messages at the top of an execution plan?
Those are missing index suggestions.
SQL Server loves to suggest that you consider changing up your index game.
The hint looks like this:
There may be more than one index suggestion - but there is never more than one green hint
SQL Server can suggest multiple indexes for a single query. It’s difficult to tell when that happens, though.
Not only will you only ever see one hint at the top of the plan, it isn’t necessarily the index that SQL Server thinks will help the most! And if you right click on the plan and script the index to a new window, you only get the index request from the green hint. If there’s another one, it doesn’t script.
You can right click the plan and look at the XML and look for the missing indexes section. But it’s a bit of a pain to do each time. You can also open the plan in the free Sentry One Plan Explorer (or run the query from Plan Explorer in the first place), but you may not always have it installed.
A quick check: look at the properties of the select operator
With that top left operator highlighted, I open the properties window and, voila– I can expand ‘Missing indexes’ and quickly tell that there are two index suggestions for this query:
The green hint isn’t even the index SQL Server thinks will help most
Check out those ‘impact’ numbers. SQL Server thinks one of these indexes will reduce the estimated cost of the query plan by 93.6%. Unfortunately that is not the hint showing in green– and it’s not the hint that will script out if I right click the plan and script indexes.
What’s the best way to view both index requests?
You can expand out all the nodes to view the index requests in the properties pane– but it’s kind of a pain, no pun intended. I really like this just for a quick check of the suggestion count, and then I’ll either go to the XML or to Plan Explorer. (Much of the time there’s just one suggestion.)
Should we add both indexes?
Nope. The missing index request is being a bit over-eager here, and it’s asking for more than it needs. I’ll write more about that in tomorrow’s post.