SA0239 : Setting the FORCEPLAN option to ON is not recommended

Setting the FORCEPLAN option to ON is not recommended

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.

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.

1SET FORCEPLAN ON;
2SET FORCEPLAN OFF;

Analysis Results

Other Resources