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

俊达2年前技术文章1459

说明

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

相关文章

JMS 介绍

JMS 介绍

一、JMS的基础JMS是什么：JMS是Java提供的一套技术规范JMS干什么用：用来异构系统 集成通信，缓解系统瓶颈，提高系统的伸缩性增强系统用户体验，使得系统模块化和组件化变得可行并更加灵活通过什么...

理解YAML文件

YAML 基础它的基本语法规则如下：大小写敏感使用缩进表示层级关系缩进时不允许使用Tab键，只允许使用空格。缩进的空格数目不重要，只要相同层级的元素左侧对齐即可# 表示注释，从这个字符一直到行尾，都会...

scylladb简单命令介绍

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

某系统被入侵挖矿排查案例

某系统被入侵挖矿排查案例

1、当时的出现的情况是：执行 top、w、netstat命令的时候，会出现卡住的情况，无法正常使用2、我们上传新的top命令之后，只是看到CPU使用率比较高，但是看不到占用CPU的异常进程3、我们通过...

oracle adg切换

1 主库转备库注意：原则上至多只能有一个主库，但是备库可以多个，所以需要先进行主库转备库操作。1.1 查看主库角色切换状态SYS@proe>select name,database_role,s...

ES运维（八）添加IK分词器

ES运维（八）添加IK分词器

一、概述ES自带standard analyzer、simple analyzer、whitespace analyzer、stop analyzer、language analyzer、patter...

发表评论    

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

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号