SA0239 : Setting the FORCEPLAN option to ON is not recommended

Setting FORCEPLAN to ON in T-SQL statements restricts the query optimizer’s capability to rearrange the order of joins for efficient execution. This restriction can degrade performance because SQL Server is forced to process joins in the order they appear in the FROM clause, rather than using the optimal order determined by the optimizer.

For example:

1-- Example of problematic use of FORCEPLAN
2SET FORCEPLAN ON;
3SELECT * FROM Orders
4JOIN Customers ON Orders.CustomerID = Customers.ID;

In the above example, enforcing the join order might work well initially but can hinder future optimizations. As data volumes or distributions change, SQL Server might benefit from a different join strategy, which FORCEPLAN prevents.

• Using FORCEPLAN ON may resolve short-term performance issues but will lock in an execution plan that may not adapt well to data changes.

• This setting bypasses the optimizer’s heuristics, potentially leading to suboptimal query performance as workloads evolve.

Resolve issues related to the use of FORCEPLAN in T-SQL code to optimize query execution by allowing SQL Server to determine the optimal join order.

Follow these steps to address the issue:

1. Identify the query using the SET FORCEPLAN ON statement that may lead to suboptimal performance.

2. Remove the SET FORCEPLAN ON statement from the query to allow SQL Server to optimize join order based on current data statistics and distribution.

3. Analyze the query’s execution plan using SQL Server Management Studio (SSMS) to verify the optimizer’s join order and overall plan efficiency.

4. If necessary, apply other indexing strategies or query refactoring to improve query performance without forcing join order.

For example:

1-- Without FORCEPLAN
2SELECT * 
3FROM Customers
4JOIN Orders ON Customers.ID = Orders.CustomerID;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Performance Rules, Bugs

There is no additional info for this rule.

Other Resources