FORCE ORDER Query Hint

Facebooktwitterredditpinterestlinkedinmail

When querying out data, SQL Server has a ton of different query hints. One less commonly known query hint is FORCE ORDER. So here we will talk about what this query hint does and how we can use it efficiently.

FORCE ORDER
When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query. Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.

To use the FORCE ORDER query hint, you will want to add this at the end of your query.

 
Example Without FORCE ORDER

SELECT	*
FROM	Habitat H 
INNER	JOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
INNER	JOIN Animal A ON AH.AnimalID = A.AnimalID

This is the actual execution plan without the FORCE ORDER hint. You can see that SQL Server starts at the table Animal and AnimalHabitat tables, then goes to the Habitat table. If you look at our query above, we clearly list the tables in the order of Habitat, AnimalHabitat, then Animal.

Execution Plan Without Force Order

 
Example With FORCE ORDER

SELECT	*
FROM	Habitat H 
INNER	JOIN AnimalHabitat AH ON H.HabitatID = AH.HabitatID
INNER	JOIN Animal A ON AH.AnimalID = A.AnimalID
OPTION	(FORCE ORDER)

In the screenshot below, it shows the actual execution plan with the FORCE ORDER hint. In the new query, you can see that SQL Server starts with the Habitat and AnimalHabitat tables and then moves to the Animal table. It does this because when we turn on the FORCE ORDER option, it uses the order of the tables specified in the query syntax.

Execution Plan With Force Order

 
What Is This Used For
The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query. Normally you will just let SQL Server figure out how to get the data from the database. It does such a good job at it, that you do not usually need to override this functionality.

Occasionally SQL Server will not figure out the most optimal way to pull this data out. In this case you may want to try this query hint.

14 thoughts on “FORCE ORDER Query Hint”

  1. I have a query that without FORCE ORDER takes 5 minutes and 36 seconds in SQL Server Management Studio. With FORCE ORDER it finishes in 0 seconds (probably less than 0.5 seconds).

    Reply
  2. I ran into a case today where I had duplicated some data to create a new test organization. The query joined 3 tables. When it pointed to the data that had been there quite a while it worked. When it pointed to the new org it failed because it was running the 3rd join first and that did not correctly constrain one of the join criteria that was derived from the 2nd join.

    No idea why the query plan was different when all the data was the same except the orgid. I could toggle back and forth with absolute consistency for it to work or fail.

    Using OPTION (FORCE ORDER) made all cases work every time.

    Reply
  3. I found the opposite. When the given query had OPTION(FORCE ORDER), it took 9 seconds to complete it no matter what optimizations I did. When I removed it, the usage dropped down to <0.5 seconds. Doesn't sound like much but if this procedure runs 50 times in a row, it shows up fairly easily.

    Reply
    • Yes, that’s the typical way that it works. SQL Server does a very good job of picking the order of the joins. I don’t think I’ve ever had to use this. I would think that as long as you perform regular maintenance (rebuilding index and stats) to your tables, that SQL Server would have no problem finding the correct joins.

      Reply
  4. Query that is taking more than 1 minutes 19 seconds is finishing in under 2 seconds with this Query Hint.

    Do anyone have an explanation in what scenario this could happen as I am not able to provide explanation on how this could happen in the first place?

    Reply
    • Have you taken into account the SQL Cache as SQL Server will always look in the cache 1st before going to the raw data?
      IE 1st run may be 2 minutes
      2nd run may be 8 seconds

      To be a true test you may need to either clear your cache (NOT on a prod machine) or identify the relevant plans and delete them 1st

      Reply
  5. This can happen because depending on changes to statistics the query optimiser might decide to drop the existing query plan and create a new one that it thinks is best based on the current stats etc.

    Reply
  6. You may want to rebuild your indexes in the cases where you see a huge difference in running your query with and without this hint. The query optimizer is most likely basing its decisions on the wrong information about your indexes.

    Reply
  7. I have an 11 table join returning about 80 rows causing some issues, and even with “update statistics with fullscan, all” done on all tables the query takes about 15 seconds without force order and recompile hints, and about 1 second with force order and recompile hints. Only one of the hints will not do the trick, I need both “option (recompile, force order)” for it to be quick. I guess that even with my specified join order, sql server has some preconceived notion about what kind of index lookup etc it will do, and the recompile hint is making sql server reconsider this. I would have thought that my “update statistics with fullscan, all” would take care of previous notions about the stats, but it seems like it doesn’t completely.

    Reply
  8. Is there a way to impose a different order using a plan guide? Say, original query (which I can not change) is “FROM a, b, c”. And I want it to be; FROM c, a, b. Thanks in advanced.

    Reply
  9. Hi, Sqlserver optimizer uses the statistics to generate the “BEST” plan..
    if the underlying statistics are bad then query plan also will be bad…

    check the Plan along with the number of Executions + Indexes used.
    Recollect the statistics ..

    Issue should be sorted…

    Reply
  10. One thing we have to remember is that for any RDBMS, a table is a table unless it understands the link between them (master->detail) via foreign key constraint/indexes and also more about data via stats on tables and columns. In many cases, we don’t have the bandwidth to collect stats to deep levels.
    So yes, if you know the flow and in some cases with BI tools ( MicroStrategy etc) where you define these parent->child relationships, they create the SQL which are more join aware. I have set this parameter at the grid report level in MicroStrategy and it works great.

    Reply

Leave a Reply to Prabu Krishnan Cancel reply

CommentLuv badge