外观
SQLServer 执行计划分析
执行计划是SQL Server生成的查询执行方案，描述了如何获取数据、连接表、排序和过滤结果等操作步骤。理解执行计划是DBA进行性能优化的核心技能。
执行计划类型
- 预估执行计划：不执行查询，基于统计信息生成，用于预先评估查询性能
- 实际执行计划：执行查询后生成，包含实际执行统计信息（如实际行数、CPU时间、IO成本）
- XML执行计划：包含最详细的执行计划信息，可用于深入分析
执行计划获取方式
生产场景示例
对于生产环境中运行时间较长的查询，优先使用预估执行计划进行分析，避免影响生产系统。对于复杂查询，结合实际执行计划和统计信息IO/TIME进行综合分析。
实用命令
sql
-- 获取预估执行计划
SET SHOWPLAN_XML ON;
GO
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';
GO
SET SHOWPLAN_XML OFF;
-- 获取实际执行计划和统计信息
SET STATISTICS XML ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM Orders WHERE OrderDate > '2023-01-01';
GO
SET STATISTICS XML OFF;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;执行计划操作符
| 操作符 | 含义 | 性能影响 | 优化建议 |
|---|---|---|---|
| Index Seek | 索引查找，高效定位单行或少量行 | 低 | 理想的操作符，保持索引维护良好 |
| Index Scan | 索引扫描，遍历索引中的所有行 | 中 | 考虑添加更具选择性的过滤条件或索引 |
| Table Scan | 全表扫描，遍历表中的所有行 | 高 | 为过滤条件创建合适的索引 |
| Nested Loops | 嵌套循环连接，适合小结果集连接 | 低到中 | 确保内层表有合适的索引 |
| Hash Match | 哈希匹配连接，适合大结果集连接 | 中到高 | 考虑优化连接顺序或添加过滤条件 |
| Merge Join | 合并连接，适合已排序的结果集连接 | 中 | 确保连接列已排序或有合适的索引 |
| Sort | 排序操作，消耗CPU和内存 | 中到高 | 考虑添加包含排序字段的索引 |
| Key Lookup | 键查找，通过书签查找行数据 | 中到高 | 创建覆盖索引，包含查询所需的所有列 |
| RID Lookup | RID查找，通过行标识符查找堆表数据 | 中到高 | 为堆表创建聚集索引或覆盖索引 |
图形化执行计划解读
- 查看执行顺序：从右到左，从上到下
- 关注成本占比：红色圆圈表示高成本操作，需要重点优化
- 分析操作符属性：鼠标悬停可查看详细属性，如估计行数、实际行数、CPU成本、IO成本
- 检查警告信息：黄色感叹号表示存在问题，如缺少统计信息、隐式转换
执行计划分析步骤
生产场景示例
对于慢查询，首先查看执行计划中的高成本操作符，比较估计行数与实际行数判断统计信息是否准确，检查索引使用情况是否存在全表扫描或键查找。
实用技巧
- 识别主查询路径：找到成本最高的执行路径
- 检查估计与实际差异：如果估计行数与实际行数差异较大，说明统计信息过时
- 分析连接方式：评估连接操作符的选择是否合理
- 查看索引使用情况：确认是否使用了合适的索引，是否存在全表扫描
- 检查排序和聚合操作：这些操作通常消耗大量资源
- 查看警告信息：解决所有警告，如隐式转换、缺少统计信息
索引相关优化
生产场景示例
某电商网站的订单查询页面加载缓慢，执行计划显示全表扫描。分析发现查询条件为OrderDate和Status，缺少合适的索引。优化方案是创建包含过滤条件的覆盖索引。
实用配置命令
sql
-- 存在键查找的查询优化
-- 原查询
SELECT OrderID, CustomerID, OrderDate, Status FROM Orders WHERE CustomerID = 123;
-- 优化：创建覆盖索引
CREATE INDEX IX_Orders_CustomerID_Include ON Orders(CustomerID)
INCLUDE (OrderID, OrderDate, Status);
-- 避免全表扫描的优化
-- 原查询（全表扫描）
SELECT * FROM Orders WHERE OrderDate > '2023-01-01' AND Status = 'Shipped';
-- 优化：创建过滤索引
CREATE INDEX IX_Orders_OrderDate_Status ON Orders(OrderDate)
WHERE Status = 'Shipped';
-- 优化索引键顺序
-- 原则：将选择性高的列放在索引前面
-- 示例：CustomerID选择性高于OrderDate
CREATE INDEX IX_Orders_CustomerID_OrderDate ON Orders(CustomerID, OrderDate);统计信息优化
生产场景示例
某报表查询执行计划显示估计行数为1000，实际行数为100万，导致查询计划不合理。分析发现表数据变化较大，统计信息过时。优化方案是更新统计信息。
实用配置命令
sql
-- 更新单个表的统计信息
UPDATE STATISTICS Orders;
-- 更新单个索引的统计信息
UPDATE STATISTICS Orders IX_Orders_CustomerID;
-- 全量扫描更新统计信息（适合大型表）
UPDATE STATISTICS Orders WITH FULLSCAN;
-- 创建过滤统计信息（数据分布不均匀的列）
CREATE STATISTICS ST_Orders_Active ON Orders(OrderDate) WHERE Status = 'Active';
-- 检查统计信息更新时间
SELECT
OBJECT_NAME(object_id) AS TableName,
name AS StatisticName,
STATS_DATE(object_id, stats_id) AS LastUpdated
FROM sys.stats
WHERE object_id = OBJECT_ID('Orders');连接操作优化
生产场景示例
两个大型表连接查询执行缓慢，执行计划显示Hash Match连接。分析发现连接条件缺少合适的索引。优化方案是为连接列创建索引，将Hash Match转换为Nested Loops。
实用配置命令
sql
-- 优化连接顺序
-- 原则：将小结果集表放在前面
SELECT *
FROM SmallTable s
JOIN LargeTable l ON s.ID = l.SmallTableID;
-- 避免笛卡尔积
-- 错误：缺少连接条件
SELECT * FROM TableA, TableB;
-- 正确：添加连接条件
SELECT * FROM TableA a JOIN TableB b ON a.ID = b.TableAID;
-- 优化多表连接
-- 原查询
SELECT o.OrderID, c.CustomerName, p.ProductName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate > '2023-01-01';
-- 优化：为连接列和过滤条件创建索引
CREATE INDEX IX_Orders_OrderDate_CustomerID ON Orders(OrderDate, CustomerID);
CREATE INDEX IX_OrderDetails_OrderID_ProductID ON OrderDetails(OrderID, ProductID);慢查询执行计划分析
场景描述
生产环境中，某个订单查询执行时间超过10秒，影响业务正常运行。
分析步骤
获取执行计划和统计信息
sqlSET STATISTICS XML ON; SET STATISTICS IO ON; SET STATISTICS TIME ON; -- 慢查询语句 SELECT o.OrderID, o.OrderDate, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS TotalAmount FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY o.OrderID, o.OrderDate, c.CustomerName; SET STATISTICS XML OFF; SET STATISTICS IO OFF; SET STATISTICS TIME OFF;分析执行计划
- 发现问题：Orders表上存在全表扫描，Customer表上存在键查找
- 原因：Orders表缺少OrderDate索引，Customer表缺少覆盖索引
优化方案
sql-- 创建Orders表的OrderDate索引 CREATE INDEX IX_Orders_OrderDate_CustomerID ON Orders(OrderDate, CustomerID); -- 创建Customer表的覆盖索引 CREATE INDEX IX_Customers_CustomerID_Include ON Customers(CustomerID) INCLUDE (CustomerName); -- 创建OrderDetails表的聚合索引 CREATE INDEX IX_OrderDetails_OrderID_Include ON OrderDetails(OrderID) INCLUDE (Quantity, UnitPrice);验证优化效果
- 重新执行查询，执行时间从10秒降低到0.5秒
- 执行计划显示所有表都使用了合适的索引，没有全表扫描
执行计划回归分析
场景描述
应用发布后，查询性能突然下降，需要分析执行计划变化。
分析步骤
使用Query Store比较执行计划
- 查询Store自动保存查询的历史执行计划
- 比较不同时间点的执行计划，找出变化
检查统计信息更新情况
sql-- 查看统计信息更新时间 SELECT OBJECT_NAME(object_id) AS TableName, name AS StatisticName, STATS_DATE(object_id, stats_id) AS LastUpdated FROM sys.stats WHERE object_id = OBJECT_ID('Orders');检查索引变更
sql-- 查看索引状态 SELECT name AS IndexName, type_desc AS IndexType, is_disabled AS IsDisabled, is_hypothetical AS IsHypothetical FROM sys.indexes WHERE object_id = OBJECT_ID('Orders');使用计划指南锁定执行计划
sql-- 获取良好的执行计划XML SET SHOWPLAN_XML ON; GO SELECT * FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE o.OrderDate BETWEEN '2023-01-01' AND '2023-12-31'; GO SET SHOWPLAN_XML OFF; -- 创建计划指南，使用特定的执行计划 EXEC sp_create_plan_guide @name = N'Guide_OrderQuery', @stmt = N'SELECT * FROM Orders o JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE o.OrderDate BETWEEN @StartDate AND @EndDate', @type = N'SQL', @module_or_batch = NULL, @params = N'@StartDate datetime, @EndDate datetime', @hints = N'OPTION(USE PLAN N''<ShowPlanXML>...</ShowPlanXML>'')';
执行计划中的隐式转换问题
场景描述
查询执行计划中出现隐式转换警告，导致索引失效。
分析步骤
识别隐式转换
- 在执行计划中查看警告信息，找到隐式转换的列
- 例如：将varchar列转换为int类型
分析原因
- 查询中参数类型与表列类型不匹配
- 例如：表中列是varchar(10)，但查询中使用int类型参数
优化方案
- 修改应用程序，确保参数类型与表列类型匹配
- 或在查询中显式转换参数类型
实用示例
sql
-- 存在隐式转换的查询
-- 表中CustomerID列是varchar(10)类型
SELECT * FROM Customers WHERE CustomerID = 123; -- 隐式转换：int->varchar
-- 优化：显式转换参数类型
SELECT * FROM Customers WHERE CustomerID = CAST(123 AS varchar(10));
-- 或修改应用程序，使用字符串参数
SELECT * FROM Customers WHERE CustomerID = '123';版本差异
SQL Server 2012及以前
- 执行计划图形化界面功能有限
- 缺少Query Store功能
- 统计信息更新机制相对简单
- 不支持自适应查询处理
SQL Server 2014
- 引入内存优化表和原生编译存储过程
- 增强了执行计划的可读性
- 改进了统计信息更新算法
SQL Server 2016
- 引入Query Store，用于跟踪和管理执行计划
- 支持实时查询统计信息
- 增强了执行计划的诊断信息
- 引入了自适应查询处理的初步功能
SQL Server 2017
- 增强了Query Store功能，支持自动计划修正
- 引入了自适应查询处理功能（如自适应连接、内存授予反馈）
- 执行计划中显示内存授予信息
- 支持图形化执行计划中的查询存储集成
SQL Server 2019
- 扩展了自适应查询处理功能
- 引入了智能查询处理功能
- 增强了Query Store，支持更多查询类型
- 执行计划中增加了更多诊断信息
SQL Server 2022
- 增强了执行计划的可视化效果
- Query Store支持更细粒度的查询捕获
- 引入了参数敏感计划优化
- 增强了自适应查询处理功能
- 支持执行计划中的更多统计信息
内置执行计划分析工具
生产场景示例
使用SSMS查看图形化执行计划，使用Query Store跟踪执行计划变化，使用DMV查询执行计划缓存。
实用命令
sql
-- 查询缓存中的执行计划
SELECT
cp.plan_handle,
qs.query_hash,
qs.query_plan_hash,
SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2 + 1) AS QueryText,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_cached_plans cp
WHERE qt.text LIKE '%SELECT%Orders%';
-- 清除单个查询的执行计划缓存
DBCC FREEPROCCACHE(plan_handle);
-- 清除所有执行计划缓存（谨慎使用）
DBCC FREEPROCCACHE;第三方执行计划分析工具
- SentryOne Plan Explorer：提供更强大的执行计划分析功能，支持执行计划比较和深入分析
- Redgate SQL Monitor：实时监控执行计划性能，自动识别问题查询
- ApexSQL Plan：可视化执行计划分析工具，支持执行计划的比较和优化建议
常见问题（FAQ）
如何查看执行计划？
解答：
- SSMS中：点击"显示估计的执行计划"或"包括实际的执行计划"按钮
- T-SQL：使用SET SHOWPLAN_XML ON（预估）或SET STATISTICS XML ON（实际）
- Azure Data Studio：使用执行计划可视化功能
- Query Store：查看历史执行计划
执行计划中出现警告怎么办？
解答：
- 隐式转换警告：检查数据类型是否匹配，避免不同数据类型之间的转换
- 缺少统计信息警告：更新统计信息或创建新的统计信息
- 书签查找警告：考虑创建覆盖索引，包含查询所需的所有列
- 排序警告：考虑添加适当的索引避免排序，或优化排序操作
- 内存授予警告：检查查询是否需要大量内存，考虑优化查询或增加内存
预估执行计划和实际执行计划有什么区别？
解答：
- 预估执行计划：不执行查询，基于统计信息生成，用于预先评估查询性能，适合生产环境中的慢查询初步分析
- 实际执行计划：执行查询后生成，包含实际执行统计信息，如实际行数、CPU时间、IO时间，适合深入分析查询性能问题
- 选择原则：对于生产环境中的复杂查询，先使用预估执行计划进行初步分析，然后在测试环境中使用实际执行计划进行深入分析
如何优化执行计划中的全表扫描？
解答：
- 为过滤条件创建合适的索引：根据查询的WHERE子句创建索引
- 确保统计信息最新：过时的统计信息可能导致SQL Server选择全表扫描
- 考虑使用分区表：对于大型表，使用分区表可以减少扫描范围
- 优化查询：减少返回的行数，使用更具选择性的过滤条件
- 考虑创建覆盖索引：如果查询需要返回大量列，创建覆盖索引可以避免全表扫描
执行计划中出现哈希匹配连接是否需要优化？
解答：
- 不一定：哈希匹配连接适合大结果集连接，在某些情况下是最优选择
- 优化建议：
- 如果可以通过添加索引将其转换为嵌套循环连接，可能会提高性能
- 检查是否可以减少连接的数据量，如添加过滤条件
- 优化连接顺序，将小结果集表放在前面
- 注意事项：不要盲目追求嵌套循环连接，哈希匹配连接在大结果集情况下可能更高效
如何使用Query Store分析执行计划变化？
解答：
- 启用Query Store：ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON;
- 在SSMS中打开Query Store报告：
- 右键点击数据库 → 报表 → 标准报表 → Query Store → 顶级资源消耗查询
- 查看查询的执行计划历史
- 比较执行计划：选择同一查询的不同执行计划，点击"比较计划"按钮
- 使用自动计划修正：如果发现执行计划退化，可以启用自动计划修正功能
实用命令
sql
-- 启用Query Store
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE);
-- 启用自动计划修正
ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE,
AUTO_PLAN_CORRECTION = ON);执行计划中的成本百分比是什么意思？
解答：
- 成本百分比：表示该操作在整个查询执行成本中所占的比例
- 成本计算：基于SQL Server的内部成本模型，考虑CPU成本和IO成本
- 实际意义：成本百分比越高，说明该操作对查询性能的影响越大
- 优化重点：优先优化成本百分比高的操作
- 注意事项：成本是相对值，不是实际执行时间，需要结合实际执行统计信息进行分析
如何解决执行计划不稳定的问题？
解答：
- 更新统计信息：确保统计信息准确，减少执行计划的变化
- 使用计划指南：锁定执行计划，防止执行计划退化
- 参数化查询：使用参数化查询，减少计划缓存膨胀
- 启用Query Store的自动计划修正：自动恢复到性能较好的执行计划
- 使用OPTION(RECOMPILE)：对于频繁变化的查询，强制每次执行重新生成执行计划
- 使用OPTION(OPTIMIZE FOR)：为特定参数值优化执行计划
实用示例
sql
-- 使用OPTION(RECOMPILE)
SELECT * FROM Orders WHERE OrderDate = @OrderDate OPTION(RECOMPILE);
-- 使用OPTION(OPTIMIZE FOR)
SELECT * FROM Orders WHERE OrderDate = @OrderDate OPTION(OPTIMIZE FOR (@OrderDate = '2023-01-01'));最佳实践
- 定期分析执行计划：对核心业务查询定期进行执行计划分析，建立执行计划基线
- 启用Query Store：在所有生产数据库中启用Query Store，便于跟踪执行计划变化
- 保持统计信息最新：建立统计信息维护作业，定期更新统计信息，特别是对于大型表和频繁变化的表
- 创建合适的索引：根据执行计划分析结果，创建或修改索引，避免过度索引
- 优化查询写法：避免低效的查询写法，如SELECT *、隐式转换、不必要的排序和聚合
- 监控执行计划警告：及时处理执行计划中的警告信息，如隐式转换、缺少统计信息
- 使用参数化查询：减少计划缓存膨胀，提高执行计划的重用率
- 培训开发人员：培训开发人员理解执行计划，编写高效的SQL语句，在开发阶段就考虑性能问题
- 建立慢查询监控机制：监控并分析慢查询，及时发现和解决性能问题
- 定期审查执行计划缓存：识别低效的执行计划，优化或清除它们
通过深入理解和分析执行计划，DBA可以快速定位查询性能问题，制定有效的优化策略，提高数据库系统的整体性能。执行计划分析是DBA进行性能优化的核心技能，需要不断学习和实践。