SQL Server - Execution Plans

Introduction 

Execution plan will be generated by Query Optimizer with the help of statistics and Algebrizer/ processor tree. It is the result of Query Optimizer and tells how to do/ perform your work/ requirement.

There are two different execution plans - Estimated and Actual.

• Estimated execution plan indicates Optimizer view.
• Actual execution plan indicates what executed the query and how was it done?

Execution plans are stored in the memory called Plan cache and hence can be reused. Each plan is stored once, unless optimizer decides parallelism for the execution of the query.

There are three different formats of the execution plans available in SQL Server - Graphical plans, Text plans and XML plans.

SHOWPLAN is the permission, which is required for the user, who wants to see the execution plan.

Example 1

Following is the procedure to view the estimated execution plan.

Step 1

Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name, as shown below.

 

Step 2

Click New Query option on the screen given above and write the query given below. Before writing the query, select the database name. In this case, 'TestDB' is a database name.

Select * from StudentTable

 

Step 3

Click the symbol, which is highlighted in Red color box on the screen given above to display the estimated execution plan, as shown below.

 

Step 4

Place the mouse on table scan, which is the second symbol shown above Red color box in the screen given above to display the estimated execution plan in detail. The screenshot given below appears.

 

Example 2

Following is the procedure to view the actual execution plan.

Step 1

Connect to SQL Server instance. In this case, 'TESTINSTANCE' is the instance name.

 

Step 2

Click New Query option seen on the screen given above and write the query given below. Before writing the query, select the database name. In this case, 'TestDB' is the database name.

Select * from StudentTable

 

Step 3

Click the symbol, which is highlighted in Red color box on the screen given above and then execute the query to display the actual execution plan along with the query result, as shown below.

 

Step 4

Place the mouse on the table scan, which is the second symbol above the Red color box on the screen to display the actual execution plan in detail. The screenshot given below appears.

 

Step 5

Click Results, which is on the top left corner on the screen given above to get the screen given below.