剖析 sql server 执行计划(Dissecting SQL Server Execution Plans) 1.1

Chapter 1: Execution Plan Basics
第1章:执行计划基础知识

An execution plan, simply put, is the result of the query optimizer'sattempt to calculate the most efficient way to implement the requestrepresented by the T-SQL query you submitted.

一个执行计划,简单地说,是查询优化器尝试计算出最有效的方法来实现你提交的t- sql查询请求。

Execution plans can tell you how SQL Server may execute a query, or how itdid execute a query. They are, therefore, the primary means of troubleshootinga poorly performing query. Rather than guess at why a given query is performingthousands of scans, putting your I/O through the roof, you can use theexecution plan to identify the exact piece of SQL code that is causing theproblem. For example, your query may be reading an entire table-worth of datawhen, by removing a function in your WHERE clause, it could simply retrieve only therows you need. The execution plan displays all this and more.

执行计划可以告诉你如何SQLServer可以执行一个查询,或者如何执行一个查询。因此,它们是诊断性能不佳的查询的主要手段。而不是猜测为什么给定查询执行成千上万的扫描,造成如此之大的 I / O压力,可以使用执行计划准确的鉴定出是哪些SQL代码导致的问题。例如,您的查询可能需要阅读整个表才能获取有价值的数据,通过在WHERE子句移除一个函数,它只能轻松的检索所需的行。执行计划显示所有这些以及更多。

The aim of this chapter is to teach you to capture actual and estimatedexecution plans, in either graphical, text or XML format, and to understand thebasics of how to interpret these plans. In order to do this, we'll cover thefollowing topics:

本章的目的是教你通过图形,文本或XML形式来捕捉实际和估计执行计划,,并掌握一些基础知识来解释这些计划。为了做到这一点,我们将讨论下列主题:

     A briefbackgrounder on the query optimizer–Execution plans are a result of theoptimizer's operations so it's useful to know at least a little bit about whatthe optimizer does, and how it works.

     查询优化器的简单介绍——执行计划是优化器的操作的结果,因此通过执行计划我们可以知道优化器做了什么,和它是如何工作的。

     Actual andestimated execution plans– What they areand how they differ.

     实际和估计执行计划——它们是什么以及它们有何不同的。

     Capturing andinterpreting the different visual execution plan formats– We'll investigate graphical, text and XML execution plans.

     捕捉和解释不同的可视执行计划的格式——我们会研究图形,文字和XML执行计划。

     Retrieveexecution plans directly from the cache– Accessing theplan cache through Dynamic Management Objects (DMOs).

     从缓存直接获取执行计划——通过动态管理访问计划缓存对象(DMOs结构)。

      Automating execution plan capture–using SQL Server Trace Event.

      自动化执行计划捕获——使用Sqlserver跟踪事件。

What Happens When a Query is ubmitted?

当提交一个查询时,会发生什么?

When you submit a query to SQL Server, a number of processes on the servergo to work on that query. The purpose of all these processes is to manage thesystem such that it willSELECT,INSERT, UPDATEor DELETEthe data.

当你向SQL服务器提交一个查询,服务器会有一些列操作来处理这个查询。所有这些操作的目的就是控制系统来处理选择、插入、更新或删除数据。

These processes kick into action every time we submit a query to thesystem. While there are many different actions occurring simultaneously withinSQL Server, we're going to focus on the processes around queries. The processesfor meeting the requirements of queries break down roughly into two stages:

每次向系统提交一个查询这些过程都将有所作为。虽然有许多不同的动作同时发生在SQLServer,我们要专注于在查询的过程。满足需求的查询分解的过程大致分为两个阶段:

1. Processes thatoccur in the relational engine.

    关系引擎中的处理。

2.  Processes that occur in the storage engine.

    存储引擎中的处理

In the relational engine, the query is parsed and then processed by thequery optimizer, which generates an execution plan. The plan is sent (in abinary format) to the storage engine, which then uses that plan as a basis toretrieve or modify the underlying data. The storage engine is where processessuch as locking, index maintenance, and transactions occur. Sinceexecutionplansare created in the relational engine, that's where we'll be focusingthe majority of our attention.

在关系引擎中,查询被解析后,由查询优化器进行处理生成一个执行计划。计划(二进制格式)发送到存储引擎,然后使用该计划为基础来检索或修改底层数据。存储引擎用来处理这些操作,如锁定,索引维护,事务处理。因为执行计划是关系引擎中创建,所以我们将集中大部分的注意力在关系引擎中。

------------------------------------------------------------------------------------------------------------------------------------------

1 A T-SQL query can be an ad hoc query from a command line or a call torequest data from a stored procedure, any T-SQL within a single batch or astored procedure, or between GO statements

1t - sql查询是一个特别的查询从命令行或从一个存储过程调用请求数据,任何一个在单个批处理或存储过程,或者GO语句之间。

Query parsing
查询分析

When we pass a T-SQL query to the SQL Server system, the first place itgoes to is the relational engine.¹As the T-SQL arrives, it passes through a process that checks that theT-SQL is written correctly, that it's well formed. This process is queryparsing.If a query fails to parse correctly, for example, if you typeSELETC instead ofSELECT, then parsingstops and SQLServer returns an error to the query source. The output of theParserprocessis a parse tree, or query tree (or it's even called a sequence tree). The parsetree represents the logical steps necessary to execute the requested query.

当我们传递一个T-SQL查询给SQL Server系统,首先由关系引擎来处理,检查T-SQL的语法是否正确.这个过程被叫做查询分析.如果分析失败,比如SELECT被你敲成了SELETC,分析将停止并返回一个错误.查询处理器输出一个分析树,或者查询树(曾经还被叫做队列树).查询解析树描绘了执行查询的必要的逻辑步骤.

 

If the T-SQL string is not a data manipulation language (DML) statement,but instead is a data definition language (DDL) query, it will be not beoptimized because, for example, there is only one "right way" for theSQL Server system to create a table; therefore, there are no opportunities forimproving the performance of that type of statement.

如果t - SQL字符串不是一个数据操作语言(DML)语句,而是是一种数据定义语言(DDL)查询,它将不被优化,因为只有一个“正确的方式”为SQLServer系统创建一个表,因此,不会改善这种类语句的性能。

Algebrizer

If the T-SQL string is a DML statement and it has parsed correctly, theparse tree is passed to a process called thealgebrizer. The algebrizerresolves all the names of the various objects, tables and columns, referred towithin the query string. The algebrizer identifies, at the individual columnlevel, all the data types(varchar(50)versusdatetimeand so on) for the objects being accessed. It also determines the locationof aggregates (such asGROUP BY, andMAX) within the query, a process calledaggregate binding. Thisalgebrizer process is important because the query may have aliases or synonyms,names that don't exist in the database, that need to be resolved, or the querymay refer to objects not in the database. When objects don't exist in thedatabase, SQL Server returns an error from this step, defining the invalidobject name. As an example, the algebrizer would quickly find the tablePerson.Personin theAdventureWorks2008R2database. However, theProduct.Persontable, which doesn't exist, would cause an error and the wholeoptimization process would stop.

如果t - sql字符串是一个DML语句并正确解析后,生成的解析树将交给algebrizer处理。Algebrizer分解所有存在于查询字符串中的各种对象,表和列。识别被访问对象中每个列的数据类型(varchar(50)和datetime等等)。它还决定了查询中聚合的位置(如GROUP BY和MAX),这一过程被称为聚合绑定。这个algebrizer过程是很重要的,因为查询可能引用到数据库中没有的别名或同义词或名称,或查询会引用数据库中不存在的对象。当对象不存在在数据库中,SQL服务器从这一步返回一个错误并定义无效的对象名称。例如,algebrizer会很快在AdventureWorks2008R2数据库中找到表 Person.Person,但是表Product.Person表不存在,会导致一个错误,整个优化过程将停止。

 

The algebrizer outputs a binary called thequery processor tree,which is then passed on to thequery optimizer. The algebrizer's outputincludes a hash, a coded value repre­senting the query. The optimizer uses thehash to determine whether there is already a plan generated and stored in theplan cache. If there is a plan there, the process stops here and that plan isused. This reduces all the overhead required by the query optimizer to generatea new plan.

Algebrizer生成二元的查询处理树,用于查询优化. algebrizer的输出包括一个哈希值来标识一个查询。优化器使用哈希值来确定是否已经有一个计划生成并存储在缓存中。如果发现已经有一个计划,将停止继续处理这个计划。这减少了查询优化器生成一个新的计划的总开销的。