Execution plans are graphical representations of operations that SQL Server performs to return the requested data. They can be nested to show complex operations that require data sorting, loading, or data scanning, and each operation is characterized by an operator.
There are essentially two types of execution plans that SQL Server generates – Estimated Execution Plan, and Actual Execution Plan.
- Estimated Execution Plan: This is created without running the query and is based on statistics available to the optimizer at the time of compilation.
- Actual Execution Plan: This is created once the query has run. It uses actual row counts, execution counts and elapsed time for each operator.
Evaluation of these plans helps in identifying performance bottlenecks, such as high costs, or inefficient operations.
How to Display Execution Plans
To view the execution plans, follow these steps:
- In SQL Server Management Studio, load your query in the query window.
- Click on either ‘Display Estimated Execution Plan’ which displays the query execution without running the query; or ‘Include Actual Execution Plan’, to display the execution plan after the query is run.
The execution plan forms a graphical layout using icons called operators. Each operator represents an action such as data retrieval, sorting, or aggregation. The arrows between operators show the data flow.
Reading Execution Plans
Execution plans should be read right to left and top to bottom, in that the query starts with the right-most operator and works its way to the left. The top branch of the plan is usually where most I/O occurs, and is accordingly the area organization should focus upon, in order to gain potential performance improvements.
Execution cost, in terms of I/O, CPU usage, and overall query cost is also included in an execution plan. This is expressed as a percentage of the total cost of the query.
Identifying High-Cost Operations
Certain operations are more cost-intensive than others. Here are a few high-cost operations that you should watch out for:
- Table Scan: Indicates no indexes were used and instead, a table scan was implemented. This can lead to slower performance for large data sets.
- Key Lookup: The SQL Server needs to retrieve additional data from the base table after performing an index seek. It can be costly if performed multiple times.
- Sort: The sorting data, especially in large quantities, can be resource intensive.
Conclusion
In conclusion, understanding and properly interpreting execution plans forms a significant part of administering Microsoft Azure SQL solutions. By using these plans, one can easily pinpoint performance bottlenecks, high-cost operations and areas where indexes may need to be adjusted or added. This understanding would greatly benefit any aspirants preparing for the DP-300 Exam.
Practice Test
True or False: The execution plan in SQL Server is a tree representation of the operations the SQL Server query optimizer uses to access data.
- True
- False
Answer: True
Explanation: The execution plan demonstrates the decisions made by SQL Server’s query optimizer about how your SQL code will be executed or processed.
Multiple Select: Which of the following are details that can be found in the execution plan of a SQL query?
- a) Cost of the operation
- b) Order in which tables are accessed
- c) Data retrieval method
- d) List of databases
Answer: a), b), c)
Explanation: The execution plans provide detailed information about cost of the operation, the order in which tables are accessed, and the method used for data retrieval. They do not include a list of databases.
True or False: The purpose of reviewing SQL execution plans is to optimize the performance of your queries.
- True
- False
Answer: True
Explanation: By examining the execution plan, you can gain insight into how your SQL queries can be adjusted or rewritten for better performance.
Single Select: Which of the following types of execution plans in SQL server is saved for reuse and available in plan cache?
- a) Estimated Execution Plan
- b) Actual Execution Plan
- c) Cached Execution Plan
Answer: c) Cached Execution Plan
Explanation: A Cached Execution Plan is specifically designed to be saved in the plan cache for potential reuse, aiding in overall query execution efficiency.
True or False: The Estimated Execution Plan in SQL Server actually runs the query.
- True
- False
Answer: False
Explanation: The Estimated Execution Plan does not run the query. It simply displays the method or path that SQL Server would take to execute that query.
Single Select: Which of these operations is the most costly in a SQL Server execution plan?
- a) Index seek
- b) Table scan
- c) Index scan
- d) Nested loops
Answer: b) Table scan
Explanation: Table scans are often considered more expensive because they involve examining every row in the table to find the ones that meet the query criteria.
True or False: Parallelism in SQL Server execution plans implies that a part of the plan is being executed by multiple processors.
- True
- False
Answer: True
Explanation: With Parallelism, a single query can be divided amongst multiple processors, thus reducing the total execution time.
Multiple Select: The concept of operator cost in SQL Server execution plans can refer to which of the following:
- a) I/O cost
- b) CPU cost
- c) Memory cost
- d) Network cost
Answer: a), b), c)
Explanation: In execution plans, operator cost can refer to I/O cost, CPU cost and Memory cost but not network cost.
True or False: All changes to a query, including the order of tables and use of indexes, will affect the SQL Server Execution Plan.
- True
- False
Answer: True
Explanation: Execution plans reflect the strategy chosen by the SQL Server query optimizer. Any modification to the query, including table order or indexing, may influence this strategy.
Single Select: Which of the following SQL Server commands can be used to remove all elements from the plan cache?
- a) DBCC FREEPROCCACHE
- b) DBCC FLUSHPLAN
- c) DBCC REMOVEPLAN
- d) DBCC DELETECACHE
Answer: a) DBCC FREEPROCCACHE
Explanation: The command DBCC FREEPROCCACHE removes all elements from the plan cache, clearing the way for new plans to be created.
Interview Questions
What is the purpose of a SQL Server execution plan?
A SQL Server execution plan is a roadmap that SQL Server actionable items generate in order to retrieve data. It displays the detail about how SQL Server’s Query Optimizer would access the data.
What are the two types of execution plans in Azure SQL?
The two types of execution plans in Azure SQL are Actual Execution Plan and Estimated Execution Plan.
What is an Actual Execution Plan in Azure SQL?
An Actual Execution Plan in Azure SQL displays the way SQL Server executed the query. The plan includes information about actual row counts, the actual number of executions, and the elapsed time for each operator in the query.
What is an Estimated Execution Plan in Azure SQL?
An Estimated Execution Plan in Azure SQL communicates the strategy SQL Server’s Query Optimizer plans to use to execute the query, with estimated row counts, estimated operator cost etc., based on statistics.
What is a Seek Predicate in an Azure SQL execution plan?
The Seek Predicate in an Azure SQL execution plan is a predicate used by the Query Optimizer to identify the rows that will satisfy the query.
How can the Azure SQL execution plan help in performance tuning?
The Azure SQL execution plan identifies costly operations, indexing strategy, data flow, join operations, the volume of data retrieved etc., which can be optimized or corrected for performance improvement.
What does a thicker line in an execution plan signify in Azure SQL?
In an execution plan, a thicker line signifies more data rows. The thickness of the line correlates with the number of rows moved between operations.
Can you change the execution plan directly in Azure SQL?
No, you cannot directly change the execution plan in Azure SQL. However, you can influence it by changing the SQL Server configuration, rewriting your query, creating or dropping indexes, or updating statistics.
What does ‘cost’ in an execution plan represent in Azure SQL?
In Azure SQL, cost in an execution plan represents an estimation of the resources needed to run each operation. It can be used to identify the most resource-intensive parts of the query.
What is the role of the Query Optimizer in Azure SQL execution plans?
The Query Optimizer in Azure SQL is responsible for determining the fastest and least resource-intensive way to execute a given SQL statement by estimating the cost of different execution strategies and choosing the plan with the lowest cost.
How can you view the execution plan in Azure SQL?
You can view the execution plan in Azure SQL by clicking the ‘Include Actual Execution Plan’ button in the SQL Query window, or by using the SET SHOWPLAN_ALL or SET SHOWPLAN_XML commands.
What does Parallelism mean in an SQL execution plan?
Parallelism in an SQL execution plan means that the query was executed in parallel, using multiple threads, to make processing faster.
What does the ‘Table Scan’ operation signify in an Azure SQL execution plan?
‘Table Scan’ operation in an Azure SQL execution plan signifies that the SQL Server had to examine every row in the table to find the ones that satisfy the predicate.
What is parameter sniffing in the context of execution plans in SQL?
Parameter sniffing is the process where SQL Server creates an optimized execution plan for a stored procedure by using the values of the parameters supplied the first time the procedure is executed.
How to force an Azure SQL query to use a specific execution plan?
To force a specific plan for a query in Azure SQL, you can use Query Store’s plan forcing feature. It allows you to manually select an execution plan for SQL Server to use when running specific query.
extutorials.com