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 .

The estimated plan shows the most optimal view.
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

Samuel Daniel

Update 25 May 2019

You should read it

May be interested

Detects code execution vulnerabilities in WinRAR, noting more than 100 infringement cases
a code execution vulnerability in winrar has been exploited more than 100 times separately by hackers in the first week since it was revealed, and it is expected that this number will continue to increase in the future.
How to choose Power Plan in Windows 10
a power plan is a collection of hardware and system settings that manage how computers use energy. this tutorial will show you how to choose a power plan to use by default in windows 10.
Hundreds of thousands of IoT devices are likely to be attacked by vulnerabilities on the server
on christmas day, a vulnerability affecting web servers was embedded with hundreds of thousands of iot devices, namely goahead, a web server created by embedthis software.
Additions for Exchange Server 2007 - Part 1: Introduction steps
in this series, i will show you how to add exchange server 2007 sp1 (beta), installed on windows server 2008 (also beta). we talked about the steps needed to add the underlying operating system by only installing a minimum number of server roles and services. in the second part, we plan to do it
How to Adjust Power Settings in a Windows Vista Laptop
in windows vista, laptop owners can now choose and even make power plans to their preference. depending on whether you have to make the most of the laptop or you want a short session to watch videos or photos, choosing a power plan for...
Please download the first Windows Server Insider Build from Microsoft
recently, microsoft unveiled the first results in a plan to provide windows server updates every two years by revealing the first version of this operating system insider. please read the article below for more details!
Microsoft continues to 'delay' the plan to launch a new version of Exchange Server for another 4 years
currently, with a focus on security, microsoft has just announced that the next version of exchange server will be released in the second half of 2025.
The difference between web server and app server
you have probably seen that the terms web server and app server are often used interchangeably as if they are related to the same thing and also facilitate the website to function properly. but in reality, they are not the same.
Learn about SQL Injection and how to prevent it
sql injection is one of the types of web hacking by injecting sql query / command codes into input before transferring to the web application, you can login without a username and password, remote execution (remote execution), dump data and retrieve the root of sql server.
Multithread (Multithread) in C #
[thread in c #] a thread is defined as an execution path (execution path) of a program. each thread defines a single control line. if your application includes complex and time-consuming activities, it is often useful to set up execution paths or thread, with each thread performing a specific task.