Exploring Query Execution Plans

In the previous chapters, we learned how to construct a T-SQL query, how SQL Server processes a query, and how the query is optimized, which results in an execution plan that can be cached and reused by subsequent query executions. Now that we understand the steps SQL Server follows to produce a plan and execute a query, we can investigate an execution plan to examine the results of this process and begin analyzing how we can improve the performance of our queries.

Think of a query execution plan as a map that provides information on the physical operators that implement the logical operations discussed in Chapter 1, Anatomy of a Query, as well as the execution context for that query, which provides information about the system on which the query was executed. Each physical operator is identified in the plan with a unique node ID.

So far, we've used the terms query plan and query execution plan interchangeably. However, in SQL Server, there is the notion of an actual plan and an estimated plan. These differ only in the fact the actual plan has runtime data collected during actual execution (hence query execution plan), whereas the estimated plan is the output of the Query Optimizer that is put in the plan cache (hence query plan, without the execution moniker).

The estimated plan, known simply as a query plan, includes the following:

• Methods used to retrieve data from a table or indexed view
• Sequence of data-retrieval operations
• Order with which tables or indexed views are joined; refer to Chapter 3, Mechanics of the Query Optimizer, where we discussed join reordering
• Use of temporary structures in TempDB (worktables and workfiles)
• Estimated row counts, iterations, and costs from each step
• How data is aggregated

Additionally, an actual plan, also known as a query execution plan, includes the following:

• Use of parallelism
• Actual row counts and iterations
• Query execution warnings
• Query execution metrics, such as elapsed time, CPU time, presence of trace flags, memory usage, version of the CE, and top waits

So, analyzing a query execution plan is a skill that allows database professionals to identify the following:

• High-cost operations in a single query or batch
• Indexing needs, such as identifying when a scan is better than a seek or vice versa
• Outdated statistics that no longer accurately portray underlying data distributions
• Unexpected large row counts being passed from operator to operator
• Query or schema modification needs, for example when a query references multiple levels of nested views; that is, views that reference views that reference views that reference common tables at all levels

With these skills, developers and query writers in general can visually analyze how queries they write actually perform beyond simply looking at elapsed time. For database administrators (DBAs), these skills allow them to identify heavy hitters running in SQL Server that perhaps weren't a problem during development time, to analyze the queries and provide mitigations based on query execution plan analysis.

In this chapter, the following topics will be covered:

• Accessing a query plan
• Navigating a query plan
• Query plan operators of interest
• Query plan properties of interest