我有一个sql Server达到并发连接的最大限制.我有很多不同的服务器和服务同时连接到一个sql Server.
我确实找到了另一个似乎有用的查询：
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
我将如何将这两个查询合并在一起？我相信这两个问题一起会给我我需要的东西.
解决方法
不完全确定你想要的输出;你可以通过stright join session_id = spid将两个查询连接在一起(没有组).
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
