promethues监控慢查询的sql 如何排查慢sql

文章目录

• 一、系统表详解

• 1.1 dm_exec_query_stats
• 1.2 dm_exec_sql_text
• 1.3 dm_exec_requests

• 二、问SQL排查常用语句

对于SQL Server的慢SQL信息排查，我们可以利用一些系统自带的统计信息表来帮助我们进行问题SQL定位。

一、系统表详解

1.1 dm_exec_query_stats

该表记录了SQL Server中相关的执行计划的统计信息，从该表中我们可以从不同的维度分析是否存在一些资源消耗较大的SQL。

重点关注字段及其含义：

• sql_handle ： SQL唯一标识，可通过该信息在sys.dm_exec_sql_text中查看具体的SQL文本信息
• plan_handle ： SQL执行计划唯一标识，可通过该信息在sys.dm_exec_query_plan 中查看具体的SQL执行计划信息
• creation_time ： 执行计划编译时间
• last_execution_time ： 执行计划最近一次执行时间
• execution_count : 自编译以来执行次数
• total_worker_time ： 自编译以来执行消耗总CPU时间
• last_worker_time ： 自编译以来最近一次CPU耗时
• min_worker_time ： 自编译以来单次执行的最小CPU耗时
• max_worker_time ： 自编译以来单次执行的最大CPU时间
• total_physical_reads ： 自编译以来执行消耗总物理读次数
• total_logical_writes ： 自编译以来执行消耗总逻辑写次数
• total_logical_reads ：自编译以来执行消耗总逻辑读次数
• total_elapsed_time ：自编译以来执行总耗时
• total_rows ：查询返回的总行数

1.2 dm_exec_sql_text

根据sql_handle的标识来查看对应的SQL文本，查询语句如下：

sys.dm_exec_sql_text(sql_handle | plan_handle)

• dbid ：数据库唯一标识ID
• text ：SQL文本

1.3 dm_exec_requests

• session_id ： 会话ID
• start_time ： 会话开始时间
• status : 会话状态
• command : 会话执行命令类型
• sql_handle ： 会话执行SQL唯一标识
• database_id ： 数据库ID
• user_id ： 数据库用户ID
• blocking_session_id : 阻塞当前会话的会话ID，NULL或0表示当前会话未出现阻塞
• wait_type ： 若当前会话出现阻塞，阻塞等待的类型
• wait_time ： 阻塞等待时间
• wait_resource ： 等待资源信息
• transaction_id ： 事物ID
• percent_complete ： 某些维护操作完成百分比
• cpu_time ： 会话请求CPU时间消耗
• total_elapsed_time ： 会话请求耗时消耗
• reads ： 会话请求物理读消耗
• writes ： 会话请求写消耗
• logical_reads ： 会话请求逻辑读消耗
• transaction_isolation_level ： 事物隔离级别

二、问SQL排查常用语句

1、查找平均CPU时间消耗 TOP N 查询

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;

2、查找平均查询耗时 TOP N 查询

SELECT TOP 5 query_stats.query_hash AS "Query Hash",   
    SUM(query_stats.total_elapsed_time) / SUM(query_stats.execution_count) AS "Avg Query Time",  
    MIN(query_stats.statement_text) AS "Statement Text"  
FROM   
    (SELECT QS.*,   
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,  
    ((CASE statement_end_offset   
        WHEN -1 THEN DATALENGTH(ST.text)  
        ELSE QS.statement_end_offset END   
            - QS.statement_start_offset)/2) + 1) AS statement_text  
     FROM sys.dm_exec_query_stats AS QS  
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats  
GROUP BY query_stats.query_hash  
ORDER BY 2 DESC;

3、查看指定sql_handle对应的SQL语句

SELECT * FROM sys.dm_exec_sql_text(${sql_handle}) -- modify this value with your actual sql_handle

4、利用cross apply结合其他系统表查看问题SQL信息

select DB_NAME(t.dbid),t.text,r.status,r.start_time,r.command,r.session_id,r.wait_time,r.last_wait_type
from sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
where r.database_id=DB_ID('nuskincsm')