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

俊达3年前技术文章1822

说明

从会话信息中我们可以查看实例当前正在运行的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是否执行过

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-1.png


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


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

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

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

计算差值的方法

-- 记录当前值
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，分析是否存在事物锁定问题。分析系统资源是否有瓶颈。


返回列表

上一篇：SQL Server优化入门系列（三）—— 性能计数器(performance counter)

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

相关文章

MySQL 添加列报错处理

MySQL 添加列报错处理

一、添加列报错(65535)表添加列收到报错，具体 SQL 和报错信息如下：ALTER TABLE table ADD column varchar(256) NULL COMMENT '个人打款授权...

gin框架连接mysql数据库连接池泄露

gin框架连接mysql数据库连接池泄露

1、故障爆发12月1号上午10点出头，我们收到阿里云监控告警：客户官网探测异常，如图所示：然后我们DBA查看了后端数据库实例，发现数据库连接已经被用尽了，导致服务出现异常，如图所示：当时我们和客户协商...

TCP短连接和长连接

TCP短连接和长连接

     当网络通信时采用TCP协议时，在真正的读写操作之前，server与client之间必须建立一个连接，当读写操作完成后，双方不再需要这个连接时它们可以释放这个连接，连接的建立是需要三次握手的，...

CDP实操--集群扩容

CDP实操--集群扩容

一、前提准备工作1.确保OS的yum源可以正常使用，通过yum repolist命令可以查看到匹配的OS的所有包2.确保Cloudera Manager的yum源运行正常3.hosts文件配置，需要将...

zabbix监控华为存储设备

zabbix监控华为存储设备

确认监控方式开始监控之前首先思考确认好要监控的方式。提出疑问：zabbix 监控华为存储设备推荐使用snmptrap还是snmptt呢？回答：在 Zabbix 监控华为存储设备时，您可以选择使用 SN...

可观测未来OpenTelemertry-结构化数据价值

可观测未来OpenTelemertry-结构化数据价值

前言开源软件和云供应商的软件开发模式已经改变了我们构建和部署软件的方式。集成开源软件，我们可以在很短时间内构建和部署一个应用程序。但这并不意味着使用和维护它们也变得更简单，随着应用程序的扩充，程序的调...

发表评论    

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号