重庆思庄Oracle、Redhat认证学习论坛

标题: sqlserver查询正在运行的语句及查锁 [打印本页]

作者: miss_puff 时间: 2020-11-19 14:14
标题: sqlserver查询正在运行的语句及查锁
语句
WITH sess AS
(
SELECT
      es.session_id,
      database_name = DB_NAME(er.database_id),
      er.cpu_time,
      er.reads,
      er.writes,
      er.logical_reads,
      login_name,
      er.status,
      blocking_session_id,
      wait_type,
      wait_resource,
      wait_time,
      individual_query = SUBSTRING (qt.text, (er.statement_start_offset/2)+1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2)+1),
      parent_query = qt.text,
      program_name,
      host_name,
      nt_domain,
      start_time,
      DATEDIFF(MS,er.start_time,GETDATE()) as duration,
      (SELECT query_plan FROM sys.dm_exec_query_plan(er.plan_handle)) AS query_plan
FROM
      sys.dm_exec_requests er
      INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
      CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
      es.session_id > 50
      AND es.session_Id NOT IN (@@SPID)
)
SELECT
*
FROM
sess
UNION ALL SELECT
es.session_id,
database_name = '',
0,
0,
0,
0,
login_name,
es.status,
0,
'',
'',
'',
qt.text,
parent_query = qt.text,
program_name,
host_name,
nt_domain,
es.last_request_start_time,
DATEDIFF(MS,es.last_request_start_time,GETDATE()) as duration,
NULL AS query_plan
FROM
sys.dm_exec_sessions es
INNER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle)as qt
WHERE
ec.most_recent_session_id IN
(
      SELECT blocking_session_id FROM sess WHERE blocking_session_id NOT IN(SELECT DISTINCT session_id FROM sess)
)
ORDER BY
1, 2

blocking_session_id列可定位到导致阻塞的会话id