首页新闻找找看学习计划

SQL Server 中如何通过已执行的 SQL 语句查到对应的数据库用户

0
悬赏园豆:100 [待解决问题]

通过监控发现 SQL server 执行的一段 SQL 语句存在性能问题,这段 SQL 语句是 EF Core 生成的,但不知道来自哪个应用,请问如何通过这段 SQL 语句找出执行它的数据库用户?

dudu的主页 dudu | 高人七级 | 园豆:39371
提问于:2020-03-20 12:02
< >
分享
所有回答(2)
0

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

czd890 | 园豆:8514 (大侠五级) | 2020-03-20 15:19
0

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

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

浅草青晨 | 园豆:639 (小虾三级) | 2020-03-21 16:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册