揭开SQL Server查询优化器的秘密

需积分: 10 76 浏览量更新于2024-07-20 收藏 3.84MB PDF 举报

Inside the SQL Server Query Optimizer 本书《Inside the SQL Server Query Optimizer》由 Benjamin Nevarez 撰写，出版于 2010 年，是一本关于 SQL Server 查询优化器的详细指南。该书旨在揭开查询优化器的神秘面纱，帮助数据库开发者和管理员更好地理解查询优化器的工作机制，从而提高数据库和应用程序的性能。在本书中，作者 Benjamin Nevarez 带领读者深入了解查询优化器的核心概念，包括查询优化器的架构、查询优化过程、统计信息的收集和使用、索引的创建和优化、查询执行计划的生成和优化等。通过学习这些知识，读者将能够编写高效的查询语句，提供查询优化器所需的信息，以生成高效的执行计划，并 troubleshoot 查询优化器无法生成最佳执行计划的情况。书中还讨论了查询优化器的内部机制，包括查询优化器的架构、查询优化过程、查询优化器的参数设置等。读者可以通过学习这些知识，深入了解查询优化器的工作机制，从而更好地优化数据库和应用程序的性能。此外，本书还提供了一些实用的查询优化技术和最佳实践，旨在帮助读者快速提高数据库和应用程序的性能。这些技术和实践包括查询优化器的参数设置、索引的创建和优化、查询执行计划的优化等。《Inside the SQL Server Query Optimizer》是一本非常实用的书籍，旨在帮助数据库开发者和管理员更好地理解查询优化器的工作机制，并提高数据库和应用程序的性能。知识点： 1. 查询优化器的架构和工作机制 2. 查询优化过程和统计信息的收集和使用 3. 索引的创建和优化 4. 查询执行计划的生成和优化 5. 查询优化器的参数设置 6. 查询优化技术和最佳实践 7. 查询优化器的内部机制和实现细节通过学习这些知识点，读者将能够更好地理解查询优化器的工作机制，并提高数据库和应用程序的性能。

Chapter 1: Introduction to Query Optimization

In order to arrive at what it believes to be the best plan for executing a query, the query

processor performs a number of diﬀerent steps; the entire query processing process is

shown in Figure 1-1.

Figure 1-1: The query processing process.

We'll look at this whole process in much more detail later in the book, but I'll just run

through the steps brieﬂy now.

• Parsing and binding – the query is parsed and bound. Assuming the query is valid,

the output of this phase is a logical tree, with each node in the tree representing a

logical operation that the query must perform, such as reading a particular table, or

performing an inner join.

Chapter 1: Introduction to Query Optimization

• Query optimization – the logical tree is then used to run the query optimization

process, which roughly consists of the following two steps:

• generation of possible execution plans – using the logical tree, the Query

Optimizer devises a number of possible ways to execute the query, i.e. a number

of possible execution plans; an execution plan is, in essence, a set of physical

operations (an Index Seek, a Nested Loops Join, and so on), that can be performed

to produce the required result, as described by the logical tree

• cost-assessment of each plan – while the Query Optimizer does not generate every

possible execution plan, it assesses the resource and time cost of each plan it does

generate; the plan that the Query Optimizer deems to have the lowest cost of those

it has assessed is selected, and passed along to the execution engine.

• Query execution, plan caching – the query is executed by the execution engine,

according to the selected plan; the plan may be stored in memory, in the plan cache.

Parsing and binding are the ﬁrst operations performed when a query is submitted to a

SQL Server instance. Parsing makes sure that the T-SQL query has a valid syntax, and

translates the SQL query into an initial tree representation: speciﬁcally, a tree of logical

operators representing the high-level steps required to execute the query in question.

Initially, these logical operators will be closely related to the original syntax of the query,

and will include such logical operations as "get data from the Customer table," "get data

from the Contact table," "perform an inner join," and so on. Diﬀerent tree representa-

tions of the query will be used throughout the optimization process, and this logical tree

will receive diﬀerent names until it is ﬁnally used to initialize the Memo structure, as will

be discussed later.

Binding is mostly concerned with name resolution. During the binding operation,

SQL Server makes sure that all the object names do exist, and associates every table and

column name on the parse tree with their corresponding object in the system catalog.

The output of this second process is called an algebrized tree, which is then sent to the

Query Optimizer.

Chapter 1: Introduction to Query Optimization

The next step is the optimization process, which is basically the generation of candidate

execution plans and the selection of the best of these plans according to their cost. As

has already been mentioned, the SQL Server Query Optimizer uses a cost-estimation

model to estimate the cost of each of the candidate plans.

In essence, query optimization is the process of mapping the logical query operations

expressed in the original tree representation to physical operations, which can be carried

out by the execution engine. So, it's actually the functionality of the execution engine that

is being implemented in the execution plans being created by the Query Optimizer, that

is, the execution engine implements a certain number of diﬀerent algorithms, and it

is from these algorithms that the Query Optimizer must choose, when formulating its

execution plans. It does this by translating the original logical operations into the physical

operations that the execution engine is capable of performing, and execution plans show

both the logical and physical operations. Some logical operations, such as a Sort, translate

to the same physical operation, whereas other logical operations map to several possible

physical operations. For example, a logical join can be mapped to a Nested Loops Join,

Merge Join, or Hash Join physical operator.

Thus, the end product of the query optimization process is an execution plan: a

tree consisting of a number of physical operators, which contain the algorithms

to be performed by the execution engine in order to obtain the desired results from

the database.

Generating candidate execution plans

As stated, the basic purpose of the Query Optimizer is to ﬁnd an ecient execution plan

for your query. Even for relatively simple queries, there may be a large number of diﬀerent

ways to access the data to produce the same end result. As such, the Query Optimizer

has to select the best possible plan from what may be a very large number of candidate

execution plans, and it's important that it makes a wise choice, as the time taken to return

the results to the user can vary wildly, depending on which plan is selected.

Chapter 1: Introduction to Query Optimization

The job of the Query Optimizer is to create and assess as many candidate execution plans

as possible, within certain criteria, in order to arrive at the best possible plan. We deﬁne

the search space for a given query as the set of all the possible execution plans for that

query, and any possible plan in this search space returns the same results. Theoretically,

in order to ﬁnd the optimum execution plan for a query, a cost-based query optimizer

should generate all possible execution plans that exist in that search space, and correctly

estimate the cost of each plan. However, some complex queries may have thousands, or

even millions, of possible execution plans and, while the SQL Server Query Optimizer

can typically consider a large number of candidate execution plans, it cannot perform

an exhaustive search of all the possible plans for every query. If it did, the time taken to

assess all of the plans would be unacceptably long, and could start to have a major impact

on the overall query execution time.

The Query Optimizer must strike a balance between optimization time and plan

quality. For example, if the Query Optimizer spends one second ﬁnding a good enough

plan that executes in one minute, then it doesn't make sense to try to ﬁnd the perfect

or most optimal plan, if this is going to take ﬁve minutes of optimization time, plus the

execution time. So SQL Server does not do an exhaustive search, but instead tries to ﬁnd

a suitably ecient plan as quickly as possible. As the Query Optimizer is working within

a time constraint, there's a chance that the plan selected may be the optimal plan but it is

also likely that it may just be something close to the optimal plan.

In order to explore the search space, the Query Optimizer uses transformation rules and

heuristics. The generation of candidate execution plans is performed inside the Query

Optimizer using transformation rules, and the use of heuristics limits the number of

choices considered, in order to keep the optimization time reasonable. Candidate plans

are stored in memory during the optimization, in a component called the Memo.

Transformation rules, heuristics, and the Memo will be discussed in more detail in

Chapter 5, The Optimization Process.

Chapter 1: Introduction to Query Optimization

Assessing the cost of each plan

Searching or enumerating candidate plans is just one part of the optimization process.

The Query Optimizer still needs to estimate the cost of these plans and select the least

expensive one. To estimate the cost of a plan, it estimates the cost of each physical

operator in that plan, using costing formulas that consider the use of resources such as

I/O, CPU, and memory. This cost estimation depends mostly on the algorithm used by

the physical operator, as well as the estimated number of records that will need to be

processed; this estimate of the number of records is known as the cardinality estimation.

To help with this cardinality estimation, SQL Server uses and maintains optimizer

statistics, which contain statistical information describing the distribution of values

in one or more columns of a table. Once the cost for each operator is estimated using

estimations of cardinality and resource demands, the Query Optimizer will add up all of

these costs to estimate the cost for the entire plan. Rather than go into more detail here,

statistics and cost estimation will be covered in more detail in Chapter 3, Statistics and

Cost Estimation.

Query execution and plan caching

Once the query is optimized, the resulting plan is used by the execution engine to retrieve

the desired data. The generated execution plan may be stored in memory, in the plan

cache (known as the "procedure cache" in previous versions of SQL Server) in order that

it may be reused if the same query is executed again. If a valid plan is available in the plan

cache, then the optimization process can be skipped and the associated cost of this step,

in terms of optimization time, CPU resources, and so on, can be avoided.

However, reuse of an existing plan may not always be the best solution for a given query.

Depending on the distribution of data within a table, the optimal execution plan for a

given query may diﬀer greatly, depending on the parameters provided in said query, and

a behavior known as parameter sning may result in a suboptimal plan being chosen.

剩余251页未读，继续阅读

vanridin

粉丝: 108
资源: 1187

揭开SQL Server查询优化器的秘密

深入解析：Microsoft SQL Server 2008 内部机制

Oracle与SQL Server语法迁移指南：关键区别与转换策略

QueryOptimizer:Java数据库管理优化技术详解

Inside the SQL Server Query Optimizer

Inside_the_SQL_Server_Query_Optimizer

SQL Optimizer for SQL Server 10.0.3

QueryOptimizer:数据库管理 ex2

sql-optimizer-for-sql-server_1001官方试用版+安装手册+使用教程

query_optimizer

SQL-Server-DBA.rar_sql server

最新资源