[问题现象]

在业务并发量大的情况下,数据库CPU达到100%,如何排查导致数据库服务器CPU 100%的SQL

[操作步骤]

在数据库中执行以下SQL,即可轻松获取导致数据库CPU 100%的SQL:

SELECT s.session_id,

r.start_time,

r.total_elapsed_time/1000 AS elapsed_seconds,

r.status AS RequestStatus,

r.database_id,

DB_NAME(r.database_id) AS DatabaseName,

r.reads,

r.writes,

r.logical_reads,

r.cpu_time,

r.total_elapsed_time,

s.status AS SessionStatus,

qt.event_info as text,

getdate() createtime

FROM sys.dm_exec_requests(nolock) r

INNER JOIN sys.dm_exec_sessions(nolock) s

ON r.session_id = s.session_id

LEFT JOIN sys.dm_exec_connections(nolock) c

ON c.session_id = s.session_id

cross apply sys.dm_exec_input_buffer (s.session_id, 0) as qt

WHERE DB_NAME(r.database_id)<";'master'

and qt.event_info is not null

order by r.cpu_time desc