SELECT
SessionID = s.Session_id,
l.request_session_id spid,
a.blocked,
a.start_time,
a.ecid,
OBJECT_NAME(l.resource_associated_entity_id) tableName,
a.text,
resource_type,
DatabaseName = DB_NAME(resource_database_id),
request_mode,
request_type,
login_time,
host_name,
program_name,
client_interface_name,
login_name,
a.nt_domain,
nt_user_name,
s.status,
last_request_start_time,
last_request_end_time,
s.logical_reads,
s.reads,
request_status,
request_owner_type,
objectid,
dbid,
a.number,
a.encrypted ,
a.blocking_session_id
FROM
sys.dm_tran_locks l
JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
LEFT JOIN
(
SELECT [Spid] = session_id ,
blocked,
sp.request_id,--请求ID
sp.cmd,
text,
ecid ,
[Database] = DB_NAME(sp.dbid) ,
[User] = nt_username ,
[Status] = r.status ,
[Wait] = wait_type ,
sp.sql_handle,
Program = program_name ,
hostname ,
nt_domain ,
start_time,
objectid,
sp.dbid,
number,
encrypted ,
blocking_session_id
FROM sys.dm_exec_requests r
INNER JOIN sys.sysprocesses sp ON r.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
) a ON s.session_id = a.Spid
WHERE
s.session_id > 50 and l.resource_type = 'OBJECT'
and start_time < DATEADD( MI,-2,GETDATE()) --执行时间超过2分钟