What is Execution Plan in SQL Server

What is a execution plan in SQL?

Execution plan is a road map that determines how the query will work.

Will the query use the index or will it scan the table completely?

What kind of join will it do? Nested loop, merge join, hash join etc.

And it determines the query cost ratio for each part of the query.

For example, an index seek process may have a query cost of 1%

When a TSQL query that you send to SQL Server runs slowly, the first thing you need to look is the query execution plan.

Execution Plan Types

There are two types of execution plan as estimated execution plan and actual execution plan.

Estimated execution plan vs Actual execution plan

Estimated execution plan predicts the execution plan to use if it is executed without executing the query.

The actual execution plan is the actual execution plan used after the query is executed.

The best way to understand the execution plan is to go through an example.

Display estimated execution plan vs include actual execution plan

Open SQL Server Management Studio and when click on the Person.Adress Table in the AdventureWorks2014 database. Then click new query and write the following query.

Then click on the marked place in the picture below.

It predict that, if query will be executed, it will perform a Clustered Index Scan and the query cost was 100%.

Let’s click on the arrow marked below and execute the query.

As you can see, the query works and the execution plan is created.

Click on the underlined Execution Plan tab in the image above to see the actual execution plan.

In our query, as you see below, the estimated and actual execution plan is same.

The following articles may interest you.

“Index Concept and Performance Effect on SQL Server“,

“Statistical Concept and Performance Effect on SQL Server“,

“Join Types in SQL Server“,

“Spool Concept in Execution Plan (Eager Spool, Lazy Spool)“,

“Join Types in SQL Server Execution Plan”