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.

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.

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)