SQLServer教程FG105-SQLServer执行计划高级分析与优化实战

内容简介

本文章主要介绍SQLServer执行计划的高级分析与优化技术，包括执行计划的详细分析方法、性能瓶颈识别、优化策略等。风哥教程参考SQLServer官方文档Query Execution Plans、Performance Tuning等相关内容，将官方理论知识转化为生产实用技能，帮助DBA和开发人员快速定位和解决SQL性能问题。

目录大纲

Part01-基础概念与理论知识
Part02-生产环境规划与建议
Part03-生产环境项目实施方案
Part04-生产案例与实战讲解
Part05-风哥经验总结与分享

Part01-基础概念与理论知识

1.1 SQLServer执行计划基础概念

执行计划是SQLServer查询优化器生成的一种数据结构，用于描述SQL语句的执行方式。它包含了查询执行的详细步骤，如表扫描方式、连接顺序、索引使用等。执行计划是SQL性能分析和优化的核心工具，通过分析执行计划，我们可以识别性能瓶颈并采取相应的优化措施。

1.2 SQLServer执行计划类型与结构

SQLServer执行计划主要分为两种类型：

预估执行计划：在执行SQL语句前生成的计划，基于统计信息和索引信息估算执行成本
实际执行计划：SQL语句执行后生成的计划，包含实际执行的统计信息，如实际行数、执行时间等

执行计划的结构主要包括：

操作符：执行计划的基本单元，如扫描、连接、排序等
属性：操作符的具体参数，如扫描类型、连接类型等
执行统计：实际执行的统计信息，如执行时间、CPU使用等

1.3 SQLServer执行计划操作符分析

常见的执行计划操作符包括：

扫描操作符：表扫描、索引扫描、聚集索引扫描等
查找操作符：索引查找、聚集索引查找等
连接操作符：嵌套循环连接、哈希连接、合并连接等
排序操作符：排序、顶部排序等
聚合操作符：流聚合、哈希聚合等

每个操作符都有其特定的性能特征，了解这些操作符的工作原理对于分析执行计划至关重要。

Part02-生产环境规划与建议

2.1 执行计划分析环境规划

在生产环境中进行执行计划分析需要合理规划环境，包括：

测试环境：与生产环境配置相似的测试环境，用于执行计划分析和优化测试
工具配置：配置SQL Server Management Studio (SSMS)、SQL Server Profiler等工具
权限设置：确保分析人员有足够的权限查看执行计划和相关系统视图

2.2 执行计划捕获策略建议

执行计划捕获策略应考虑以下因素：

捕获时机：在SQL语句执行前和执行后都需要捕获执行计划
捕获方式：使用SSMS、SQL Server Profiler、Query Store等工具
存储策略：将执行计划存储在数据库中，便于后续分析和比较

2.3 执行计划存储与管理建议

执行计划的存储与管理应注意：

存储位置：使用Query Store或自定义表存储执行计划
保留策略：根据业务需求设置执行计划的保留时间
定期清理：定期清理过期的执行计划，避免占用过多存储空间

Part03-生产环境项目实施方案

3.1 执行计划分析工具配置

# 启用Query Store
ALTER DATABASE fgedudb SET QUERY_STORE = ON;
GO

# 配置Query Store
ALTER DATABASE fgedudb SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 10240,
INTERVAL_LENGTH_MINUTES = 15,
QUERY_CAPTURE_MODE = AUTO,
MAX_PLANS_PER_QUERY = 200,
WAIT_STATS_CAPTURE_MODE = ON
);
GO

3.2 执行计划捕获与存储实现

# 创建执行计划存储表
CREATE TABLE dbo.fgedu_execution_plans (
plan_id INT PRIMARY KEY IDENTITY,
query_text NVARCHAR(MAX) NOT NULL,
plan_xml XML NOT NULL,
capture_time DATETIME NOT NULL DEFAULT GETDATE(),
execution_count INT NOT NULL DEFAULT 1,
avg_execution_time_ms FLOAT NOT NULL,
max_execution_time_ms FLOAT NOT NULL,
min_execution_time_ms FLOAT NOT NULL
);
GO

# 创建索引
CREATE INDEX IX_fgedu_execution_plans_capture_time ON dbo.fgedu_execution_plans(capture_time);
GO

# 捕获执行计划存储过程
CREATE PROCEDURE dbo.usp_capture_execution_plan
@query NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @plan_handle VARBINARY(64);
DECLARE @sql NVARCHAR(MAX);

— 执行查询并捕获计划
SET @sql = N’EXEC sp_executesql N”’ + REPLACE(@query, ””, ”””) + ””;
EXEC sp_executesql @sql;

— 获取计划句柄
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE ‘%’ + REPLACE(@query, ””, ”””) + ‘%’
ORDER BY qs.last_execution_time DESC;

— 存储执行计划
IF @plan_handle IS NOT NULL
BEGIN
INSERT INTO dbo.fgedu_execution_plans (
query_text, plan_xml, avg_execution_time_ms, max_execution_time_ms, min_execution_time_ms
)
SELECT
@query,
query_plan,
qs.avg_elapsed_time / 1000.0,
qs.max_elapsed_time / 1000.0,
qs.min_elapsed_time / 1000.0
FROM sys.dm_exec_query_plan(@plan_handle) p
CROSS APPLY sys.dm_exec_query_stats qs
WHERE qs.plan_handle = @plan_handle;
END;
END;
GO

3.3 执行计划分析流程实施

# 执行计划分析流程

# 1. 捕获执行计划
EXEC dbo.usp_capture_execution_plan @query = N’SELECT * FROM dbo.fgedu_users WHERE user_id = 1′;
GO

# 2. 分析执行计划
SELECT
plan_id,
query_text,
capture_time,
avg_execution_time_ms,
max_execution_time_ms,
min_execution_time_ms
FROM dbo.fgedu_execution_plans
ORDER BY capture_time DESC;
GO

# 3. 查看执行计划XML
SELECT
plan_id,
plan_xml
FROM dbo.fgedu_execution_plans
WHERE plan_id = 1;
GO

Part04-生产案例与实战讲解

4.1 执行计划分析实战案例

# 创建测试表
CREATE TABLE dbo.fgedu_sales (
sales_id INT PRIMARY KEY IDENTITY,
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL,
region NVARCHAR(50) NOT NULL,
salesperson NVARCHAR(100) NOT NULL
);
GO

# 插入测试数据
INSERT INTO dbo.fgedu_sales (
product_id, customer_id, sale_date, quantity, unit_price, total_amount, region, salesperson
) VALUES
(1, 1, ‘2023-01-01’, 10, 100.00, 1000.00, ‘North’, ‘John’),
(2, 2, ‘2023-01-02’, 5, 200.00, 1000.00, ‘South’, ‘Jane’),
(3, 3, ‘2023-01-03’, 15, 50.00, 750.00, ‘East’, ‘Bob’),
(4, 4, ‘2023-01-04’, 8, 150.00, 1200.00, ‘West’, ‘Alice’),
(5, 5, ‘2023-01-05’, 12, 80.00, 960.00, ‘North’, ‘John’);
GO

# 执行查询并查看执行计划
SELECT * FROM dbo.fgedu_sales WHERE region = ‘North’;
GO

# 执行计划输出
/*
|–Clustered Index Scan(OBJECT:([fgedudb].[dbo].[fgedu_sales].[PK__fgedu_sa__3214EC07A1234567]), WHERE:([fgedudb].[dbo].[fgedu_sales].[region]=N’North’))
*/

# 创建索引
CREATE INDEX IX_fgedu_sales_region ON dbo.fgedu_sales(region);
GO

# 再次执行查询并查看执行计划
SELECT * FROM dbo.fgedu_sales WHERE region = ‘North’;
GO

# 执行计划输出
/*
|–Index Seek(OBJECT:([fgedudb].[dbo].[fgedu_sales].[IX_fgedu_sales_region]), SEEK:([fgedudb].[dbo].[fgedu_sales].[region]=N’North’) ORDERED FORWARD)
*/

4.2 执行计划优化实战案例

# 原始查询
SELECT
s.sales_id,
s.product_id,
s.customer_id,
s.sale_date,
s.quantity,
s.unit_price,
s.total_amount,
s.region,
s.salesperson
FROM dbo.fgedu_sales s
WHERE s.sale_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’
AND s.region = ‘North’
ORDER BY s.sale_date DESC;
GO

# 执行计划分析
/*
|–Sort(ORDER BY:([fgedudb].[dbo].[fgedu_sales].[sale_date] DESC))
|–Clustered Index Scan(OBJECT:([fgedudb].[dbo].[fgedu_sales].[PK__fgedu_sa__3214EC07A1234567]), WHERE:([fgedudb].[dbo].[fgedu_sales].[sale_date] >= ‘2023-01-01’ AND [fgedudb].[dbo].[fgedu_sales].[sale_date] <= '2023-01-31' AND [fgedudb].[dbo].[fgedu_sales].[region]=N'North')) */ # 创建复合索引 CREATE INDEX IX_fgedu_sales_region_sale_date ON dbo.fgedu_sales(region, sale_date DESC); GO # 优化后查询 SELECT s.sales_id, s.product_id, s.customer_id, s.sale_date, s.quantity, s.unit_price, s.total_amount, s.region, s.salesperson FROM dbo.fgedu_sales s WHERE s.sale_date BETWEEN '2023-01-01' AND '2023-01-31' AND s.region = 'North' ORDER BY s.sale_date DESC; GO # 执行计划分析 /* |--Index Seek(OBJECT:([fgedudb].[dbo].[fgedu_sales].[IX_fgedu_sales_region_sale_date]), SEEK:([fgedudb].[dbo].[fgedu_sales].[region]=N'North' AND [fgedudb].[dbo].[fgedu_sales].[sale_date] >= ‘2023-01-01’ AND [fgedudb].[dbo].[fgedu_sales].[sale_date] <= '2023-01-31'), ORDERED FORWARD) */

4.3 执行计划回归测试案例

# 创建执行计划回归测试存储过程
CREATE PROCEDURE dbo.usp_execution_plan_regression_test
@query NVARCHAR(MAX),
@threshold_ms INT = 1000
AS
BEGIN
SET NOCOUNT ON;

DECLARE @start_time DATETIME;
DECLARE @end_time DATETIME;
DECLARE @execution_time_ms INT;

— 执行查询并记录时间
SET @start_time = GETDATE();
EXEC sp_executesql @query;
SET @end_time = GETDATE();
SET @execution_time_ms = DATEDIFF(MS, @start_time, @end_time);

— 检查执行时间是否超过阈值
IF @execution_time_ms > @threshold_ms
BEGIN
PRINT ‘执行计划回归测试失败：执行时间超过阈值’;
PRINT ‘执行时间：’ + CAST(@execution_time_ms AS NVARCHAR(10)) + ‘ms’;
PRINT ‘阈值：’ + CAST(@threshold_ms AS NVARCHAR(10)) + ‘ms’;

— 捕获执行计划
EXEC dbo.usp_capture_execution_plan @query = @query;
END
ELSE
BEGIN
PRINT ‘执行计划回归测试通过：执行时间在阈值范围内’;
PRINT ‘执行时间：’ + CAST(@execution_time_ms AS NVARCHAR(10)) + ‘ms’;
PRINT ‘阈值：’ + CAST(@threshold_ms AS NVARCHAR(10)) + ‘ms’;
END;
END;
GO

# 执行回归测试
EXEC dbo.usp_execution_plan_regression_test
@query = N’SELECT * FROM dbo.fgedu_sales WHERE region = ”North”’,
@threshold_ms = 500;
GO

# 测试结果
/*
执行计划回归测试通过：执行时间在阈值范围内
执行时间：15ms
阈值：500ms
*/

Part05-风哥经验总结与分享

5.1 执行计划分析最佳实践

定期分析：定期分析执行计划，特别是高频查询和性能瓶颈查询
比较分析：比较不同执行计划的性能差异，找出最优执行计划
索引优化：根据执行计划分析结果，合理创建和优化索引
统计信息维护：定期更新统计信息，确保优化器生成准确的执行计划
查询重写：根据执行计划分析结果，重写复杂查询以提高性能

5.2 执行计划优化常见问题

索引缺失：执行计划中出现表扫描，说明缺少合适的索引
统计信息过期：执行计划中估算行数与实际行数差异较大，说明统计信息过期
参数嗅探：同一查询在不同参数下执行计划不同，可能是参数嗅探导致
查询复杂度过高：执行计划中出现大量操作符，说明查询过于复杂
连接方式选择不当：执行计划中使用了不合适的连接方式，如哈希连接在小表上的使用

5.3 执行计划监控与维护建议

启用Query Store：使用Query Store跟踪和管理执行计划
建立执行计划基线：为关键查询建立执行计划基线，定期比对
自动化监控：使用脚本或工具自动化监控执行计划的变化
定期审查：定期审查执行计划，及时发现和解决问题
知识共享：建立执行计划分析知识库，共享经验和最佳实践

更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

学习交流加群风哥QQ113257174

风哥提示：执行计划分析是SQL性能优化的核心工具，掌握执行计划分析技巧对于提升数据库性能至关重要。

更多学习教程公众号风哥教程itpux_com

from SQLServer视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html