深入理解SQL Server执行计划

需积分: 9 180 浏览量更新于2024-07-18 收藏 7.32MB PDF 举报

"SQL Server Execution Plans Second Edition 是一本由Grant Fritchey编著，由SimpleTalk Publishing于2012年9月出版的书籍。本书是关于SQL Server执行计划的第二版，旨在帮助数据库管理员、开发者、报告编写者等了解SQL Server如何执行查询并优化性能。" SQL Server执行计划是SQL Server查询优化的关键工具，它揭示了数据库在幕后的工作原理，为用户提供了丰富的信息，以理解查询执行的各个环节。以下是执行计划中包含的一些关键知识点： 1. **索引使用情况**：执行计划可以展示SQL Server是否以及如何使用了定义的索引。它会显示哪些查询部分利用了索引，以及在哪些情况下没有使用索引，这对于优化查询性能至关重要。 2. **数据检索和连接**：通过执行计划，你可以看到数据是如何从查询涉及的表中被获取和连接的。这包括各种连接类型（如内连接、外连接）以及它们的效率。 3. **GROUP BY聚合**：对于包含GROUP BY子句的查询，执行计划展示了如何计算聚合结果。这有助于理解数据如何被分组，以及聚合函数（如SUM, COUNT, AVG等）的计算过程。 4. **操作预计负载与成本**：执行计划估计了每个操作对系统资源的预期消耗，包括CPU、内存和I/O。这可以帮助你评估查询的性能影响，并确定是否需要优化。 5. **查询优化器选择**：SQL Server查询优化器根据成本模型选择最佳执行路径。执行计划展示了优化器的决策过程，这可以帮助你理解为何SQL Server选择了特定的执行策略。 6. **性能瓶颈识别**：通过分析执行计划，可以发现潜在的性能瓶颈，如扫描过多的行、全表扫描而非索引扫描，或者不适当的连接方法。 7. **提示和强制执行计划**：在某些情况下，开发人员或DBA可能需要使用查询提示来影响SQL Server的执行计划选择，以强制使用特定的索引或避免某些操作。 8. **统计信息和动态管理视图**：执行计划结合统计信息和动态管理视图（DMVs）可以提供更深入的洞察，例如，查看索引的使用频率、查询缓存状态等。 9. **并行与串行计划**：执行计划还能揭示查询是否并行执行，以及并行度如何影响性能。并行执行虽然可以加快处理速度，但也可能带来额外的资源开销。 10. **查询改写和重写**：通过比较不同执行计划，可以识别出可能的查询改写策略，以改进查询性能。理解和解读SQL Server执行计划是提升查询性能和优化数据库设计的关键步骤。这本书《SQL Server Execution Plans Second Edition》提供了深入的指导，帮助读者掌握这一强大的诊断工具。

I'll push out updated versions of the eBook, at semi-regular intervals, for you to download

and provide your feedback.

Be sure to check out the website for this book:

(:.-.----),

where you can:

• Find links to download the latest versions of the eBook and buy the latest

printed book.

• Sign up to receive email notiﬁcations when I release a new eBook version.

• View the "Change Log," describing what has changed in each new version.

• Most importantly, let me know what you think! Seriously, hit me with whatever

feedback you have. Be honest, brutal…scary even, if necessary. If you provide feedback

that makes it into the next edition of the book, you'll receive an Amazon voucher to

buy yourself a copy of the latest printed version.

Enjoy the book, and I look forward to hearing from you!

Code Examples

Throughout this book, I'll be supplying T-SQL code that you're encouraged to run for

yourself, in order to generate the plans we'll be discussing. From the following URL,

you can obtain all the code you need to try out the examples in this book:

.-.RGBGF_SQLSEP_

C.

I wrote and tested the examples on SQL 2008 Release 2 sample database, Adventure-

Works2008R2. However, the majority of the code will run on all editions and versions of

SQL Server, starting from SQL Server 2005.

Chapter 1: Execution Plan Basics

stops and SQL Server returns an error to the query source. The output of the Parser

process is a parse tree, or query tree (or it's even called a sequence tree). The parse tree

represents the logical steps necessary to execute the requested query.

If the T-SQL string is not a data manipulation language (DML) statement, but instead is

a data deﬁnition language (DDL) query, 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.

Algebrizer

If the T-SQL string is a DML statement and it has parsed correctly, 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 identiﬁes,

at the individual column level, all the data types (varchar(50) versus datetime and so

on) for the objects being accessed. It also determines the location of aggregates (such as

GROUP BY, and MAX) within the query, a process called aggregate 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. When objects don't exist in the database, SQL Server returns an error from

this step, deﬁning the invalid object name. As an example, the algebrizer would quickly

ﬁnd the table Person.Person in the AdventureWorks2008R2 database. However,

the Product.Person table, which doesn't exist, would cause an error and the whole

optimization process would stop.

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

on to the query optimizer. The algebrizer's output includes a hash, a coded value repre-

senting the query. The optimizer uses the hash to determine whether there is already a

plan generated and stored in the plan cache. If there is a plan there, the process stops here

and that plan is used. This reduces all the overhead required by the query optimizer to

generate a new plan.

剩余321页未读，继续阅读

weixin_41018496

粉丝: 0
资源: 3

深入理解SQL Server执行计划

高清彩版 dissecting sqlserver execution plans

高清彩版 SQL Server Execution Plans Second Edition

《Pro Oracle SQL》Chapter10 -- 10.2 Optimizing SQL -10.2.1Testing Execution Plans

《Pro Oracle SQL》 读书笔记--Chapter 6--6.2 Execution Plans--之四

SQL Server 2017 Query Performance Tuning 5th Edition

Re-post: DBMS_XPLAN : Display Oracle Execution Plans

详解SQL Server 2008工具SQL Server Profiler

SQLServer2005 DTS 补丁（For SQL Server 2000 DTS）

MSSQL-execution-plan:有关Microsoft SQL Server的计划优化器的文章

使用Execution Plan写个sql server的demo

最新资源

《Pro Oracle SQL》读书笔记--Chapter 6--6.2 Execution Plans--之四