SQLServer 执行计划分析

执行计划概述

执行计划是SQL Server查询优化器生成的查询执行蓝图，它详细描述了SQL Server如何执行一个查询，包括：

如何访问表数据（扫描、查找等）
表之间的连接顺序和连接方式
使用哪些索引
如何处理排序、聚合、分组等操作
估计的执行成本和行数

执行计划是SQL Server性能优化的核心工具，通过分析执行计划，可以找出查询性能瓶颈并进行优化。

执行计划的类型

估计执行计划

估计执行计划是在不实际执行查询的情况下，由查询优化器基于统计信息生成的执行计划。它包含估计的成本、行数和操作顺序，但不包含实际的执行统计信息。

生成方式：

在SSMS中，点击"显示估计的执行计划"按钮或按Ctrl+L
使用SET SHOWPLAN_XML ON或SET SHOWPLAN_TEXT ON命令

实际执行计划

实际执行计划是查询实际执行后生成的执行计划，它包含了估计执行计划的所有信息，以及实际的执行统计信息，如实际行数、实际执行时间、CPU时间、I/O开销等。

生成方式：

在SSMS中，点击"包括实际的执行计划"按钮或按Ctrl+M
使用SET STATISTICS XML ON或SET STATISTICS PROFILE ON命令

执行计划缓存

SQL Server会将生成的执行计划缓存在内存中，以便后续相同或相似的查询可以重用，避免重新生成执行计划的开销。

查看执行计划缓存：

sql

-- 查看执行计划缓存
SELECT 
    cp.plan_handle,
    st.text,
    qp.query_plan,
    cp.usecounts,
    cp.size_in_bytes
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.text LIKE '%SELECT%';

执行计划的查看方式

图形化执行计划

图形化执行计划是SSMS中最常用的执行计划查看方式，它以图形方式显示查询执行的步骤和顺序，每个操作都用不同的图标表示，便于直观理解。

主要图标：

表扫描：黄色警告图标，表示全表扫描
索引扫描：绿色图标，表示索引扫描
索引查找：绿色图标，表示索引查找
键查找：红色警告图标，表示书签查找
哈希匹配：蓝色图标，表示哈希连接
嵌套循环：蓝色图标，表示嵌套循环连接
合并连接：蓝色图标，表示合并连接
排序：黄色警告图标，表示排序操作
聚合：黄色警告图标，表示聚合操作

文本化执行计划

文本化执行计划以文本格式显示执行计划，适合在命令行环境或脚本中使用。

生成方式：

sql

-- 生成文本格式的估计执行计划
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Sales.Orders WHERE OrderDate > '2023-01-01';
GO
SET SHOWPLAN_TEXT OFF;

-- 生成文本格式的实际执行计划
SET STATISTICS PROFILE ON;
GO
SELECT * FROM Sales.Orders WHERE OrderDate > '2023-01-01';
GO
SET STATISTICS PROFILE OFF;

XML执行计划

XML执行计划以XML格式存储执行计划的完整信息，包含了图形化和文本化执行计划的所有信息，以及更多的细节信息。

生成方式：

sql

-- 生成XML格式的估计执行计划
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Sales.Orders WHERE OrderDate > '2023-01-01';
GO
SET SHOWPLAN_XML OFF;

-- 生成XML格式的实际执行计划
SET STATISTICS XML ON;
GO
SELECT * FROM Sales.Orders WHERE OrderDate > '2023-01-01';
GO
SET STATISTICS XML OFF;

执行计划的解读

执行计划的基本结构

执行计划由一系列操作符组成，每个操作符代表一个物理或逻辑操作，操作符之间通过箭头连接，表示数据流动的方向。

主要操作符类型：

扫描操作符：
- 表扫描 (Table Scan)：扫描整个表，适用于小表或没有合适索引的情况
- 索引扫描 (Index Scan)：扫描整个索引，适用于范围查询或没有合适筛选条件的情况
查找操作符：
- 索引查找 (Index Seek)：使用索引查找特定行，效率高
- 键查找 (Key Lookup)：通过书签查找表中的其他列，通常需要优化
- RID查找 (RID Lookup)：针对堆表的书签查找
连接操作符：
- 嵌套循环连接 (Nested Loops)：适用于小结果集的连接
- 哈希匹配连接 (Hash Match)：适用于大结果集的连接
- 合并连接 (Merge Join)：适用于已排序的结果集连接
排序和聚合操作符：
- 排序 (Sort)：对结果集进行排序，可能导致性能问题
- 聚合 (Aggregate)：执行聚合操作，如SUM、COUNT、AVG等
- 流聚合 (Stream Aggregate)：适用于已排序的结果集
- 哈希聚合 (Hash Aggregate)：适用于未排序的结果集

执行计划的成本分析

执行计划中的每个操作符都有一个估计成本，总成本是所有操作符成本的总和。成本的计算基于CPU开销和I/O开销，其中：

CPU成本：执行操作所需的CPU时间
I/O成本：读取数据所需的磁盘I/O时间

解读成本信息：

成本百分比最高的操作符通常是性能瓶颈
关注估计行数与实际行数的差异，如果差异较大，可能需要更新统计信息
关注警告图标（黄色三角形），表示可能存在性能问题

执行计划的常见警告

执行计划中的警告图标表示可能存在性能问题，需要关注和优化：

缺少统计信息：查询优化器没有足够的统计信息来生成最优执行计划
隐式类型转换：数据类型不匹配导致索引失效
书签查找：键查找或RID查找，通常可以通过覆盖索引优化
排序警告：排序操作消耗大量资源
并行执行警告：并行执行计划可能存在问题
Cardinality Estimation警告：基数估计不准确

执行计划的分析技巧

识别性能瓶颈

查看成本百分比：成本百分比最高的操作符通常是性能瓶颈
查看警告图标：关注执行计划中的黄色警告图标
比较估计行数与实际行数：如果差异较大，可能需要更新统计信息
查看扫描操作：全表扫描或索引扫描可能导致性能问题
查看书签查找：键查找或RID查找通常可以优化

常见性能问题及优化方案

性能问题	优化方案
表扫描	创建合适的索引
索引扫描	优化查询条件，使用更精确的筛选条件
键查找	创建覆盖索引或包含列索引
排序操作	优化查询，避免不必要的排序，或创建排序索引
哈希匹配	优化连接顺序，或创建合适的索引
隐式类型转换	确保数据类型匹配，避免隐式转换
缺少统计信息	更新统计信息，或启用自动统计信息更新

执行计划分析示例

示例1：表扫描问题

sql

-- 执行以下查询，查看执行计划
SELECT * FROM Sales.Orders WHERE CustomerID = 1;

如果执行计划显示表扫描，说明没有为CustomerID创建索引，优化方案是创建索引：

sql

CREATE NONCLUSTERED INDEX idx_Orders_CustomerID ON Sales.Orders(CustomerID);

示例2：键查找问题

sql

-- 执行以下查询，查看执行计划
SELECT OrderID, OrderDate, CustomerID, ShipCity FROM Sales.Orders WHERE CustomerID = 1;

如果执行计划显示键查找，说明索引只包含了CustomerID列，没有包含ShipCity列，优化方案是创建覆盖索引：

sql

CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_Include 
ON Sales.Orders(CustomerID) INCLUDE (OrderDate, ShipCity);

示例3：隐式类型转换问题

sql

-- 执行以下查询，查看执行计划
SELECT * FROM Sales.Orders WHERE CustomerID = '1';

如果CustomerID是整数类型，而查询中使用了字符串'1'，会导致隐式类型转换，索引失效。优化方案是使用正确的数据类型：

sql

SELECT * FROM Sales.Orders WHERE CustomerID = 1;

执行计划的高级分析

强制使用特定执行计划

在某些情况下，可能需要强制SQL Server使用特定的执行计划，例如：

查询优化器生成了不理想的执行计划
需要测试不同执行计划的性能差异

使用查询提示：

sql

-- 强制使用索引
SELECT * FROM Sales.Orders WITH (INDEX(idx_Orders_CustomerID)) WHERE CustomerID = 1;

-- 强制连接顺序
SELECT * FROM Sales.Orders o JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
OPTION (FORCE ORDER);

-- 强制使用特定的连接类型
SELECT * FROM Sales.Orders o JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
OPTION (LOOP JOIN);

执行计划的统计信息更新

统计信息是查询优化器生成执行计划的重要依据，如果统计信息过时，可能导致执行计划不准确。

更新统计信息：

sql

-- 更新单个表的统计信息
UPDATE STATISTICS Sales.Orders;

-- 更新单个索引的统计信息
UPDATE STATISTICS Sales.Orders idx_Orders_CustomerID;

-- 更新所有表的统计信息
EXEC sp_updatestats;

执行计划的缓存管理

执行计划缓存可以提高查询性能，但也可能导致问题，例如：

缓存膨胀：过多的执行计划占用内存
计划缓存污染：无效的执行计划占用缓存
参数嗅探问题：特定参数生成的执行计划不适合其他参数

管理执行计划缓存：

sql

-- 清除特定查询的执行计划
DBCC FREEPROCCACHE(plan_handle);

-- 清除所有执行计划
DBCC FREEPROCCACHE;

-- 清除特定数据库的执行计划
DBCC FLUSHPROCINDB(database_id);

版本差异

SQLServer 2019

引入了新的基数估计器，提高了执行计划的准确性
增强了查询存储功能，可以更方便地管理执行计划
引入了智能查询处理功能，包括批处理模式内存授予反馈、表变量延迟编译等

SQLServer 2022

增强了执行计划的可读性，添加了更多的性能信息
引入了参数敏感计划优化，可以为不同参数生成不同的执行计划
增强了查询存储功能，支持自动计划修正
引入了内存中OLTP的执行计划优化

Azure SQL

支持自动优化功能，可以自动创建索引和强制使用最优执行计划
增强了查询性能洞察功能，可以更方便地分析执行计划
支持查询存储的高级功能，如计划回归检测和自动修正

实际生产场景

场景一：电商订单查询优化

问题：电商系统中的订单查询响应缓慢，执行计划显示表扫描。

分析：执行以下查询，查看执行计划：

sql

SELECT OrderID, OrderDate, CustomerName, ProductName, Quantity, TotalAmount 
FROM Sales.vw_OrderDetails 
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31' 
AND CustomerID = 123;

执行计划显示Sales.Orders表存在表扫描，因为OrderDate和CustomerID上没有合适的索引。

优化方案：

sql

-- 创建复合索引
CREATE NONCLUSTERED INDEX idx_Orders_CustomerID_OrderDate 
ON Sales.Orders(CustomerID, OrderDate) 
INCLUDE (OrderID, Status, ShipCity);

-- 优化视图，添加索引视图
CREATE VIEW Sales.vw_OrderDetails_Optimized
WITH SCHEMABINDING
AS
SELECT 
    o.OrderID,
    o.OrderDate,
    c.CustomerName,
    p.ProductName,
    od.Quantity,
    od.UnitPrice,
    od.Quantity * od.UnitPrice AS TotalAmount
FROM Sales.Orders o
JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
JOIN Sales.OrderDetails od ON o.OrderID = od.OrderID
JOIN Production.Products p ON od.ProductID = p.ProductID;

CREATE UNIQUE CLUSTERED INDEX idx_OrderDetails_OrderID ON Sales.vw_OrderDetails_Optimized(OrderID);

场景二：数据仓库报表优化

问题：数据仓库中的月度销售报表生成缓慢，执行计划显示哈希聚合和排序操作。

分析：执行以下查询，查看执行计划：

sql

SELECT 
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    ProductCategory,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalAmount) AS TotalSales
FROM DataWarehouse.FactSales 
JOIN DataWarehouse.DimProduct ON FactSales.ProductID = DimProduct.ProductID
GROUP BY YEAR(OrderDate), MONTH(OrderDate), ProductCategory
ORDER BY SalesYear, SalesMonth, ProductCategory;

执行计划显示哈希聚合和排序操作，消耗大量资源。

优化方案：

sql

-- 创建列存储索引，提高聚合查询性能
CREATE COLUMNSTORE INDEX idx_FactSales_ColumnStore 
ON DataWarehouse.FactSales(OrderDate, ProductID, Quantity, TotalAmount);

-- 创建聚合索引视图
CREATE VIEW DataWarehouse.vw_MonthlySales
WITH SCHEMABINDING
AS
SELECT 
    YEAR(OrderDate) AS SalesYear,
    MONTH(OrderDate) AS SalesMonth,
    ProductCategory,
    SUM(Quantity) AS TotalQuantity,
    SUM(TotalAmount) AS TotalSales,
    COUNT_BIG(*) AS RowCount
FROM DataWarehouse.FactSales fs
JOIN DataWarehouse.DimProduct dp ON fs.ProductID = dp.ProductID
GROUP BY YEAR(OrderDate), MONTH(OrderDate), ProductCategory;

CREATE UNIQUE CLUSTERED INDEX idx_MonthlySales_YearMonthCategory 
ON DataWarehouse.vw_MonthlySales(SalesYear, SalesMonth, ProductCategory);

场景三：参数嗅探问题

问题：存储过程在不同参数下性能差异很大，有时很快，有时很慢。

分析：执行以下存储过程，查看执行计划：

sql

CREATE PROCEDURE GetOrderDetails
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID;
END;

当@CustomerID是一个大客户时，生成的执行计划可能不适合小客户，导致性能问题。

优化方案：

sql

-- 选项1：使用OPTIMIZE FOR未知
ALTER PROCEDURE GetOrderDetails
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR UNKNOWN);
END;

-- 选项2：使用OPTIMIZE FOR特定值
ALTER PROCEDURE GetOrderDetails
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID
    OPTION (OPTIMIZE FOR (@CustomerID = 123));
END;

-- 选项3：使用RECOMPILE
ALTER PROCEDURE GetOrderDetails
    @CustomerID INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE);
END;

常见问题与解决方案

Q1：如何查看执行计划？

A：可以使用以下方式查看执行计划：

在SSMS中，点击"显示估计的执行计划"按钮或按Ctrl+L查看估计执行计划
在SSMS中，点击"包括实际的执行计划"按钮或按Ctrl+M查看实际执行计划
使用SET SHOWPLAN_XML ON、SET SHOWPLAN_TEXT ON、SET STATISTICS XML ON或SET STATISTICS PROFILE ON命令

Q2：执行计划中的成本百分比是什么意思？

A：执行计划中的成本百分比表示该操作在整个查询执行成本中所占的比例，成本百分比最高的操作符通常是性能瓶颈。

Q3：如何优化执行计划中的键查找？

A：可以通过以下方式优化键查找：

创建覆盖索引，将查询中需要的列包含在索引中
使用INCLUDE子句将列添加到索引中
减少查询中返回的列数，只返回需要的列

Q4：为什么估计行数与实际行数差异很大？

A：估计行数与实际行数差异很大可能是由以下原因导致的：

统计信息过时，需要更新统计信息
查询中使用了复杂的表达式或函数
表结构发生了变化，导致统计信息不准确

Q5：如何强制SQL Server使用特定的执行计划？

A：可以通过以下方式强制SQL Server使用特定的执行计划：

使用查询提示，如INDEX、FORCE ORDER、LOOP JOIN等
使用计划指南，将特定的执行计划与查询关联
使用查询存储，强制使用之前的最优执行计划

Q6：执行计划中的并行执行是什么意思？

A：并行执行是指SQL Server使用多个CPU核心来执行查询，提高查询性能。但并行执行也会消耗更多的资源，需要根据实际情况进行优化。

Q7：如何优化执行计划中的排序操作？

A：可以通过以下方式优化排序操作：

避免不必要的ORDER BY子句
创建合适的索引，使结果集已经排序
使用TOP子句限制返回的行数
优化查询逻辑，减少排序的数据量

Q8：如何更新统计信息？

A：可以使用以下方式更新统计信息：

使用UPDATE STATISTICS语句更新单个表或索引的统计信息
使用sp_updatestats存储过程更新所有表的统计信息
启用自动统计信息更新，设置AUTO_UPDATE_STATISTICS选项为ON

总结

执行计划是SQL Server性能优化的核心工具，通过分析执行计划，可以找出查询性能瓶颈并进行优化。理解执行计划的类型、结构和解读方法，掌握执行计划的分析技巧，对于提高SQL Server查询性能至关重要。

在实际生产环境中，需要定期分析执行计划，关注性能瓶颈，及时优化查询和索引。同时，需要了解不同SQL Server版本的执行计划特性，利用新特性提高查询性能。

通过不断学习和实践执行计划分析，可以提高SQL Server数据库的性能，提供更好的用户体验。

SQLServer 执行计划分析 ​

执行计划概述 ​

执行计划的类型 ​

估计执行计划 ​

实际执行计划 ​

执行计划缓存 ​

执行计划的查看方式 ​

图形化执行计划 ​

文本化执行计划 ​

XML执行计划 ​

执行计划的解读 ​

执行计划的基本结构 ​

执行计划的成本分析 ​

执行计划的常见警告 ​

执行计划的分析技巧 ​

识别性能瓶颈 ​

常见性能问题及优化方案 ​

执行计划分析示例 ​

执行计划的高级分析 ​

强制使用特定执行计划 ​

执行计划的统计信息更新 ​

执行计划的缓存管理 ​

版本差异 ​

SQLServer 2019 ​

SQLServer 2022 ​

Azure SQL ​

实际生产场景 ​

场景一：电商订单查询优化 ​

场景二：数据仓库报表优化 ​

场景三：参数嗅探问题 ​

常见问题与解决方案 ​

Q1：如何查看执行计划？ ​

Q2：执行计划中的成本百分比是什么意思？ ​

Q3：如何优化执行计划中的键查找？ ​

Q4：为什么估计行数与实际行数差异很大？ ​

Q5：如何强制SQL Server使用特定的执行计划？ ​

Q6：执行计划中的并行执行是什么意思？ ​

Q7：如何优化执行计划中的排序操作？ ​

Q8：如何更新统计信息？ ​

总结 ​

SQLServer 执行计划分析

执行计划概述

执行计划的类型

估计执行计划

实际执行计划

执行计划缓存

执行计划的查看方式

图形化执行计划

文本化执行计划

XML执行计划

执行计划的解读

执行计划的基本结构

执行计划的成本分析

执行计划的常见警告

执行计划的分析技巧

识别性能瓶颈

常见性能问题及优化方案

执行计划分析示例

执行计划的高级分析

强制使用特定执行计划

执行计划的统计信息更新

执行计划的缓存管理

版本差异

SQLServer 2019

SQLServer 2022

Azure SQL

实际生产场景

场景一：电商订单查询优化

场景二：数据仓库报表优化

场景三：参数嗅探问题

常见问题与解决方案

Q1：如何查看执行计划？

Q2：执行计划中的成本百分比是什么意思？

Q3：如何优化执行计划中的键查找？

Q4：为什么估计行数与实际行数差异很大？

Q5：如何强制SQL Server使用特定的执行计划？

Q6：执行计划中的并行执行是什么意思？

Q7：如何优化执行计划中的排序操作？

Q8：如何更新统计信息？

总结