FORCE ORDER Query Hint

Facebooktwittergoogle_plusredditpinterestlinkedinmail

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

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

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.

9 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).

  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.

  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.

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

  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?

  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.

  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.

Leave a Comment

CommentLuv badge