Question

如何查找最耗费cpu和内存的sql语句

Answer 1

CREATE procedure sp_who3
@type int =1,
@host_name varchar(50) ='%'
as
declare @str varchar(200)
select @str =@type

if not (@str = '1' or @str ='2' or @str ='3' or @str ='4' or @str ='11' or @str ='12' or @str ='13' or @str ='22' or @str ='21' or @str ='31' or @str ='32' or @str ='14'or @str = '15' or @str ='23')
select '支持命令'= ' 1 - 请求信息' union all
select '支持命令'= ' 2 - 请求信息 by user' union all
select '支持命令'= ' 3 - 请求信息 info by hostname' union all
select '支持命令'= ' 4 - 请求信息 info by status' union all
select '支持命令'= '11 - 记录当前阻塞信息 ' union all
select '支持命令'= '12 - 阻塞其他session的session ' union all
select '支持命令'= '13 - 被阻塞时间最长的session ' union all
select '支持命令'= '14 - 递归查被阻塞' union all
select '支持命令'= '15 - 死锁信息' union all
select '支持命令'= '21 - 事务日志时间按用户指定用户 ' union all
select '支持命令'= '22 - 事务日志时间按 ' union all
select '支持命令'= '31 - 平均消费CPU的前30行语句 ' union all
select '支持命令'= '32 - 总耗CPU最多的前个SQL '

--request info
if @type = 1
select s.session_id, s.status,db_name(r.database_id) as database_name,
s.login_name,s.login_time, s.host_name,
c.client_net_address,c.client_tcp_port,s.program_name,
r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes,
s.client_interface_name,
s.last_request_start_time, s.last_request_end_time,
c.connect_time, c.net_transport, c.net_packet_size,
r.start_time, r.status, r.command,
r.blocking_session_id, r.wait_type,
r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count,
r.percent_complete,r.granted_query_memory
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50 and s.host_name like @host_name
order by s.session_id

if @type =2
--request info by user
select login_name,COUNT(0) user_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by login_name
order by 2 desc

--request info by hostname
if @type =3
select s.host_name,c.client_net_address,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50 and host_name like @host_name + '%'
group by host_name,client_net_address
order by 3 desc

--request info by status
if @type =4
select s.status,COUNT(0) host_count
from Sys.dm_exec_requests r with(nolock)
right outer join Sys.dm_exec_sessions s with(nolock)
on r.session_id = s.session_id
right outer join Sys.dm_exec_connections c with(nolock)
on s.session_id = c.session_id
where s.session_id >50
group by s.status
order by 2 desc

----------------------------------------Blocked Info----------------------------------
--记录当前阻塞信息
if @type =11
select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address

--阻塞其他session的session
if @type =12
select t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) ,
sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2

--被阻塞时间最长的session
if @type =13
select top 10 t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]
,t1.resource_associated_entity_id as [blk object]
,t1.request_mode as [lock req] -- lock requested
,t1.request_session_id as [waiter sid] -- spid of waiter
,t2.wait_duration_ms as [wait time]
,(select text from sys.dm_exec_requests as r with(nolock) --- get sql for waiter
cross apply sys.dm_exec_sql_text(r.sql_handle)
where r.session_id = t1.request_session_id) as waiter_batch
,(select substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end - r.statement_start_offset)/2+1)
from sys.dm_exec_requests as r with(nolock)
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
where r.session_id = t1.request_session_id) as waiter_stmt --- statement executing now
,t2.blocking_session_id as [blocker sid] --- spid of blocker
,(select text from sys.sysprocesses as p with(nolock) --- get sql for blocker
cross apply sys.dm_exec_sql_text(p.sql_handle)
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)
where t1.lock_owner_address = t2.resource_address
order by t2.wait_duration_ms desc

if @type =21
select '事务日志时间'= b.elapsed_time_seconds, hh = b.elapsed_time_seconds/3600,
a.session_id,
a.login,
[host_name],
[database],
task_state,
a.command,
a.total_cpu_ms,
a.total_physical,
a.memory_use,
open_transactions,
login_time,
last_request_start_time,
net_address,
sqlsyntax
from v_sys_session a left join sys.dm_tran_active_snapshot_database_transactions b
on a.session_id =b.session_id where host_name like @host_name
order by a.session_id

if @type ='31'--最消费CPU的前30行语句
SELECT TOP 30
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

--总耗CPU最多的前个SQL:
if @type ='32'
begin
SELECT TOP 30
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],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
end
GO
SP_WHO3 32
GO

Answer 2

使用sql执行计划看看，具体操作百度查查