5. SQL Server数据库性能监控 - 当前请求
SELECT r.session_id,r.blocking_session_id
,DB_Name(r.database_id) as database_name
,r.start_time
,r.total_elapsed_time
,r.
,CASE WHEN r.blocking_session_id 0 THEN'Blocking'
WHEN r.blocking_session_id= 0 AND r.wait_type is not null THEN 'Waiting'
ELSE 'Long-running'
END as slowness_type
,r.percent_complete
,r.command
,r.wait_type
,r.wait_time
,r.wait_resource
,r.last_wait_type
,r.cpu_time
,r.reads
,r.writes
,r.logical_reads
,t. as executing_batch
,SUBSTRING(t.,
r.statement_start_offset/2,
(CASE WHENr.statement_end_offset =-1
THENDATALENGTH(t.) --LEN(CONVERT(NVARCHAR(MAX),t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset )/2+ 1) as executing_sql
,bt. as blocking_batch
,SUBSTRING(bt.,
br.statement_start_offset/2,
(CASE WHENbr.statement_end_offset = -1
THENDATALENGTH(bt.) --LEN(CONVERT(NVARCHAR(MAX),bt.text)) * 2
ELSE br.statement_end_offset
END - br.statement_start_offset )/2+ 1) as blocking_sql
--,p.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) as p
LEFT JOIN sys.dm_exec_requests br
ON r.blocking_session_id =br.session_id
OUTER APPLY sys.dm_exec_sql_text(br.session_id) as bt
WHERE r.session_id > 50 and r.session_id@@SPID
AND r.total_elapsed_time >30 * 60 * 1000
ORDER BY r.total_elapsed_timeDESC;
页:
[1]