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

俊达3年前技术文章1821

说明

从会话信息中我们可以查看实例当前正在运行的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源码方法

相关文章

scylladb简单命令介绍

一、cql 命令查看版本SHOW VERSION查看连接的数据库地址SHOW HOST批量执行.cql文件中的命令SOURCE '/home/thobbs/commands.cql'开启和关闭命令追踪...

GitLab 变量的介绍与使用

环境变量是一个动态命名的值，它可以影响正在运行的进程在操作系统上的行为方式。一、环境变量分类在 GitLab CI/CD 中，环境变量分为两部分：预定义环境变量自定义环境变量1.1 预定义环境变量Gi...

strace

一、简介strace常用来跟踪进程执行时的系统调用和所接收的信号。 在Linux世界，进程不能直接访问硬件设备，当进程需要访问硬件设备(比如读取磁盘文件，接收网络数据等等)时，必须由用户态模式切换至内...

cattle-cluster-agent产生的僵尸进程处理

cattle-cluster-agent产生的僵尸进程处理

现象zabbix告警服务器存在僵尸进程排查步骤1、登陆服务器使用top命令，确认下僵尸进程数量。2、使用ps -aux ｜ grep Z命令，过滤出僵尸进程PID。3、根据查到的PID，借助ps命令，...

副本集同步原理

一、Initial Sync大体来说，MongoDB副本集同步主要包含两个步骤：1. Initial Sync，全量同步2. Replication，即sync oplog先通过init sync同步...

CDH实操--配置Kerberos服务高可用（一）

CDH实操--配置Kerberos服务高可用（一）

前置条件1.主Kerberos已安装并与CDH集成2.备节点安装Kerberos服务yum -y install krb5-server krb5-libs krb5-auth-dialog krb5...

发表评论    

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

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号