Click or drag to resize

SA0239 : Setting the FORCEPLAN option to ON is not recommended

The topic describes the SA0239 analysis rule.

Message

Setting the FORCEPLAN option to ON is not recommended

Description

This rule checks T-SQL code for SET statements, which change the FORCEPLAN option to ON.

The FORCEPLAN ON setting, changes the way SQL Server query optimizer processes table joins and overrides the logic that the SQL Server query optimizer uses to produce execution plans.

When FORCEPLAN is set to ON, the SQL Server query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.

It is not recommended to use SET FORCEPLAN ON. Even the FORCEPLAN ON option produced a good execution plan at the time the query was written or it fixed a performance problem, the option will prevent SQL Server to find a better execution plan when the underlying data changes in the future.

Review the query and try to fix its performance using more conventional methods.

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.

Categories

Performance Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
1SET FORCEPLAN ON;
2SET FORCEPLAN OFF;

Analysis Results

 MessageLineColumn
1SA0239 : Setting the FORCEPLAN option to ON is not recommended.10
See Also

Other Resources