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

俊达3年前技术文章1661

说明

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

相关文章

Presto临时设置session超时时间

Presto临时设置session超时时间

全局默认超时时间为200s：测试语句：select    "t7"."__fcol_16" "__fcol_22",    date_trunc('month', "t7"."__fcol_21"...

kubernetes调度和调度器

一、Kubernetes调度Scheduler 是 kubernetes 的调度器，主要的任务是把定义的 pod 分配到集群的节点上。听起来非常简单，但有很多要考虑的问题：公平：如何保证每个节点都能被...

MySQL 组复制一致性保证

MySQL 组复制一致性保证

说明本篇文章介绍，MySQL 组复制作为一个分布式系统，如何保证事务一致性？1. 一致性级别MySQL 8.0.14 版本开始，提供组复制变量：group_replication_consistenc...

oracle11g打补丁31537677验证报错

环境：操作系统： Kylin Linux Advanced Server V10数据库：oracle 11.2.0.4问题描述：打补丁31537677/时候补丁集对.so文件的验证报错$ORACLE_...

spark配置任务日志（Client模式& Cluster模式）

在Spark中，日志级别可以通过log4j.properties或log4j.xml文件来配置。对于spark-submit命令启动的应用程序，可以通过以下两种方式来修改日志级别：对于Client模式...

Linux高并发Nginx配置优化

在日常的运维工作中，经常会用到nginx服务，也时常会碰到nginx因高并发导致的性能瓶颈问题。一般来说nginx配置文件中对优化比较有作用的主要有以下几项：1）nginx进程数，建议按照cpu数目来...

发表评论    

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

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号