Execution Plans - Execution plan in MS SQL Server

The execution plan is created using the Query Optimizer with the help of statistics, the Algebrizer / process tree . That's the Query Optimizer result and shows how your work is done.

There are 2 types of plans that are Estimated and Actual .

1. The estimated plan shows the most optimal view.
2. The actual plan shows what the query is and how it performs.

The execution plan is saved on the Plan Cache for reuse. Each plan is saved once unless the optimizer decides to perform parallel execution of the query.

There are 3 execution plan formats in SQL Server - Graphical Plan , Text Plan and XML Plan (Marked language format).

SHOWPLAN is required (Permisson) required if users want to see the execution plan.

Example 1

Below is the process to see the estimated execution plan.

Step 1 - Connect to the SQL Server installation. In this case is TESTINSTANCE as shown in the picture below.

Execution Plans - Execution plan in MS SQL Server Picture 1
Open the SQL Server installation

Step 2 - Select the database name (in this case TestDB) then click New Query and write the query below.

 Select * from StudentTable 

Execution Plans - Execution plan in MS SQL Server Picture 2
Write a new query

Step 3 - Click the red highlighted icon in the image above to open the estimated execution plan as shown below.

Execution Plans - Execution plan in MS SQL Server Picture 3
Estimated execution plan

Step 4 - Place the mouse pointer on the Table Scan (the second icon in the red frame of the image above) to see the detailed estimated execution plan. The screen below will appear.

Execution Plans - Execution plan in MS SQL Server Picture 4
Detailed plan estimates

Example 2

This is the process to see the actual execution plan

Step 1 and Step 2 as in example 1.

Execution Plans - Execution plan in MS SQL Server Picture 5
Create a new query to see the actual execution plan

Step 3 - Click the red circled icon in the image above to open the actual execution plan as shown below.

Execution Plans - Execution plan in MS SQL Server Picture 6
Actual execution plan in SQL Server

Step 4 - Place your mouse pointer on the Table Scan (the second icon in the red frame of the image above) to see a detailed implementation plan. The screen below will appear.

Execution Plans - Execution plan in MS SQL Server Picture 7
Detailed execution plan

Step 5 - Finally click Results in the top left corner of the screen above.

Previous article: Reporting service in MS SQL Server

Next article: Integrated services in MS SQL Server