SET FORCEPLAN

Fifth post of SET Statement series.

Let us execute the following query.

USE AdventureWorks2012
GO

SELECT SOH.SalesOrderID,SOH.OrderDate, P.ProductID, P.Name,SOD.OrderQty,SOD.LineTotal FROM Sales.SalesOrderDetail SOD 
         INNER JOIN Sales.SalesOrderHeader SOH ON SOH.SalesOrderID = SOH.SalesOrderID
         INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID
         WHERE SOH.OrderDate = '2005-07-01'
         AND P.ProductId = 712

Following will be the query execution plan.

You can see that query plan does not depends on the order of the tables in the query. for example, though you have SalesOrderDetail table in the query, query plan first pick the products table since that query plan will be the best.

If you set the above set option to ON, then the query plan will change. as shown below.

So when the SET FORCEPLAN is set to ON,SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query. In addition, setting FORCEPLAN to ON forces the use of a nested loop join unless other types of joins are required to construct a plan for the query.

However,when using this option, you need to make sure that after setting it to ON, your query performs better.

Previous posts of this series,

SET IMPLICIT_TRANSACTIONS ON

SET NOCOUNT

SET DEADLOCK_PRIORITY

SET CONCAT_NULL_YIELDS_NULL