语句的sessionid --> session --> ip,username,?

SELECT a.[request_session_id] AS [会话ID] ,
CASE [b].[status]
WHEN 'dormant' THEN 'dormant(重置会话)'
WHEN 'running' THEN 'running(执行中)'
WHEN 'sleeping' THEN 'sleeping(睡眠中)'
WHEN 'background' THEN 'background(后台执行)'
WHEN 'rollback' THEN 'rollback(事务回滚)'
WHEN 'pending' THEN 'pending(会话变为可用)'
WHEN 'runnable' THEN 'runnable(可执行)'
WHEN 'scheduler' THEN 'scheduler(执行可执行队列)'
WHEN 'spinloop' THEN 'spinloop(等待自旋锁变为可用)'
WHEN 'suspended' THEN 'spinloop(等待事件完成)'
ELSE [b].[status]
END AS [进程状态] ,
CONVERT(VARCHAR(100), DB_NAME([b].dbid)) AS [数据库名] ,
[qt].[text] AS [正在执行语句] ,
CONVERT(VARCHAR(100), [b].hostname) AS [主机名称] ,
d.client_net_address AS [IP地址],
[b].loginame AS [登录名] ,
[c].start_time AS [开始执行时间],
CASE a.[request_mode]
WHEN 'Sch-S' THEN 'Sch-S(架构稳定性)'
WHEN 'S' THEN 'S(共享)'
WHEN 'U' THEN 'U(更新)'
WHEN 'X' THEN 'X(排他)'
WHEN 'IS' THEN 'IS(意向共享)'
WHEN 'IU' THEN 'IU(意向更新)'
WHEN 'IX' THEN 'IX(意向排他)'
WHEN 'BU' THEN 'BU(大容量操作)'
WHEN 'RangeS_S' THEN 'RangeS_S(共享键范围和共享资源锁)'
WHEN 'RangeS_U' THEN 'RangeS_U(共享键范围和更新资源锁)'
WHEN 'RangeI_N' THEN 'RangeI_N(插入键范围和空资源锁)'
WHEN 'RangeI_S' THEN 'RangeI_S(RangeI_N 和 S 转换锁)'
WHEN 'RangeI_U' THEN 'RangeI_U(RangeI_N 和 U 转换锁)'
WHEN 'RangeI_X' THEN 'RangeI_X(angeI_N 和 X 转换锁)'
WHEN 'RangeX_S' THEN 'RangeX_S(RangeI_N 和 RangeS_S 转换锁)'
WHEN 'RangeX_U' THEN 'RangeX_U(RangeI_N 和 RangeS_U 转换锁)'
WHEN 'RangeX_X' THEN 'RangeX_X(排他键范围和排他资源锁)'
ELSE a.[request_mode]
END AS [请求锁模式] ,
CASE a.[request_status]
WHEN 'GRANTED' THEN 'GRANTED(已授予)'
WHEN 'CONVERT' THEN 'CONVERT(转换中)'
WHEN 'WAIT' THEN 'WAIT(等待中)'
ELSE a.[request_status]
END AS [请求状态] ,
b.blocked AS [阻塞会话ID] ,
CONVERT(VARCHAR(100), SUSER_NAME([b].uid)) AS [执行用户] ,
CONVERT(VARCHAR(100), [b].program_name) AS [应用程序名] ,
CONVERT(VARCHAR(MAX), [b].cmd) AS [正在执行命令] ,
CASE a.[resource_type]
WHEN 'DATABASE' THEN 'DATABASE(数据库)'
WHEN 'FILE' THEN 'FILE(文件)'
WHEN 'OBJECT' THEN 'OBJECT(对象)'
WHEN 'PAGE' THEN 'PAGE(页)'
WHEN 'KEY' THEN 'KEY(索引键)'
WHEN 'EXTENT' THEN 'EXTENT()'
WHEN 'RID' THEN 'RID(行标识)'
WHEN 'APPLICATION' THEN 'APPLICATION(应用程序)'
ELSE a.[resource_type]
END AS [资源类型] ,
CASE WHEN a.[resource_database_id] = DB_ID()
AND a.[resource_type] = 'OBJECT'
THEN CONVERT(VARCHAR(200), OBJECT_NAME(a.resource_Associated_Entity_id))
ELSE CONVERT(VARCHAR(200), a.resource_Associated_Entity_id)
END AS [关联资源对象] ,
a.[request_reference_count] AS [请求次数] ,
CASE a.[request_owner_type]
WHEN 'TRANSACTION' THEN 'TRANSACTION(事务)'
WHEN 'CURSOR' THEN 'CURSOR(游标)'
WHEN 'SESSION' THEN 'SESSION(用户会话)'
WHEN 'SHARED_TRANSACTION_WORKSPACE'
THEN 'SHARED_TRANSACTION_WORKSPACE(事务工作区共享)'
WHEN 'EXCLUSIVE_TRANSACTION_WORKSPACE'
THEN 'EXCLUSIVE_TRANSACTION_WORKSPACE(事务工作区排他)'
ELSE a.[request_owner_type]
END AS [请求实体类型] ,
STR([b].cpu, 7) AS [累计CPU时间] ,
STR([b].physical_io, 7) AS [当前IO(字节)]
FROM [sys].[dm_tran_locks] a WITH ( NOLOCK )
INNER JOIN sys.sysprocesses b WITH ( NOLOCK ) ON a.[request_session_id] = [b].[spid]
INNER JOIN sys.dm_exec_requests c WITH ( NOLOCK ) ON [c].[session_id] = [b].[spid]
INNER JOIN sys.dm_exec_connections d WITH ( NOLOCK ) ON d.session_id = a.request_session_id
CROSS APPLY sys.dm_exec_sql_text(c.[sql_handle]) AS qt
ORDER BY request_session_id ,
resource_database_id DESC

这个你看看能不能解决你说的问题