SQL Server执行计划解析与优化

5星 · 超过95%的资源需积分: 9 97 浏览量更新于2024-07-25 收藏 12.19MB PDF 举报

"SQL Server Execution Plans 由 Grant Fritchey 编著，深入解析 SQL Server 查询执行计划，包括基础概念、图形化与文本/XML 执行计划、游标操作及 XML 在执行计划中的应用等内容。" 在 SQL Server 中，执行计划是数据库系统执行查询时的详细步骤描述，它揭示了数据库内部的工作机制。执行计划对于数据库管理员、开发者、报告编写者等任何使用 T-SQL 访问 SQL Server 数据库的人来说都是至关重要的工具，因为它提供了以下关键信息： 1. **索引使用情况**：执行计划显示了查询中使用的索引，以及在没有合适索引的情况下如何进行全表扫描。 2. **数据检索和连接方式**：通过执行计划，你可以了解 SQL Server 是如何从查询中涉及的表中获取和组合数据的，包括如何执行各种类型的连接（如内连接、外连接）。 3. **聚合函数处理**：对于包含 GROUP BY 子句的查询，执行计划展示了 SQL Server 如何构建聚合结果。 4. **系统资源预期负载和成本估算**：执行计划还估计了各项操作对系统资源的需求和预计执行成本，这对于性能优化至关重要。本书的目标是将有关执行计划的实用信息汇总到一个集中的位置，并按照清晰的逻辑结构组织，从捕获执行计划的基础知识开始，逐步解释其含义，然后介绍如何利用执行计划来理解查询的运行方式和性能问题。书中可能会涵盖以下主题： - 如何捕捉和查看执行计划，包括图形界面和文本/XML 格式。 - 游标操作在执行计划中的体现，以及它们对性能的影响。 - 使用 XML 格式的执行计划提供更详细的元数据和调试信息。 - 性能分析技巧，包括识别执行计划中的瓶颈和改进查询的方法。 - 使用执行计划进行查询优化，比如调整索引策略，重构查询语句以减少资源消耗。 - 对于复杂查询和多表联接，如何解读执行计划中的各个操作节点和它们之间的关系。通过学习和掌握 SQL Server 执行计划，你可以更有效地诊断性能问题，提升数据库的整体运行效率。这本书正是为你提供这样一套全面的指南，帮助你成为 SQL Server 性能优化的专家。

Foreword 15

FOREWORD

I have attended many SQL Server conferences since 2000, and I have

spoken with hundreds of people attending them. One of the most

significant trends I have noticed over the past eight years is the huge

number of people who have made the transition from IT Professional

or Developer, to SQL Server Database Administrator. In some cases,

the transition has been planned and well thought-out. In other cases, it

was an accidental transition, when an organization desperately needed a

DBA, and the closest warm body was chosen for the job.

No matter the route you took to get there, all DBAs have one thing in

common: we have had to learn how to become DBAs through self-

training, hard work, and trial and error. In other words, there is no

school you can attend to become a DBA, it is something you have to

learn on your own. Some of us are fortunate to attend a class or two, or

to have a great mentor to help us along. However, in most cases, DBAs

become DBAs the hard way: we are thrown into the water and we either

sink or swim.

One of the biggest components of a DBA's self-learning process is

reading. Fortunately, there are many good books on the basics of being

a DBA that make a good starting point for your learning process. Once

you have read the basic books and have gotten some experience under

your belt, you will soon want to know more of the details of how SQL

Server works. While there are a few good books on the advanced use of

SQL Server, there are still many areas that aren't well covered. One of

those areas of missing knowledge is a dedicated book on SQL Server

execution plans.

That's where Dissecting SQL Server Execution Plans comes into play. It is

the first book available anywhere that focuses entirely on what SQL

Server execution plans are, how to read them, and how to apply the

information you learn from them in order to boost the performance of

your SQL Servers.

This was not an easy book to write because SQL Server execution plans

are not well documented anywhere. Grant Fritchey spent a huge amount

of time researching SQL Server execution plans, and conducting

original research as necessary, in order to write the material in this book.

Once you understand the fundamentals of SQL Server, this book

should be on top of your reading list, because understanding SQL

Chapter 1: Execution Plan Basics 17

CHAPTER 1: EXECUTION PLAN BASICS

An execution plan, simply put, is the result of the query optimizer's

attempt to calculate the most efficient way to implement the request

represented by the T-SQL query you submitted.

Execution plans can tell you how a query will be executed, or how a

query was executed. They are, therefore, the DBA's primary means of

troubleshooting a poorly performing query. Rather than guess at why a

given query is performing thousands of scans, putting your I/O

through the roof, you can use the execution plan to identify the exact

piece of SQL code that is causing the problem. For example, it may be

scanning an entire table-worth of data when, with the proper index, it

could simply backpack out only the rows you need. All this and more is

displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated

execution plans, in either graphical, text or XML format, and to

understand the basics of how to interpret them. In order to do this,

we'll cover the following topics:

• A brief backgrounder on the query optimizer – execution

plans are a result of the optimizer's calculations so it's useful to

know at least a little bit about what the optimizer does, and how

it works

• Actual and Estimated execution plans – what they are and how

they differ

• Capturing and interpreting the different visual execution

plan formats – we'll investigate graphical, text and XML

execution plans for a very basic SELECT query

• Automating execution plan capture – using the SQL Server

Profiler tool

What Happens When a Query is Submitted?

When you submit a query to a SQL Server database, a number of

processes on the server go to work on that query. The purpose of all

these processes is to manage the system such that it will provide your

data back to you, or store it, in as timely a manner as possible, whilst

maintaining the integrity of the data.

These processes are run for each and every query submitted to the

system. While there are lots of different actions occurring

simultaneously within SQL Server, we're going to focus on the

processes around T-SQL. They break down roughly into two stages:

1. Processes that occur in the relational engine

2. Processes that occur in the storage engine.

In the relational engine the query is parsed and then processed by the

Query Optimizer, which generates an execution plan. The plan is sent

(in a binary format) to the storage engine, which it then uses to retrieve

or update the underlying data. The storage engine is where processes

such as locking, index maintenance and transactions occur. Since

execution plans are created in the relational engine, that's where we'll

be focusing our attention.

Query Parsing

When you pass a T-SQL query to the SQL Server system, the first place

it goes to is the relational engine.1

As th

e T-SQL arrives, it passes through a process that checks that the T-

SQL is written correctly, that it's well formed. This process is known as

query parsing. The output of the Parser process is a parse tree, or query

tree (or even sequence tree). The parse tree represents the logical steps

necessary to execute the query that has been requested.

If the T-SQL string is not a data manipulation language (DML)

statement, it will be not be optimized because, for example, there is only

one "right way" for the SQL Server system to create a table; therefore,

there are no opportunities for improving the performance of that type

of statement. If the T-SQL string is a DML statement, the parse tree is

passed to a process called the algebrizer. The algebrizer resolves all the

names of the various objects, tables and columns, referred to within the

query string. The algebrizer identifies, at the individual column level, all

the types (varchar(50) versus nvarchar(25) and so on) of the objects

being accessed. It also determines the location of aggregates (such as

GROUP BY, and MAX) within the query, a process called aggregate

A T-SQL Query can be an ad hoc query from a command line or a call

to request data from a stored procedure, any T-SQL within a single

batch or a stored procedure, or between "GO" statements.

Chapter 1: Execution Plan Basics 19

binding. This algebrizer 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 query may refer to objects not in the database.

The algebrizer outputs a binary called the query processor tree, which

is then passed on to the query optimizer.

The Query Optimizer

The query optimizer is essentially a piece of software that "models" the

way in which the database relational engine works. Using the query

processor tree and the statistics it has about the data, and applying the

model, it works out what it thinks will be the optimal way to execute the

query – that is, it generates an execution plan.

In other words, the optimizer figures out how best to implement the

request represented by the T-SQL query you submitted. It decides if the

data can be accessed through indexes, what types of joins to use and

much more. The decisions made by the optimizer are based on what it

calculates to be the cost of a given execution plan, in terms of the

required CPU processing and I/O, and how fast it will execute. Hence,

this is known as a cost-based plan.

The optimizer will generate and evaluate many plans (unless there is

already a cached plan) and, generally speaking, will choose the lowest-

cost plan i.e. the plan it thinks will execute the query as fast as possible

and use the least amount of resources, CPU and I/O. The calculation

of the execution speed is the most important calculation and the

optimizer will use a process that is more CPU-intensive if it will return

results that much faster. Sometimes, the optimizer will select a less

efficient plan if it thinks it will take more time to evaluate many plans

than to run a less efficient plan.

If you submit a very simple query – for example, a single table with no

indexes and with no aggregates or calculations within the query – then

rather than spend time trying to calculate the absolute optimal plan, the

optimizer will simply apply a single, trivial plan to these types of

queries.

If the query is non-trivial, the optimizer will perform a cost-based

calculation to select a plan. In order to do this, it relies on statistics that

are maintained by SQL Server.

Statistics are collected on columns and indexes within the database, and

describe the data distribution and the uniqueness, or selectivity, of the

data. The information that makes up statistics is represented by a

histogram, a tabulation of counts of the occurrence of a particular

剩余250页未读，继续阅读

emilyandyeen

粉丝: 0
资源: 1

SQL Server执行计划解析与优化

SQLServerExecutionPlans

SQLSERVER执行计划

SQL执行计划

SQL Server Execution Plans Second Edition

高清彩版 dissecting sqlserver execution plans

高清彩版 SQL Server Execution Plans Second Edition

剖析SQL Server执行计划

SQL Server 2017 Query Performance Tuning 5th Edition

SQLServer2008查询性能优化 1/2

SQLServer2008查询性能优化 2/2

最新资源