启用SQL Server RDS慢日志收集功能(阈值设置为3秒),并运行几天后分析收集到的慢日志,对慢日志进行分类整理,找出执行频率高且耗时长的SQL进行分析。
--如下所示,执行的SQL并不复杂,只有一个主从表关联,但是总共执行了4870次,总耗时2650秒
declare @p1 int
set @p1=180263033
declare @p3 int
set @p3=16
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT "T0"."FID" "ID", "T0"."FGROUPID" "T1.ID" FROM "T_HR_ATS_ATTENDANCELOGENTRY" "T0" INNER JOIN "T_HR_ATS_ATTENDANCELOG" "T1" ON "T0"."FGROUPID" = "T1"."FID" WHERE "T1"."FID" = @P0 ORDER BY "T1.ID" ASC, "T0"."FSEQ" ASC',@p3 output,@p4 output,@p5 output,N'@P0 varchar(8000)','O9AAAACZJT4V4uUB'
select @p1, @p3, @p4, @p5
分析SQL的执行计划,存在明显的全表扫描(关联字段未加索引):
Storage RowStore
Estimated Operator Cost 104.766 (99%)
Estimated I/O Cost 102.076
Estimated CPU Cost 2.69061
Estimated Subtree Cost 104.766
Estimated Number of Executions 1
Estimated Number of Rows to be Read 9.78391e+006
Estimated Number of Rows 1391.81
Estimated Row Size 45 B
给对应字段增加索引:
CREATE NONCLUSTERED INDEX INDEX_ATTLOGENTRY_INDEX3 ON [dbo].[T_HR_ATS_AttendanceLogEntry] ([FGroupID])
重新查看执行计划,分析执行占比最大的节点执行情况,CPU Cost从2.69061
下降到了0.0032308,总耗时从2650秒下降到15秒:
Physical Operation Index Seek
Logical Operation Index Seek
Estimated Execution Mode Row
Storage RowStore
Estimated Operator Cost 0.0175073 (54%)
Estimated I/O Cost 0.0142766
Estimated CPU Cost 0.0032308
Estimated Subtree Cost 0.0175073
Estimated Number of Executions 1
Estimated Number of Rows to be Read 2794.34
Estimated Number of Rows 2794.34
Estimated Row Size 51 B
推荐阅读