SQL Server检索SQL和用户信息的需求

bisal

发布于 2022-04-23 09:00:51

1.2K0

发布于 2022-04-23 09:00:51

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

Oracle中如果需要知道一条SQL是谁执行的，可以通过v$sql的parsing_schema_name字段得到登录的schema名称，相当于SQL和会话登录信息是有绑定的。

但是最近有个SQL Server的需求，需要知道历史SQL的执行者。

如下SQL，可以找到当前SQL Server跑过的SQL，但是没用户信息，

SELECT p.refcounts, p.usecounts, sqltext.text
 ?FROM?sys.dm_exec_cached_plans?p
?CROSS?APPLY?sys.dm_exec_sql_text(p.plan_handle)?sqltext
 WHERE?p.objtype?IN?('Adhoc',?'Prepared')?
?? AND?p.cacheobjtype?=?'Compiled?Plan'
   AND sqltext.text NOT LIKE '%sys%'
   AND sqltext.text NOT LIKE '%fn_listextendedproperty%'
   AND upper(sqltext.text) LIKE 'SELECT%'
???AND?upper(db_name(sqltext.dbid))?=?'XXX'
?ORDER?BY?sqltext.text

从官网找到DMV中有个sys.dm_exec_sessions，其中字段login_name，应该指的是登录的用户名，

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15

但是能和sys.dm_exec_sql_text关联起来的只有database_id，如下得到的应该是个笛卡尔积，并未将SQL和login_name用户的信息关联起来，所以还是没满足需求，

SELECT sessions.login_name, p.refcounts, p.usecounts, sqltext.text
  FROM sys.dm_exec_cached_plans p
?CROSS?APPLY?sys.dm_exec_sql_text(p.plan_handle)?sqltext
 CROSS APPLY sys.dm_exec_sessions sessions
 WHERE?p.objtype?IN?('Adhoc',?'Prepared')?
   AND p.cacheobjtype = 'Compiled Plan'
   AND sqltext.text NOT LIKE '%sys%'
   AND sqltext.text NOT LIKE '%fn_listextendedproperty%'
   AND upper(sqltext.text) LIKE 'SELECT%'
?? AND?upper(db_name(sqltext.dbid))?=?'XXX'
???AND?sessions.database_id?=?sqltext.dbid
?ORDER?BY?sqltext.text;

另外找到了这条语句，

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

他可以找到当前正在执行的SQL和会话的信息，单从内容上，满足需求，但是范围上，只是当前执行的，并未包含历史的。

咨询了大师，给到的回复是，SQL Server不能通过DMV视图来查询某一个会话执行过的历史SQL，只能采集当前会话正在执行的SQl，不断采集然后保存下来才行。

无论从监控粒度，还是数据统计的角度，SQL和用户信息关联检索还是有用的，可以做到更精细的控制，不太清楚为什么微软官方没给出这样的设计，或者有其他隐藏的功能？

本文参与?腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-04-22 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql server