SQL – 如何查找正在使用所有最大并发连接的操作

我确实找到了另一个似乎有用的查询：

SELECT DB_NAME(dbid) AS DBName,COUNT(dbid)   AS NumberOfConnections,loginame      AS LoginName,nt_domain     AS NT_Domain,nt_username   AS NT_UserName,hostname      AS HostName
FROM   sys.sysprocesses
WHERE  dbid > 0
GROUP  BY dbid,hostname,loginame,nt_domain,nt_username
ORDER  BY NumberOfConnections DESC;

但是,这给了我很好的连接数.那么我发现另一个查询似乎吐出了正在运行的sql语句等.

SELECT
SPID                = er.session_id,STATUS             = ses.STATUS,[Login]            = ses.login_name,Host               = ses.host_name,BlkBy              = er.blocking_session_id,DBName             = DB_Name(er.database_id),CommandType        = er.command,SQLStatement       = st.text,ObjectName         = OBJECT_NAME(st.objectid),ElapsedMS          = er.total_elapsed_time,CPUTime            = er.cpu_time,IOReads            = er.logical_reads + er.reads,IOWrites           = er.writes,LastWaitType       = er.last_wait_type,StartTime          = er.start_time,Protocol           = con.net_transport,ConnectionWrites   = con.num_writes,ConnectionReads    = con.num_reads,ClientAddress      = con.client_net_address,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id

我将如何将这两个查询合并在一起？我相信这两个问题一起会给我我需要的东西.

解决方法

SELECT
spr.loginame as LoginName,spr.nt_domain     AS NT_Domain,spr.nt_username   AS NT_UserName,spr.hostname      AS HostName,Authentication     = con.auth_scheme
FROM [sys.dm_exec_requests][1] er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
left outer join sys.sysprocesses spr
on er.session_id = spr.spid

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!