Recently, Jes asked the team an index tuning question: “If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?”

I immediately loved the question because I’d never really thought about it before. I typically think of index hints as being a very risky game and avoid them whenever I can– after all if someone drops the index you’ve hinted, any query hinting a non-existent index will start to fail. (That’s a really bad day!)

Even so, some people love index hints. And some folks inherit code that’s already littered with index hints, and they may not be able to change it. In those cases, are the missing index requests in SQL Server DMVs going to be skewed by all those hints?

Testing missing index requests

There’s a great Books Online page documenting limitations of the missing index feature, but I’m a huge fan of getting my hands dirty and testing these things myself. I powered up a virtual machine with SQL Server 2012 and the AdventureWorks2012 sample database to take a look.

First, I ran a simple query that I knew would generate a missing index request:

Sure enough, I got a missing index request. SQL Server scanned an existing nonclustered index to satisfy my query, but pointed out that a new nonclustered index just on the FirstName column would be just perfect for that query.

I wanted to preserve the exact plan it had selected, so I reran the query with an index hint– however I hinted the exact nonclustered index that it had decided to scan anyway:

And look at that! No missing index request on that second query. The index hint “broke” the missing index feature. SQL Server didn’t point out that an index just on FirstName would help the query out, even though it was using the exact same execution plan it used before where it DID point out that the plan wasn’t perfect.

Not so fast, index tuner

At this point, I was pretty excited. What an interesting point! I’ve never read about this before. What a delicious geeky snack this is! Then I came back to the real world and thought, “Well, it’s kind of odd that I haven’t read about this. I should look at those published limitations again.”

The Missing Index feature in SQL Server is pretty complicated. There’s a few things that have burned me a couple of times, so I’ve learned to use it pretty carefully. As soon as I looked at the list of limitations, I had a pretty good idea I might have just been fooled.

Let’s go back and look at the queries we ran before in a different way. If we take a closer look at the first query’s execution plan (this was the query without an index hint that generated a missing index request), we can see that it received “FULL” optimization:

If we do the same thing for the second query (it had an index hint and did NOT generate a missing index warning), we see something different:

When we added the index hint, we did something subtle: we simplified the process of optimization for SQL Server. We gave it fewer choices– in fact, given the query there was only ONE choice for optimization. Since it only had one choice, it did a “TRIVIAL” optimization, which is more lightweight.

One side effect of trivial optimized plan is that they don’t generate missing index requests. This is one of those documented limitations we talked about earlier.

So in this case, using an index hint didn’t generate a missing index request– but that was a side effect of the change it made to the optimization process.

The second test: A more complex query

To find out if this holds true for queries using full optimization with index hints, I just changed my query up a little bit.

Here’s a little trick: It’s not hard to write a difficult-to-optimize query in AdventureWorks. All you have to do is use one of the built-in views. (Sorry, views, you just often make things messy. It’s just the truth.)

Our new query for testing is:

This query gets “FULL” optimization. And it generates the missing index request for the Person.Person table, even though we’ve used an index hint on that table.

Missing indexes are complicated, index hints are risky

To sum up: using an index hint won’t necessarily prevent you from getting a missing index request. In some cases it may change a query that was getting “FULL” optimization to “TRIVIAL” optimization, in which case the missing index request won’t be generated, but that also holds true for all queries getting “TRIVIAL” optimization. (Plans getting trivial optimization have some other quirks, by the way. One notable one is that they won’t consider indexed views for execution, either.)

The bigger point is that the missing index request feature in SQL Server is very helpful, but it’s not perfect. It gives you some of the “big picture”, broad stroke requests, but it really just gives you a first direction to look in.

In order to tune a workload, you also need to know a lot about your queries that run. You may have super frequently run queries that get a “TRIVIAL” optimization because the queries are simple– whether or not those queries have an index hint. If the queries are run frequently enough it can absolutely be beneficial to tune your indexes for them, even if they never make an appearance in the missing index DMVs.

I really enjoyed looking into this question because it reminded me that all changes can have unexpected side-effects. Maybe a hint makes a query faster today, but who’s to say that it’s the best optimization decision for all time, and what else you may impact along the way?

Learn More in Our Execution Plan Training

Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.