SA0239 : Setting the FORCEPLAN option to ON is not recommended

Introduction

The FORCEPLAN option can limit SQL Server’s ability to optimize query execution plans effectively.

Description

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:

-- Example of problematic use of FORCEPLAN
SET FORCEPLAN ON;
SELECT * FROM Orders
JOIN 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.

How to fix

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:

-- Without FORCEPLAN
SELECT *
FROM Customers
JOIN Orders ON Customers.ID = Orders.CustomerID;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 hours per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

SET FORCEPLAN ON;
SET FORCEPLAN OFF;

Analysis Results

	Message	Line	Column
1	SA0239 : Setting the FORCEPLAN option to ON is not recommended.	1	0

Analysis Rules