Execution plans

1. Execution plans

In this final lesson, we’ll introduce SQL Execution Plans and some basic examples of what they can tell us.

2. Optimization phase

When a query is submitted to a database engine, after passing all the syntax, table, and data checks, it is passed to an Optimization Phase,

3. Optimization phase

which evaluates several execution plans to determine which one will return results at the lowest cost. Among other parameters costs include processor usage, memory usage, and data pages read.

4. Optimization phase

Once the best execution plan is selected, it is passed to the Execution Engine to process the query.

5. Information from execution plans

Reading execution plans is useful. Among other important information, they can tell us: if indexes were used, the types of joins used, if and where filter conditions, sorting and aggregations occurred, and their relative costs. Any issue with a query should be immediately apparent in an execution plan which makes it an excellent tool for troubleshooting query performance.

6. Estimated execution plan in SSMS

This image shows how we can view an estimated execution plan in SSMS. We highlight the query

7. Estimated execution plan in SSMS

and select the Display Estimated Execution Plan icon from the menu toolbar.

8. Viewing executions plans in SSMS

A graphical execution plan for the query is displayed in the Execution plan tab.

9. Operator statistics

Each icon in an execution plan represents an operator that is used to perform a specific task. Hovering over an operator provides detailed statistics of the task used in the query.

10. Reading execution plans

Execution plans are read from right to left, as indicated by the direction of the arrows between operators. The width of each arrow reflects how much data was passed from one operator to the next.

11. Index example

Let’s take a look at some of the information execution plans can provide us. An execution plan can tell us if an index is accessed while processing the query. In this example, we have two versions of the Customers table from the Customers Orders database. CustomersCI has a clustered index on the CustomerID column, and Customers does not. We’ll query both tables for a CustomerID of PARIS. The execution plan for the query on the Customers table shows a Table Scan operator. This operator indicates the entire table was scanned for the filter condition. In contrast, the execution plan for the query on the CustomersCI table shows a Clustered Index Seek operator. This operator indicates an index was used to go directly to the data pages that meet the filter condition.

12. Sort operator example

In an earlier chapter, we said that we could append queries together using UNION and UNION ALL. We noted that UNION removes duplicates and UNION ALL returns everything, including duplicates. To check for and remove duplicates requires a sort operation. We can see this by comparing two queries with UNION and UNION ALL. The execution plan with UNION uses a Sort operator internally to check and remove duplicates and UNION ALL does not. Be aware of the Sort operator in execution plans because it can be costly on a query. Examining an execution plan could tell us particular syntax or operators that are unnecessary.

13. The same execution plan?

Often, queries using different methods to return the same result will produce the same execution plan. In an earlier chapter, we compared the IN and EXISTS operators. In this example we have two simple queries: one using IN and the other EXISTS. The optimizer has decided to use the same execution plan for both. This is not always the case, and it does not mean that we should let the optimizer do all the query performance tuning for us. If time is important, we should always ask ourselves if there is a better or faster way to return the results we require.

14. Let's practice!

Let's practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.