Index Hints: Helpful or Harmful?

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.

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.

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.

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.

Previous Post
Announcing sp_BlitzFirst® for Troubleshooting Slow SQL Servers
Next Post
Frequently Asked Questions About TempDB

18 Comments. Leave new

  • 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)

    Reply
    • Jes Schultz Borland
      October 24, 2013 7:04 am

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

      Reply
  • Alan Rothschild
    October 25, 2013 3:48 pm

    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…

    Reply
    • Jes Schultz Borland
      October 28, 2013 7:28 am

      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.

      Reply
    • Ondrej Bouda
      June 23, 2015 1:33 am

      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.

      Reply
  • 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!)

    Reply
  • Alex Parris
    July 15, 2014 4:41 am

    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?

    Reply
    • Jes Schultz Borland
      July 15, 2014 8:21 am

      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!

      Reply
      • Thank you for coming back to me.

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

        Alex

        Reply
  • 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.

    Reply
  • 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.

    Reply
  • Agree that Index hints are not a good idea. Question is what to do when you have an excellent index that the optimiser refuses to use?
    I’ve a query that does an Index Seek, Nested Loop Join and then a Key Lookup taking 30 Seconds. When I create a covering index (with or without INCLUDE to cover all columns) the optimiser ignores it, DESPITE it being a SQL Optimiser recommended index. When I use the index hint it does an Index Seek and runs sub-second. See this article on SSC: http://www.sqlservercentral.com/Forums/Topic1706485-2799-1.aspx?Update=1
    Interested in your comments.

    Reply
  • one comment on an aspect of this that isn’t covered here

    select t1.*,t2.* from t1 join t2 on t1.pk=t2.someKey where t2.someTime >=@param1 and t2.sometime<@param2

    has a tendency to estimate the select sqrt tableRows worth of info (in my case 300k rows, rather than 88 when only 1 day of data was wanted); i resolved this issue 2 ways: 1 with an index (NC_someTime) , and the other was with an option (recompile) . runtime went from ~4 mins, down to 12 seconds with the index hint, then down to 3 seconds when i told it to recompile.

    The moral of the story; once again, once it has the right assumptions, the query optimizer out-did the dev.

    (we don't have 10 years of data, the discrepancy between 300k and 88 is that the different query plan with the correct info applied one of the other predicates differently, nested loops joined onto the range, rather than table-scan with post-filter)

    Reply
  • John Gephart
    March 2, 2018 11:02 am

    Cardinality anomalies can make index hints very useful. I once inherited a 7TB db on SQL2008 with a table containing 800+ million rows of multi-national e-commerce data. 80% of the data was US and the other 20% was international. We had an index optimized for US data and another used for a different purpose that happened to work for the international data. Since the query optimizer only sampled large tables when updating statistics, we would occasionally see the optimizer choose the international index if the sampling ‘caught’ mostly international data. Performance would go from sub-second to over 4 minutes in a 24×7, global e-commerce application. Since this application was handling customer payments, it would effectively take the site down. Adding a query hint to force the optimizer to use the US index solved the problem and it worked for several years as the cardinality changed slowly and didn’t balance out while I was at the company.

    Reply
  • What are your thoughts on using more generic hints like FORCESEEK, HASH JOIN, etc.?

    Reply
    • When I see folks using those, I tend to open up query plans and ask folks if they can explain why SQL Server is choosing to do what it’s doing. If they can explain why SQL Server is doing it, where the row estimates come from, why the estimates are wrong, etc., then I’m fine with the use of hints like that.

      But between you and me? Most of the time, they don’t know. 😀 In which case, I say it’s probably not a good idea to boss an app around if you don’t even understand what it’s doing.

      Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}