An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

Query Analyzer has an option, called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on, it will display a query execution plan in a separate window when the query is run again.


Q1: What is a SQL Server Execution Plan?

SQL Server Execution Plan is a binary representation of the steps that are followed by the SQL Server Engine to execute the query. It also is known as the most efficient roadmap for the query

For more information, check SQL Server Execution Plans overview

Q2: Which component of the SQL Server Engine is responsible for generating an Execution Plan for the submitted query?

A: The SQL Server Query Optimizer is responsible for creating the most efficient plan to execute the provided query

For more information, check SQL Server Execution Plans overview

Q3: Where will the generated Execution Plan be stored?

A: The SQL Server Execution Plan will be stored in the Plan Cache memory storage

For more information, check SQL Server Execution Plans overview

Q4: What is the goal behind storing the Execution Plan for the query in the Plan Cache?

A: The process of generating the most optimal execution plan is an expensive process. Instead of creating a new Execution Plan each time a new query is submitted, the SQL Server Query Optimizer will search in the plan cache storage for an existing Execution Plan for the submitted query and use it. If there is no plan that can be used for that query, the Query Optimizer will create a new plan, taking more time to execute that query. The Execution plans reuse mechanism is very helpful when there are stored procedures executed frequently

For more information, check SQL Server Execution Plans overview

Q5: What are the main types of Execution Plans that you can generate for a T-SQL query and what is the difference between these two types?

A: The Estimated Execution Plan. It is the plan that is generated by parsing the submitted query as an estimate of how the query will be executed, without being executed

The Actual Execution Plan, that is generated by executing the submitted query, displaying the actual steps that followed while executing the query

For more information, check SQL Server Execution Plans types

Q6: What are the three Execution Plan formats?

A: Graphical Format, Text Format and XML Format

Q7: How could the Execution Plan help in identifying the missing index for a specific query?

A: Based on the available SQL Server statistics and the workload performed on the SQL Server, the SQL Server Query Optimizer will provide us with a suggested index, that may improve the performance of the submitted query in a calculated percentage. So, it will display that index as a recommendation with the query plan in green

For more information, check How to Analyze SQL Execution Plan Graphical Components

Q8: What is the correct way of reading the Execution Plan?

A: The correct way to read the SQL Execution Plan is to start from the right side of the plan to the left side and from the top to the bottom, and the most left SELECT operator contains the final result of the submitted query

For more information, check How to Analyze SQL Execution Plan Graphical Components

Q9: How could we use the arrow between the Execution Plan operators to read the plan?

A: You can make use of the arrows that are connecting the operators in identifying the direction and the amount of the data passed between the Execution Plan operators. In addition, the arrow is an indication of how much data passed between the operators

For more information, check How to Analyze SQL Execution Plan Graphical Components

Q10: Can the Estimated SQL Execution Plan result be trusted?

A: This depends on the statistics. If it is updated, the results should be the same. You need the Estimated SQL Execution Plan in case the query will take a long time to execute and you need to troubleshoot it

For more information, check How to Analyze SQL Execution Plan Graphical Components

Q11: What is the difference between the RID and the Key Lookup operators?

A: RID is a row locator that includes information about the location of that record such as the database file, the page, the slot numbers that helps to identify the location of the row quickly

The Key Lookup operator is the Clustered equivalent of the RID Lookup operator

For more information, check SQL Server Execution Plan Operators – Part 2

Q12: What is the Aggregate operator in the Execution Plan?

A: The Aggregate Operator is mainly used to calculate the aggregate expressions in the submitted query, by grouping the values of an aggregated column. The aggregate expressions include the MIN, MAX, COUNT, AVG, SUM operations

For more information, check SQL Server Execution Plan Operators – Part 3

Q13: What is the Compute Scalar operator in the Execution Plan?

A: The Compute Scalar operator is used to perform scalar computation operations in order to calculate a new value from the existing row value

For more information, check SQL Server Execution Plan Operators – Part 3

Q14: What is the Concatenation operator in the Execution Plan?

A: The Concatenation operator takes one or more data sets in sequence as inputs and returns all records from all the input data set. A good example of the concatenation operator is the UNION ALL T-SQL statement

For more information, check SQL Server Execution Plan Operators – Part 3

Q15: What is the ASSERT operator in the Execution Plan?

A: The Assert operator will verify whether the inserted values meet the defined CHECK or FOREIGN KEY constraints on the table called by the query

For more information, check SQL Server Execution Plan Operators – Part 3

Q16: What is the Hash Match operator in the Execution Plan?

A: Hashing table is used when the SQL Server engine divides the joined tables in the query into equally sized buckets, using a Hashing Function, so that it can access these data in a quick manner. In this case, the SQL Server Optimizer will use the Hash Match operator to perform that action

For more information, check SQL Server Execution Plan Operators – Part 3

Q17: What is the Lazy Spool operator in the Execution Plan?

A: The SQL Server Lazy Spool is used to build a temporary table on the TempDB and fill it in a lazy manner. In other words, it fills the table by reading and storing the data only when individual rows are required by the parent operator

For more information, check SQL Server Execution Plan Operators – Part 4

Q18: What is the Parallelism operator in the Execution Plan?

A: The parallel plan is used by the SQL Server Engine to execute the expensive queries faster. The SQL Server Engine decides to use a parallel plan to execute the query when the SQL Server is installed on a multi-processor server, the number of threads that are requested are available to be assigned, the value of the Maximum Degree of Parallelism option is not equal to 1 and the cost of the submitted query is larger than the Cost Threshold for Parallelism value. The Parallelism operator is used by the SQL Server Engine to execute the query using a parallel plan

For more information, check SQL Server Execution Plan Operators – Part 4

Q19: How could we take benefits from the SQL Server Execution Plan in tuning the T-SQL queries performance?

A: The SQL Server Execution Plan can be used in identifying the bad performance parts of the query. The first thing to look at is the most expensive operator with the highest cost, compared with the overall query cost. In addition, having a fat arrow, which is followed by a thin one, is an indication of the missing index that forced scanning a large amount of data to retrieve a small number of records

Next, you need to search for the extra operators, as its overhead will degrade the query performance. Also, the Scan operators that read the overall table or index is an indication of a missing index, the existing index is badly used, or the submitted query has no filtering condition. The Execution Plan Warnings messages are a sign of different query performance problems that should be checked

For more information, check Using the SQL Execution Plan for Query Performance Tuning

Q20: What is the SQL Server level option that can be used to enhance the Plan Cache usage performance and minimize the memory pressure when the majority of your system workload are ad-hoc queries?

A: You can enable the Optimize for Ad hoc Workloads option, to store the SQL Execution Plan of the query in the Plan Cache at the second execution of the query

For more information, check Saving your SQL Execution Plan

Q21: How long will the plan be stored in the Plan cache?

A: It is useless to keep the SQL Server Execution Plan in the Plan cache forever. The SQL Server Engine will automatically drop any plan from the Plan Cache whenever more memory is required by the system or when the plan becomes old and not called for a long time. The SQL Server Engine users the Lazy Writer system process to clean these aged plans

For more information, check Saving your SQL Execution Plan

Q22: How could we explicitly clear the Plan cache?

A: Using the DBCC FREEPROCCACHE T-SQL command

Could you please some items which you may see in an execution plan indicating the query is not optimized. 

  1. Index Scan or Table Scan
  2. Hash Joins
  3. Thick arrows (indicating large work tables)
  4. Parallel streams (Parallelism)
  5. Bookmark lookup (or key lookup)