SQL SERVER – Interesting Observation – Query Hint – FORCE ORDER

SQL Server never stops to amaze me. As regular readers of this blog already know that besides conducting corporate training, I work on large-scale projects on query optimizations and server tuning projects. In one of the recent projects, I have noticed that a Junior Database Developer used the query hint Force Order; when I asked for details, I found out that the basic concept was not properly understood by him.

Today, let us try to understand its working and the effect of this hint. Further, we will see the extent of difference in performance created by this one query hint. I also have one interesting question for all of you as well; I will give the answer in one of my later posts.

USE AdventureWorks
GO
SELECT COUNT(*) CountEmployee -- 290 Rows
FROM HumanResources.Employee
GO
SELECT COUNT(*) CountEmployeeAddress -- 290 Rows
FROM HumanResources.EmployeeAddress
GO
SELECT COUNT(*) CountEmployeeDepartmentHistory -- 296 Rows
FROM HumanResources.EmployeeDepartmentHistory
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON edh.EmployeeID = e.EmployeeID
GO
SELECT *
FROM HumanResources.Employee e
INNER JOIN HumanResources.EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID
INNER JOIN HumanResources.EmployeeDepartmentHistory edh ON edh.EmployeeID = e.EmployeeID
OPTION (FORCE ORDER)
GO


In above example, I have used three tables and their row count is listed as well. Employee and EmployeeAddress – both have same number rows, and EmployeeDepartmentHistory table has around 6 rows more than the other two tables. Now let us run the query without using OPTION (FORCE ORDER) and run it along with the query hint and check the execution plan. You will find a difference in the query cost.

We all accept that the table with least number of rows should be listed as the base table, and the same is done here. We have two such tables with the least number of rows, which are listed as base tables. Now before we further explain this, let us see the execution plan for the same.

SQL SERVER - Interesting Observation - Query Hint - FORCE ORDER forceorder

It is very clear from the above execution plan that when I order is forced the query cost goes high. This proves that the SQL Server has already made a good decision with regard to the optimized query plan. When plan is forced in the case of the joins more than 2 table the performance matters. Let us see the execution order of the table in both the cases.

SQL SERVER - Interesting Observation - Query Hint - FORCE ORDER forceorder1

Now, from the example, it is very clear when you force order the query, it evaluates the table Employee first and when it is not forced, it evaluates EmployeeAddress first. Even though both the tables have same number of rows, the query optimizer processes them differently and uses different types of join logic. When the order is not forced, it uses hash join; further, in case of forced order, it uses nested loop – this creates a significant difference in the query cost.

The conclusion of this whole exercise is very simple.

  • SQL Server Query Execution Engine is pretty smart to decide the best execution plan with least query cost for any query.
  • Order of the tables in any query can make a significant impact on the query.

Now the question for you: We have seen that using query hint of OPTION (FORCE ORDER) reduces the performance; give an example wherein we can use this hint to improve the performance?

Please leave your comment here. I will publish the answer to this question with due credit and with my own example in a later post.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Choose Right Edition of SQL Server Express for Your Application
Next Post
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup

Related Posts

27 Comments. Leave new

  • In scenarios where you know that a JOIN to an inline select statement will reduce the size of further huge table joins, FORCE ORDER is very useful. Without the hint, SQL server will join big tables first thus producing a significantly bigger amount of logical reads before the inner select join is able to reduce the whole result set down.

    Reply
  • I got this error:

    Msg 8624, Level 16, State 116, Procedure usp_xxxxxxxx, Line 265
    Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

    and using the FORCE ORDER option on two queries within the procedure fixed it for me. The odd thing was that it was working a couple of days before without the option.

    Reply
  • I have a query that without this option does an index scan and retrieves 127 million records, when i use this hint it lowers that to 210 thousand in 1/10th the time.

    Reply
  • Great and very informative article.

    Reply
  • Thanks!

    Reply
  • David Rueter
    May 25, 2020 10:10 pm

    In our ISV software I frequently come across queries where OPTION (FORCE ORDER) make a tremendous positive improvement. In the example below, without the option, elapsed time is 59 seconds. With the option, the elapsed time drops to 22 ms.

    The cardinality estimator is part of the reason.

    This simple query highlights the issue. The execution times are on SQL 2019 Standard, but are similar to SQL 2016. (SQL 2019 gives us the ability to specify the specific optimizer version.)

    If you are interested, I could send additional information and query plans.

    SELECT DISTINCT
    TOP 3000
    ref.ProjectName AS Value
    FROM
    opsstream.vwQXD_SalesOrders qxd
    JOIN opsstream.vwQXD_Projects ref ON qxd.Project=ref.QuestID

    –32 rows returned
    –{no hint} CPU time = 58984 ms, elapsed time = 58975 ms.
    –OPTION (FORCE ORDER) –CPU time = 15 ms, elapsed time = 22 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_100’)) –CPU time = 21250 ms, elapsed time = 21246 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110’)) –CPU time = 20672 ms, elapsed time = 20671 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120’)) –CPU time = 58047 ms, elapsed time = 58092 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_130’)) –CPU time = 58610 ms, elapsed time = 58658 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_140’)) –CPU time = 58062 ms, elapsed time = 58093 ms.
    –OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150’)) –CPU time = 59562 ms, elapsed time = 59670 ms.

    SELECT * FROM opsstream.vwQXD_SalesOrders
    –7892 rows returned
    –{no hint} CPU time = 78 ms, elapsed time = 941 ms.
    –OPTION (FORCE ORDER) –CPU time = 47 ms, elapsed time = 110 ms.

    SELECT * FROM opsstream.vwQXD_Projects
    –37 rows returned
    –{no hint} CPU time = 15 ms, elapsed time = 12 ms.
    –OPTION (FORCE ORDER) –CPU time = 0 ms, elapsed time = 2 ms.

    Reply

Leave a Reply

Menu