mssql查询占用CPU过高进程及语句

aoaoIT技术管理 > 数据库中间件 > MSSQL数据库 mssql sql server
发布时间 :
阅读 :
  1. 参考资料
  2. 查询占用CPU过高进程
  3. 查询占用CPU高的语句
  4. 查找最新的30条告警事件
  5. 一、查看当前的数据库用户连接有多少
  6. 二、选取前10个最耗CPU时间的会话
  7. 三、查询前10个最耗CPU时间的SQL语句
  8. 四、查询会话中有多少个worker在等待
  9. 五、查询CPU占用高的语句
  10. 1.找出执行时间最长的10条SQL（适用于SQL SERVER 2005及其以上版本）
  11. 2.找出平均消耗CPU最多的前个SQL （SQL SERVER 2005或以上版本）
  12. 3.平均耗CPU最多的前20个SQL （SQL SERVER 2008或以上版本）
  13. 4.总耗CPU最多的前20个SQL （SQL SERVER 2008或以上版本）

妈的服务器老是经常半夜CPU过高报警，得查下

参考资料

https://www.modb.pro/db/210369

https://www.cnblogs.com/zhaoshujie/p/9594719.html

查询占用CPU过高进程

SELECT r.session_id, r.start_time, r.status, r.command, r.cpu_time, s.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.cpu_time > 0
ORDER BY r.cpu_time DESC;

查询占用CPU高的语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

查找最新的30条告警事件

select top 30  a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.Name as AddrName,b.Name as MgrObjName,a.Ch,a.Value,a.Content,a.Level
    ,ag.Name as AgentServerName,a.EventBm,a.MgrObjId,a.Id,a.Cfmoper,a.Cfm,a.Cfmtime,a.State,a.IgnoreStartTime,a.IgnoreEndTime
    ,a.OpUserId,d.Name as MgrObjTypeName,l.UserName as userName,f.Name as AddrName2 
from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
    left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
    left join eventdir as e on a.EventBm=e.Bm left join agentserver as ag on a.AgentBm=ag.AgentBm 
    left join loginUser as l on a.cfmoper=l.loginGuid left join addrnode as f on ag.AddrId=f.Id 
where ((MgrObjId in (
    select Id from MgrObj 
    where AddrId in ('','02100000','02113000','02113001','02113002','02113003','02113004'
        ,'02113005','02113006','02113007','02113008','02113009','02113010','02113011','02113012'
        ,'02113013','02113014','02113015','02113016','02113017','02113018','02113019','02113020'
        ,'02113021','02113022','02113023','02113024','02113025','02113026'))) 
    or (mgrobjid in ('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000'
        ,'00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111'
        ,'11111111-1111-1111-1111-111111111111'))
    ) 
order by alarmtime DESC

一、查看当前的数据库用户连接有多少

USE master

GO
SELECT *
FROM sys.[sysprocesses]
WHERE [spid] > 50
–AND DB_NAME([dbid])=’gposdb’

SELECT COUNT(*)
FROM [sys].[dm_exec_sessions]
WHERE [session_id] > 50

二、选取前10个最耗CPU时间的会话

SELECT TOP 10
[session_id] ,
[request_id] ,
[start_time] AS ‘开始时间’ ,
[status] AS ‘状态’ ,
[command] AS ‘命令’ ,
dest.[text] AS ‘sql语句’ ,
DB_NAME([database_id]) AS ‘数据库名’ ,
[blocking_session_id] AS ‘正在阻塞其他会话的会话ID’ ,
[wait_type] AS ‘等待资源类型’ ,
[wait_time] AS ‘等待时间’ ,
[wait_resource] AS ‘等待的资源’ ,
[reads] AS ‘物理读次数’ ,
[writes] AS ‘写次数’ ,
[logical_reads] AS ‘逻辑读次数’ ,
[row_count] AS ‘返回结果行数’
FROM sys.[dm_exec_requests] AS der
CROSS APPLY sys.dm_exec_sql_text AS dest
WHERE [session_id] > 50
AND DB_NAME(der.[database_id]) = ‘gposdb’
ORDER BY [cpu_time] DESC

三、查询前10个最耗CPU时间的SQL语句

SELECT TOP 10
dest.[text] AS ‘sql语句’
FROM sys.[dm_exec_requests] AS der
CROSS APPLY sys.dm_exec_sql_text AS dest
WHERE [session_id] > 50
ORDER BY [cpu_time] DESC

四、查询会话中有多少个worker在等待

SELECT TOP 10
[session_id] ,
[request_id] ,
[start_time] AS ‘开始时间’ ,
[status] AS ‘状态’ ,
[command] AS ‘命令’ ,
dest.[text] AS ‘sql语句’ ,
DB_NAME([database_id]) AS ‘数据库名’ ,
[blocking_session_id] AS ‘正在阻塞其他会话的会话ID’ ,
der.[wait_type] AS ‘等待资源类型’ ,
[wait_time] AS ‘等待时间’ ,
[wait_resource] AS ‘等待的资源’ ,
[dows].[waiting_tasks_count] AS ‘当前正在进行等待的任务数’ ,
[reads] AS ‘物理读次数’ ,
[writes] AS ‘写次数’ ,
[logical_reads] AS ‘逻辑读次数’ ,
[row_count] AS ‘返回结果行数’
FROM sys.[dm_exec_requests] AS der
INNER JOIN [sys].[dm_os_wait_stats] AS dows ON der.[wait_type] = [dows].[wait_type]
CROSS APPLY sys.dm_exec_sql_text AS dest
WHERE [session_id] > 50
ORDER BY [cpu_time] DESC

五、查询CPU占用高的语句

SELECT TOP 10
total_worker_time / execution_count AS avg_cpu_cost ,
plan_handle ,
execution_count ,
( SELECT SUBSTRING(text, statement_start_offset / 2 + 1,
( CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), text))
* 2
ELSE statement_end_offset
END - statement_start_offset ) / 2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

1.找出执行时间最长的10条SQL（适用于SQL SERVER 2005及其以上版本）

SELECT
    (total_elapsed_time / execution_count)/1000 N'平均时间ms'
    ,total_elapsed_time/1000 N'总花费时间ms'
    ,total_worker_time/1000 N'所用的CPU总时间ms'
    ,total_physical_reads N'物理读取总次数'
    ,total_logical_reads/execution_count N'每次逻辑读次数'
    ,total_logical_reads N'逻辑读取总次数'
    ,total_logical_writes N'逻辑写入总次数'
    ,execution_count N'执行次数'
    ,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) N'执行语句'
    ,creation_time N'语句编译时间'
    ,last_execution_time N'上次执行时间'
    ,qp.query_plan
    FROM
    sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st 
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
    WHERESUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END- qs.statement_start_offset)/2) + 1) not like 'fetch%'ORDER BY
total_elapsed_time / execution_count DESC;

如果想对SQL作筛选，可以更换not like ‘%fetch%’ 部分，如将not like ‘%fetch%’ 换成 like ‘%user%’就可以找出SQL语句中含有user关键字的SQL 。

2.找出平均消耗CPU最多的前个SQL （SQL SERVER 2005或以上版本）

SELECT TOP 5 total_worker_time / execution_count AS [Avg CPU Time],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.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   
 ORDER BY total_worker_time/execution_count DESC

3.平均耗CPU最多的前20个SQL （SQL SERVER 2008或以上版本）

SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],
    execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],
    min_worker_time /1000 AS [最小执行时间(ms)],
    max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName 
    FROM sys.dm_exec_query_stats qs WITH(nolock)
    CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE  execution_count>1
    ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

4.总耗CPU最多的前20个SQL （SQL SERVER 2008或以上版本）

SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    qt.dbid, dbname=db_name(qt.dbid),
    qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 xieaoao@qq.com QQ:1296454177

赏

©2008-2023 aoao.club

aoao

×

喜欢就点赞,疼爱就打赏