SQL Server 执行计划(8) - 使用 SQL 执行计划进行查询性能调优

在本系列的前几篇文章（见底部索引）中，我们介绍了SQL 执行计划的多个方面，我们讨论了执行计划是如何在内部生成的，不同类型的计划，主要组件和运算符以及如何阅读和分析使用不同工具生成的计划。在本文中，我们将介绍如何使用执行计划来调整 T-SQL 查询的性能。

SQL Server 查询性能调优被认为是数据库管理员的首要任务，也是一场无休止的战斗，以使其托管系统获得最佳性能，同时消耗最少的资源。任何数据库管理员在考虑查询性能调优时都会想起的第一种方法就是使用 SQL 执行计划。这是因为SQL 执行计划可以指导我们如何去优化查询。SQL 执行计划中清晰显示了查询在内部执行的方式，执行路线图、以及整体查询中执行成本最高的部分、同时还提供了如何设计最佳索引的提示信息。

SQL 执行计划中存在许多通用迹象表明查询中可能存在性能不佳的地方。例如，在执行计划中成本最高的运算符，是排除查询性能故障的良好起点。此外，如果执行计划中出现了一个粗箭头后面跟随着一个细箭头的状况，表示在此处大量数据被处理，同时从一个运算符流向另一个运算符，而最终结果只是返回少量记录。这通常表明查询关联数据表中缺乏合适索引或存在数据乘法性能问题。

了解了本系列中讨论的每个运算符的作用后，你可以识别出不必要的额外的运算符，这些运算符会增加开销而降低查询性能。此外，如果存在扫描整个表或索引的 扫描(Scan) 运算符，在大多数情况下都表明缺少索引、索引使用不当或查询不包含过滤条件。查询性能问题的另一个信号是执行计划警告。这些消息提示了需要解决多种性能问题，例如 TempDb 溢出问题、缺少索引或错误的基数估计等。

我们通过以下示例来深入解如何使用 SQL 执行计划来调整 SQL 查询的性能。首先，我们将使用下面的 CREATE TABLE T-SQL 语句创建两个新表：

CREATE TABLE Employee_Main
( Emp_ID INT IDENTITY (1,1) PRIMARY KEY,
  EMP_FirsrName VARCHAR (50),
  EMP_LastName VARCHAR (50),
  EMP_BirthDate DATETIME,
  EMP_PhoneNumber VARCHAR (50),
  EMP_Address VARCHAR (MAX)  
)
GO
CREATE TABLE EMP_Salaries
( EMP_ID INT IDENTITY (1,1),
  EMP_HireDate DATETIME,
  EMP_Salary INT,
  CONSTRAINT FK_EMP_Salaries_Employee_Main FOREIGN KEY (EMP_ID)     
  REFERENCES Employee_Main (EMP_ID),
)
GO

创建表后，我们给每个表填充10 万条记录。

简单查询的调优

现在测试用的数据表格准备好了。假设我们需要改善以下SELECT 语句的性能：

SELECT [EMP_ID]
      ,[EMP_HireDate]
      ,[EMP_Salary]
  FROM [AdventureWorks2016CTP3].[dbo].[EMP_Salaries]
  WHERE [EMP_ID]< 1000

调整查询性能的最佳方法是研究该查询的 SQL 执行计划。上面的 SELECT 查询的实际执行计划如下图所示：

从生成的计划中可以清楚地看出，SQL Server 引擎扫描了所有表行（10 万条记录）以检索请求的数据(1 条记录)。从执行计划中我们可以看到3个明显存在问题的地方：

1. 表扫描(Table Scan)运算符
2. 表扫描(Table Scan)运算符的高执行成本
3. 从粗箭头（将数据从表扫描操作输出到到下一个操作符）到细箭头(结果的输出)转换。

从上面执行计划中的三个问题迹象将我们可以得知性能不佳的主要原因是EMP_Salary 表中缺少索引。接下来我们通过 以下CREATE INDEX T-SQL 语句在 EMP_Salary 表的 EMP_ID 列上创建索引：

CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID)

然后运行相同的 T-SQL 语句。从新的执行计划中可以看出，SQL Server 引擎将直接在新创建的索引中查找请求的数据，无需扫描整个底层表，索引查找处理的成本降低到了50%。另外，从Index Seek操作符流到下一个操作符流的记录数明显减少，从箭头粗细就可以看出。详细执行计划如下图所示：
![简单查询调优-调优后
再看看查看查询的执行统计信息，可以看到看到行数减少到 2，而持续时间和 CPU 成本可以忽略不计。详细信息如下图所示：

我们在深入看看执行计划。我们还能发现另一个性能问题的迹象，那就是额外昂贵的 RID Lookup 和 Nested Loops 运算符。回想一下上一篇关于执行计划运算符的文章，我们可以通过创建覆盖索引来解决再次检索基础表的问题。

我们接着使用下面的 CREATE INDEX T-SQL 语句，为该查询创建覆盖索引：

CREATE NONCLUSTERED INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries (EMP_ID) INCLUDE (EMP_HireDate,EMP_Salary ) WITH DROP_EXISTING

再次运行相同的SELECT语句。在最新的执行计划中，RID Lookup和Nested Loops操作符不再出现，因为SQL Server 引擎在索引中已经找到了所有请求的数据。详细执行计划如下图所示：

复杂查询调优

结下来，我们再看一个复杂查询的调优示例。
让我们先删除在之前在 EMP_Salaries 表上创建的索引。使用下面的 DROP INDEX T-SQL 语句：

DROP INDEX IX_EMP_Salaries_EMP_ID ON EMP_Salaries

假设我们需要调整以下 SELECT 查询的性能，该查询连接先前创建的两个 EMP 测试表，以检索员工的信息：

SELECT EMP_FirsrName, EMP_LastName, EMP_BirthDate, EMP_Address, EMP_HireDate, EMP_Salary
FROM [dbo].[Employee_Main] EM
JOIN  [dbo].[EMP_Salaries] ES
ON EM.[EMP_ID] =ES.[EMP_ID]
WHERE EM.[EMP_ID] > 2470 AND ES.EMP_Salary >450

执行 SELECT 查询，从生成的实际计划中我们可以看到多个性能问题的迹象，例如 表扫描(Table Scan) 运算符-扫描了整个底层表。粗箭头-大量数据在操作符之间流转。不必要的昂贵运算符-例如Hash Match 运算符。详细的SQL 执行计划如下图所示：

进一步查看查询的执行统计话剧，会发现读取量大，持续时间长，CPU开销高等问题。详细如下图所示：

在执行计划的上半部分，我们看到SQLServer提示了的需要创建的推荐索引(绿色文字)。创建推荐索引可以提升查询的性能。我们通过以下T-SQL创建推荐索引：

创建索引后，再次执行 SELECT 语句。在最新的执行计划中我们可以看到 表扫描(Table Scan) 运算符更改为了 索引查找(Index Seek) 操作符。但是箭头仍然保持为粗箭头，这是这里的正常行为，因为没有从粗箭头到细箭头的过渡。详细执行计划如下图所示：

我们在看一下执行计划的统计数据。执行时长和CPU消耗都得到了少量改善。

下一步我们可以通过，改善查询语句来实现查询性能的增强。例如，可以使用限制返回行数的 TOP 子句来减小箭头的粗细。另一方面，可以通过使用下面的 CREATE INDEX T-SQL 语句在 EMP_Salaries 表上创建新索引来避免Filter 运算符的使用：

CREATE NONCLUSTERED INDEX [IX_EMP_Salaries_EMP_Salary] ON [dbo].[EMP_Salaries]  ([EMP_Salary] )

优化后生成的最新执行计划如下图所示：

从以上的示例可以清楚地看出，SQL 执行计划在调整不同 T-SQL 查询的性能方面的重要作用。请继续关注下一篇文章，我们将介绍执行计划在 SQL Server 内存中的保存位置以及如何保存执行计划以供重用！

系列目录

SQL Server 执行计划(1) - 概述
SQL Server 执行计划(2) - 执行计划类型
SQL Server 执行计划(3) - 如何分析图形执行计划
SQL Server 执行计划(4) - 执行计划运算符详解1
SQL Server 执行计划(5) - 执行计划运算符详解2
SQL Server 执行计划(6) - 执行计划运算符详解3
SQL Server 执行计划(7) - 执行计划运算符详解4
[SQL Server 执行计划(8) - 使用执行计划进行查询性能调优]
[SQL Server 执行计划(9) - 保存和比较执行计划]