DF005: The SET FORCEPLAN statement is used.

Last modified: December 25, 2024

The topic describes the DF005 T-SQL code analysis rule.

Category

PERFORMANCE

Message

The SET FORCEPLAN statement is used.

Description

It is not recommended to use the SET FORCEPLAN statements because of the possibility of performance loss.

Additional information

When FORCEPLAN is activated, the SQL Server query optimizer arranges joins in the same sequence as the tables listed in the FROM clause of a query. Moreover, activating FORCEPLAN forces the use of a nested loop join unless other types of joins are mandatory for constructing a query plan, or they are specified with join hints or query hints.

SET FORCEPLAN overrides the logic used by the optimizer for processing a Transact-SQL SELECT statement. Despite this setting, the data returned by the SELECT statement remains consistent. The sole difference lies in how SQL Server manages the tables to fulfill the query.

It is important to note that SET FORCEPLAN takes effect during execution or runtime, not during parsing.

Noncompliant code example

SET FORCEPLAN ON;
GO

Compliant solution

/*
remove usage of SET FORCEPLAN
*/