博客 SQL Server优化入门系列—— 找到Top SQL

SQL Server优化入门系列—— 找到Top SQL

   数栈君   发表于 2024-02-19 11:10  98  0

说明

从会话信息中我们可以查看实例当前正在运行的SQL、当前被Block的SQL。但是如果要查看过去一段时间执行的SQL,我们有几个选择

  • 1、通过外部SQL审计平台记录所有SQL。

  • 2、通过SQL Server的SQL Profile、extended events等功能采集。

  • 3、通过sys.dm_exec_query_stats、sys.dm_exec_sql_text等DMV查看SQL执行情况

本文介绍如何使用DMV分析SQL执行情况。

SQL执行统计介绍

SQL Server执行完成一个SQL后,会在sys.dm_exec_query_stats中记录执行的相关信息,比较重要的信息如下表:

字段名称

描述

sql_handle

SQL的唯一标识。可用于关联sys.dm_exec_sql_text

statement_start_offset

查询在文本中的开始位置(单位是字节)

statement_end_offset

查询在文本中的结束位置(单位是字节)

plan_handle

执行计划标识。可关联sys.dm_exec_query_plan

creation_time

编译计划的时间

last_execution_time

最近一次执行时间

execution_count

累计执行次数

total_worker_time

CPU时间(单位是微秒)

total_physical_reads

物理读取总数

total_logical_reads

逻辑读取总数

total_logical_writes

逻辑写入次数

total_clr_time

clr时间(单位微秒)

total_elapsed_time

消耗总时间

total_rows

总行数

上述统计信息,除了total开头,还有:

  • last_xxx: 最后一次执行的数据

  • min_xxx: 最小一次的数据

  • max_xxx: 最大一次的数据

使用这个视图,有几点需要注意:

  • 1、total_xxx记录的是自SQL编译后,所有执行的汇总情况。如果要查看一段时间内的SQL执行情况,需要记录开始和结束的时间点的相关数据,并计算差值。

  • 2、当SQL Server实例重启后,内存中的数据都会清空。

  • 3、即使SQL Server实例没有重启,也可能出现由于内存紧张或其他原因(如使用dbcc命令清理缓存)导致缓存的SQL被清理掉。

  • 4、SQL执行完成后才会记录,正在执行中的SQL不会记录。


分析SQL

查找某个SQL是否执行过

SQL
SELECT TOP 20
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 [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE 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)
LIKE '%insert into dbo.deal%'

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/4bcd5c92ad76a5b1b68ff1fea45ec4ab..png

查找消耗时间最长的SQL

SQL
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]
, 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 [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/8c35cbe7910eaa03a108ad8071c88760..png

查找阻塞时间最长的SQL

SQL
SELECT TOP 20
CAST((qs.total_elapsed_time - qs.total_worker_time) /
1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28,2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0
/ qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)]
, 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 [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/6a1ad892d702f63935da772a1394c80e..png

查找逻辑读最高的SQL

SQL
SELECT TOP 20
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, 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 [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total IO] DESC

http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/70947eab79be36901c1ee512b38f2657..png

查找执行次数最多的SQL

SQL
SELECT TOP 20
qs.execution_count
, 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 [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.execution_count DESC;


http://dtstack-static.oss-cn-hangzhou.aliyuncs.com/2021bbs/files_user1/article/5340e4a221c1ca4cee66c7bb877490cb..png

计算差值的方法

SQL
-- 记录当前值
SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time,
total_logical_reads, total_logical_writes, total_physical_reads,
total_rows, total_clr_time, execution_count,
statement_start_offset, statement_end_offset
INTO #PreWorkSnapShot
FROM sys.dm_exec_query_stats



-- 等一段时间


-- 再次记录当前值
SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time,
total_logical_reads, total_logical_writes, total_physical_reads,
total_rows, total_clr_time, execution_count,
statement_start_offset, statement_end_offset
INTO #PostWorkSnapShot
FROM sys.dm_exec_query_stats


-- 计算2个快照的差值
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkSnapShot p1
RIGHT OUTER JOIN
#PostWorkSnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset) AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [Duration] DESC


-- 清理临时表
DROP TABLE #PreWorkSnapShot
DROP TABLE #PostWorkSnapShot

SQL优化的基本思路

  • 找到待优化的TOP SQL。一般先优化总逻辑读最高的SQL。

  • 逻辑读返回行数比例高,并且没有使用聚合函数的SQL,通常有较大的优化空间。

  • 执行次数特别高的SQL,分析是否可使用外部缓存(如缓存到redis)。

  • 对于阻塞时间特别高的SQL,分析是否存在事物锁定问题。分析系统资源是否有瓶颈。


《数栈V6.0产品白皮书》下载地址:https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs

同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack  
0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群