SQL - How to find what actions are using all of the max concurrent connections

Question

I have a SQL Server that is reaching the max limit of concurrent connections. I have many different servers & services connecting to one SQL Server at the same time.

I did find another query that seems to work:

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;

However, this gives me the number of connections which is good. So then i found another query that seems to spit out the sql statements that er being run etc.

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

how would i merge both of these query together? I belive both of these query's together would give me what I need.

Answer 1

Not entirely sure what you are wanting on the output; you can join the two queries together (without the group) via a 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,

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][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

Answer 2

I'm not really sure what more you would need to know beyond sys.dm_exec_connections if the number of connections is what you are concerned with. Either way this is the code I use when looking at what's running on the instance, it also shows you which job is running the statement (if it is a job):

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       =
    SUBSTRING
    (
        qt.text,
        er.statement_start_offset/2,
        (CASE WHEN er.statement_end_offset = -1
        THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
        ELSE er.statement_end_offset
        END - er.statement_start_offset)/2
    )
    , sj.name + SUBSTRING(ses.program_name,65,8) JobName
    , ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    , 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
    , transaction_isolation =
    CASE ses.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
    , ConnectionWrites   = con.num_writes
    , ConnectionReads    = con.num_reads
    , ClientAddress      = con.client_net_address
    , Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
    LEFT OUTER JOIN sys.dm_exec_sessions ses
        ON ses.session_id = er.session_id
    LEFT OUTER JOIN sys.dm_exec_connections con
        ON con.session_id = ses.session_id
    LEFT OUTER JOIN msdb..sysjobs sj
        ON upper(convert(varchar(34), master.dbo.fn_varbintohexstr(convert(varbinary(16), sj.job_id)))) = LTRIM(RTRIM(SUBSTRING(ses.program_name,29,36)))
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE er.session_id > 50
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id