Requirements for SQL server to retrieve SQL and user information

Oracle If you need to know one thing SQL Who did it , Can pass v$sql Of parsing_schema_name Field to get the login schema name , amount to SQL And session login information are bound .

But recently, there was a SQL Server The needs of , Need to know history SQL The executor of the .

as follows SQL, Can find the current SQL Server I ran SQL, But no user information ,

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

Find... On the official website DMV There was a sys.dm_exec_sessions, Which field login_name, It should refer to the login user name ,

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

But I can talk to you sys.dm_exec_sql_text Only database_id, The following result should be a Cartesian product , Not yet SQL and login_name Associate user information , So it still doesn't meet the demand ,

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;

In addition, I found this statement ,

SELECT a.[request_session_id] AS [ conversation ID] ,
CASE [b].[status]
WHEN 'dormant' THEN 'dormant( Reset session )'
WHEN 'running' THEN 'running( In execution )'
WHEN 'sleeping' THEN 'sleeping( In sleep )'
WHEN 'background' THEN 'background( The background to perform )'
WHEN 'rollback' THEN 'rollback( Transaction rollback )'
WHEN 'pending' THEN 'pending( The session becomes available )'
WHEN 'runnable' THEN 'runnable( Executable )'
WHEN 'scheduler' THEN 'scheduler( Execute executable queue )'
WHEN 'spinloop' THEN 'spinloop( Wait for the spin lock to become available )'
WHEN 'suspended' THEN 'spinloop( Wait for the event to complete )'
ELSE [b].[status]
END AS [ Process status ] ,
CONVERT(VARCHAR(100), DB_NAME([b].dbid)) AS [ Database name ] ,
[qt].[text] AS [ Executing statement ] ,
CONVERT(VARCHAR(100), [b].hostname) AS [ Host name ] ,
d.client_net_address AS [IP Address ],
[b].loginame AS [ Login name ] ,
[c].start_time AS [ Start execution time ],
CASE a.[request_mode]
WHEN 'Sch-S' THEN 'Sch-S( Architecture stability )'
WHEN 'S' THEN 'S( share )'
WHEN 'U' THEN 'U( to update )'
WHEN 'X' THEN 'X( Exclusive )'
WHEN 'IS' THEN 'IS( Intention to share )'
WHEN 'IU' THEN 'IU( Intention update )'
WHEN 'IX' THEN 'IX( Intending to be exclusive )'
WHEN 'BU' THEN 'BU( High capacity operation )'
WHEN 'RangeS_S' THEN 'RangeS_S( Shared key range and shared resource lock )'
WHEN 'RangeS_U' THEN 'RangeS_U( Share key ranges and update resource locks )'
WHEN 'RangeI_N' THEN 'RangeI_N( Insert key range and empty resource lock )'
WHEN 'RangeI_S' THEN 'RangeI_S(RangeI_N  and  S  Conversion lock )'
WHEN 'RangeI_U' THEN 'RangeI_U(RangeI_N  and  U  Conversion lock )'
WHEN 'RangeI_X' THEN 'RangeI_X(angeI_N  and  X  Conversion lock )'
WHEN 'RangeX_S' THEN 'RangeX_S(RangeI_N  and  RangeS_S  Conversion lock )'
WHEN 'RangeX_U' THEN 'RangeX_U(RangeI_N  and  RangeS_U  Conversion lock )'
WHEN 'RangeX_X' THEN 'RangeX_X( Exclusive key range and exclusive resource lock )'
ELSE a.[request_mode]
END AS [ Request lock mode ] ,
CASE a.[request_status]
WHEN 'GRANTED' THEN 'GRANTED( Has been awarded )'
WHEN 'CONVERT' THEN 'CONVERT( Switching )'
WHEN 'WAIT' THEN 'WAIT( Waiting for the )'
ELSE a.[request_status]
END AS [ Request status ] ,
b.blocked AS [ Blocking a session ID] ,
CONVERT(VARCHAR(100), SUSER_NAME([b].uid)) AS [ Perform user ] ,
CONVERT(VARCHAR(100), [b].program_name) AS [ Application name ] ,
CONVERT(VARCHAR(MAX), [b].cmd) AS [ Executing order ] ,
CASE a.[resource_type]
WHEN 'DATABASE' THEN 'DATABASE( database )'
WHEN 'FILE' THEN 'FILE( file )'
WHEN 'OBJECT' THEN 'OBJECT( object )'
WHEN 'PAGE' THEN 'PAGE( page )'
WHEN 'KEY' THEN 'KEY( Index key )'
WHEN 'EXTENT' THEN 'EXTENT()'
WHEN 'RID' THEN 'RID( Line identification )'
WHEN 'APPLICATION' THEN 'APPLICATION( Applications )'
ELSE a.[resource_type]
END AS [ The resource type ] ,
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 [ Associate resource objects ] ,
a.[request_reference_count] AS [ Number of requests ] ,
CASE a.[request_owner_type]
WHEN 'TRANSACTION' THEN 'TRANSACTION( Business )'
WHEN 'CURSOR' THEN 'CURSOR( The cursor )'
WHEN 'SESSION' THEN 'SESSION( The user's session )'
WHEN 'SHARED_TRANSACTION_WORKSPACE'
THEN 'SHARED_TRANSACTION_WORKSPACE( Transaction workspace sharing )'
WHEN 'EXCLUSIVE_TRANSACTION_WORKSPACE'
THEN 'EXCLUSIVE_TRANSACTION_WORKSPACE( The business workspace is exclusive )'
ELSE a.[request_owner_type]
END AS [ Request entity type ] ,
STR([b].cpu, 7) AS [ Cumulative CPU Time ] ,
STR([b].physical_io, 7) AS [ At present IO( byte )]
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

He can find what is currently being implemented SQL And conversation information , Just from the content , To meet the requirements , But in scope , It's just the current execution , Without history .

Consulted the master , The reply is ,SQL Server Cannot pass DMV View to query the execution history of a session SQL, You can only collect data that the current session is executing SQl, Keep collecting and then save it .

No matter from the monitoring granularity , Or from the perspective of data statistics ,SQL Associated with user information retrieval is still useful , Can achieve more fine control , It's not clear why Microsoft didn't officially give such a design , Or there are other hidden functions ？

Recently updated articles ：

《MySQL A case of implicit conversion 》

《Linux Comparison of several data transmission tools 》

《 Xiaobai studies MySQL - There are some differences between users created by different versions 》

《 Recent problems 》

《Linux Retrieval of process information 》

Article classification and indexing ：

《 official account 900 Article classification and index 》