Blog

Let me ask you a question: do you want to boss the SQL Server query optimizer around?

Query Optimizer flowchart

If you answered no: good. You’re willing to let the query optimizer do its job, which is to find the least expensive way to run a query, as quickly as possible.

If you answered yes: you’re a brave person.

Maybe you’ve hit upon the perfect index for a specific query, but for some reason the optimizer won’t use it. But you know it improves performance. How can you make the query optimizer listen to you?

Index hints are a powerful, yet potentially dangerous, feature of SQL Server.

Let’s look at an example. I’m working with AdventureWorks2012. The database has two related tables, HumanResources.Employee and Person.Person. They are related through the BusinessEntityID column. I want to retrieve information about the users and their logins.

SELECT PER.FirstName,
PER.LastName,
EMP.LoginID
FROM HumanResources.Employee EMP
INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID;

Let’s look at the execution plan.

index hints 1

A nonclustered index seek is performed on Employee.AK_Employee_LoginID, a nonclustered index on the LoginID column. A clustered index seek is performed on Person. Note the cost is 0.217439.

I notice that the Employee table has another index, PK_Employee_BusinessEntityID, which is on the BusinessEntityID column. I want to force my query to use this index instead. I can do this by using the WITH (INDEX) hint.

SELECT PER.FirstName,
PER.LastName,
EMP.LoginID
FROM HumanResources.Employee EMP WITH (INDEX (PK_Employee_BusinessEntityID))
INNER JOIN Person.Person PER ON PER.BusinessEntityID = EMP.BusinessEntityID;

Let’s look at this execution plan.

index hints 2

Now, a clustered index scan is performed on Employee. Note, though, that the query cost has increased – to 0.220402.

In your case, the index you force the query to use might get the better cost – for now. But what happens when more data is added to the table, and statistics change? What happens if you update SQL Server, and the query optimizer changes?

Eventually, the index you’re using may not be the best one for the job – but SQL Server is going to continue to use it anyway. You’ve told it to do so, and it’s doing to keep doing it.

Think about how you would get rid of it. Is there an easy way to search all of your code – application code, stored procedures, report definitions – for this specific index hint, to remove it?

Another point to consider is what would happen to that code if the index was disabled or deleted? Would it continue to run? Let’s give it a try. I issue a disable index command.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee DISABLE;

Then I run the same query as before, and what happens? I get an error:

Msg 315, Level 16, State 1, Line 1
Index “PK_Employee_BusinessEntityID” on table “HumanResources.Employee” (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

Index hints can be a powerful feature, but use with caution. Look for other ways to optimize your query – perhaps a different index can be created, or your query can be rewritten. If you can’t find a better way, document the use of the index hint and its purpose. When you upgrade SQL Server, test whether that index is as effective.

Remember, the query optimizer does its job really well – let it.

↑ Back to top
  1. Please tell this to Microsoft. Have you seen Sharepoint’s queries? Here’s an example (btw, SQL recommends an index for 90% improvement, but hey, what would it know.. MS Spoint dev: INDEX=AllUserData_PK, there will never be any better index. EVER)

    JOIN AllUserData AS t2
    WITH
    (
    NOLOCK,
    INDEX=AllUserData_PK
    )
    AND (t1.SiteId=@SITEID)
    ORDER BY UserData.[tp_ID] DESC OPTION (FORCE
    ORDER, MAXDOP 1)

    • SharePoint is like the moody teenager of Microsoft products – whatever it’s supposed to do, it does the exact opposite!

  2. Pingback: (SFTW) SQL Server Links 25/10/13 • John Sansom

  3. One place where I get boxed into using index hints is where a stored proc uses table vars and the optimizer assumes there is just one row when in fact there are many more. Any suggestions? I am not a fan of hints either…

    • Switch your code from using table variables to temp tables! There aren’t a lot of other options. Table variables stink sometimes – and this is one of those times.

    • Add OPTION(RECOMPILE) to the queries with table variable.
      The issue with TV in stored procedures is that the query plans are created when the TV is empty. OPTION(RECOMPILE) forces a new execution plan at the time the query is executed – which creates plan optimal for the TV’s row count.

  4. They are there for a reason. The reason should be the exception rather than the rule.

    But I have found specifically filtered indexes often being ignored and have occasionally had the need to use a hint (see how that went? Not a rule to use them!)

  5. Firstly, apologies for posting on an old topic.

    We occasionally need to use index hints, due to the large amounts of data in various tables, but we have also seen SQL ignoring these index hints at times.

    Have you come across this before?

    • Alex, it’s going to be hard to give you an answer without more background. You should go to dba.stackexchange.com and fill out a question. Lots of smart people are over there checking it all the time. If you don’t get a good answer in a day or two, post the link here and I’ll see if I can help out!

      • Thank you for coming back to me.

        We have also posted to sqlperformance.com and had a few responses back.

        Alex

  6. Agree wholeheartedly but there is a genuine place for index hints – when you have a badly performing query and you want to manually try different query plans.
    In this case – you use the hints to find the best plan (hopefully) then you figure out why the optimiser isn’t using this plan.
    often is a case of re-updating stats or use of a judicious covering index to overcome the issue.

    Speaking of hints, don’t get me started about the noexpand hint – grrr.
    it forces you to write breaking code to use the materialized view.

  7. Another reason to use index hints is to avoid deadlocks. E.g. we have a deadlock on clustered index, but even adding a non-clustered index in to a table structure doesn`t make any effect. Query plan still using clustered index and deadlock occurs.
    Of course there are always could be another way to evade deadlocks, but sometimes it`s difficult to change complex internal logic(change operation sequence, use transaction/lock hints etc.) so you can carefully use the index hint IMHO.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

css.php