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

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

数栈君发表于 2024-02-19 11:10 358 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%'

查找消耗时间最长的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

查找阻塞时间最长的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

查找逻辑读最高的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

查找执行次数最多的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;

计算差值的方法

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

Postgresql mysql事务 mysql索引 sql server cdc sqlserver SQL语法 sqlparser flinksql mysql sql

0条评论

上一篇：元数据备份与恢复

下一篇：远程DEBUG HADOOP源码方法

我要提问

分享经验

社区公告

大数据领域最专业的产品&技术交流社区，专注于探讨与分享大数据领域有趣又火热的信息，专业又专注的数据人园地

最新活动更多