SQL优化 之 执行计划分析（sql server 数据库）

执行计划是SQL Server查询优化器生成的指令集，描述了如何执行查询。通过分析执行计划，可以识别性能瓶颈并进行优化。

一、获取执行计划

1. 图形化执行计划

-- 在SSMS中选中查询后使用快捷键或菜单
-- 快捷键: Ctrl+M (预估执行计划), Ctrl+L (实际执行计划)

2. 文本/XML格式执行计划

-- 文本格式
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM YourTable;
GO
SET SHOWPLAN_TEXT OFF;

-- XML格式
SET SHOWPLAN_XML ON;
GO
SELECT * FROM YourTable;
GO
SET SHOWPLAN_XML OFF;

3. 动态管理视图(DMV)获取历史执行计划

SELECT qp.query_plan, qt.text, qs.*
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
WHERE qt.text LIKE '%YourQuery%';

二、执行计划关键元素分析

1. 主要操作符

• 表扫描(Table Scan): 全表扫描，通常需要优化

• 索引扫描(Index Scan): 扫描整个索引

• 索引查找(Index Seek): 高效索引访问

• 键查找(Key Lookup): 书签查找，可能导致性能问题

• 哈希匹配(Hash Match): 连接或聚合操作

• 排序(Sort): 内存消耗大

• 并行(Parallelism): 可能表示复杂查询

2. 关键指标

• 预估行数 vs 实际行数: 差异大说明统计信息不准确

• 操作成本百分比: 高成本操作是优化重点

• 内存授予: 过多内存可能浪费资源

• I/O成本: 高I/O操作需要关注

三、常见性能问题及优化方案

1. 缺失索引

-- 执行计划中会提示缺失索引
-- 示例创建索引
CREATE INDEX IX_YourTable_Column ON YourTable(Column1, Column2)
INCLUDE (Column3, Column4);

2. 索引使用不当

• 场景: 索引扫描代替索引查找

• 优化:

  • 确保查询条件与索引键匹配

  • 考虑创建覆盖索引

3. 键查找(书签查找)问题

• 解决方案:

  • 创建覆盖索引

  • 减少SELECT列数

4. 统计信息过期

-- 更新统计信息
UPDATE STATISTICS YourTable WITH FULLSCAN;

5. 参数嗅探问题

-- 解决方案1: 使用本地变量
DECLARE @Param INT = 1;
SELECT * FROM YourTable WHERE Column = @Param;

-- 解决方案2: 使用查询提示
SELECT * FROM YourTable WHERE Column = @Param
OPTION (OPTIMIZE FOR UNKNOWN);

-- 解决方案3: 使用计划指南

四、高级分析技巧

1. 使用执行计划比较

• 在SSMS中保存不同版本查询的执行计划进行比较

2. 分析等待统计

SELECT * FROM sys.dm_exec_session_wait_stats
WHERE session_id = @@SPID;

3. 使用Query Store分析历史执行计划

-- 启用Query Store
ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

4. 使用扩展事件跟踪执行计划问题

-- 创建扩展事件会话跟踪性能问题

五、优化最佳实践

1. 遵循SARGable原则: 确保查询条件可有效使用索引

   • 避免在列上使用函数

   • 避免隐式转换

2. 合理设计索引:

   • 选择性高的列放在索引前面

   • 考虑包含列减少键查找

3. 批处理优化:

   • 减少网络往返

   • 使用表变量或临时表处理中间结果

4. 避免游标: 尽量使用集合操作代替

5. 定期维护:

   • 更新统计信息

   • 重建/重组索引

   • 检查数据库碎片

通过系统性地分析执行计划并结合这些优化技术，可以显著提高SQL Server查询性能。