Home > SQL Server > SQL SERVER – SET FORCEPLAN – SET Statement – JOIN HINTS

SQL SERVER – SET FORCEPLAN – SET Statement – JOIN HINTS


SET FORCEPLAN ON – This set statement tell the SQL Query Optimizer to process the join tables as given in the user specified order and use Nested Loop Join. Unless user specified an explicit  query or join hint and the query optimizer does not require to use other join to construct the actual plan for execution.

SET FORCEPLAN OFF – This tell the SQL Query Optimizer to construct the optimized plan on it own.

Note: SET FORCEPLAN and Hits are used to override the logic used by the query optimizer. So, avoid using hints or Forceplan unless it is necessary. Because SQL Server query optimizer typically construct and select the best execution plan for a query.

Usage

SET FORCEPLAN ON

or

SET FORCEPLAN OFF

Example

I have 3 tables (tblEmployee, tblJobTitle and tblDepartment) in my database. I am going to generate actual query execution plan using the SQL Query given below with FORCEPLAN ON or OFF.

When SET FORCEPLAN OFF,  SQL Server query optimizer uses its own plan and we can clearly see in the execution plan that the order of the join table is different and it uses hash match (inner join) for table join.

pic0

When SET FORCEPLAN is ON, SQL Server query optimizer uses the join table in the order that use specified and it uses Nested Loops (inner join) to join tables.

pic1

I hope you enjoy reading this post. Please write you comment and question in the comment section. Happy coding.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: