An execution plan is the query optimizer’s output after trying a number of possible ways to execute a T-SQL request in a most effecient way. It tells us how actually a query is going to be executed or was executed by SQL server. It’s a series of steps or physical and logical operations that need to be performed to produce the required the result or satisfy the T-SQL request.
The Query optimizer generates the execution plan from the Query Processor Tree or Query Tree produced by the Query Parser after parsing and algibrayzing the T-SQL request by using the statistics it has about the data.
There are two distinct types of execution plans,
•Estimated Execution Plan
•Actual Execution Plan
Estimated Execution Plan : This represents the optimzer’s view of the plan, This a logical plan, generated by the optimizer with out actually executing the T-SQL request.
Actual Execution Plan : This represents the out puts from the actual query execution. It tells what actually happend the T-SQL request exeuted.
Execution Plan Formats
SQL Server execution plan can be viewed in three different formats
•Graphical plan
•Text Plan
•XML Plan
Graphical Plan
Quick and easy to read graphical form of the plan ,uses icons. both estimated and actual plans can be viewed in Graphical format.
Graphical Estimated Execution plan can be viewed by using the short cut key CTRL+L or from the Query options menu as shown in the below screen shot or by clicking the Display Estimated Execution plan tool bar icon.
Graphical Actual Execution plan can be viewed by using the short cut key CTRL+M or from the Query options menu as shown in the below screen shot or by clicking the Include Actual Execution plan tool bar icon.
Textual Plan
Bit harder to read , displays the plan textually
Estimated Execution plan can be viewed in text format with the below set options
1. SET SHOWPLAN_ALL ON
2. SET SHOWPLAN_TEXT ON
Actual Execution plan can be viewed in text format with the below set options
1. SET STATISTICS PROFILE ON
XML Plan
Displays the complete set of data of a plan in XML format.
Estimated Exection plan can be viewed in XML format with the below set options
1. SET SHOWPLAN_XML ON
Actual Exection plan can be viewed in XML format with the below set options
1. SET STATISTICS XML ON
Data Skills 