上QQ阅读APP看书，第一时间看更新

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.

Query execution plans are often referred to as a showplan, which is a textual, XML, or graphical representation of the plan.

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).

Going forward, we will refer to plans in a more precise fashion, depending on whether they have runtime data.

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

Whether all this information is available or just a subset depends on the version of SQL Server on which the query execution plan was captured.

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

There are certain images in the chapter that may seem unclear. Please note that the individual text is not important, and does not need to be read.

本周热推：

计算机应用复习与练习多媒体技术基础及应用数据库系统管理初步 Dreamweaver CS3网页制作融会贯通 Linux系统下C程序开发详解