The maximum number of connectionsmaximum number of connections across SQL Server versions and editions is 32,767.
You can determine how many connections SQL Server currently has by looking at:
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END
, CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END;
If the ratio between used and unused connections from the above query is concerning, it is likely that connection pooling is enabled by client applications connected to the server, and those connections are not being efficiently used. You may want to have developers modify the connection string for these applications to limit the size of the connection pool, and ensure they are properly disposing connections. If connections are not being disposed correctly, they will remain open as long as the client application is running.
If you are feeling particularly rabid, and need to get rid of all the connections that haven't performed anything recently (regardless of if they are actually currently performing work), you can run the following code, which will generate a list of sessions that can be killed. You'd need to copy-and-paste the generated commands into a new SSMS window to actually run the commands. I'd also recommend having your résumé up-to-date just in case.
DECLARE @cmd NVARCHAR(MAX); SET @cmd = ''; SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END + 'KILL ' + CONVERT(VARCHAR(MAX), dec.session_id) + ';' FROM sys.dm_exec_connections dec WHERE dec.most_recent_sql_handle = 0x0;
PRINT @cmd;
It is possible to linearly scale the number of connections beyond 32,767 by sharding the data across multiple SQL Server nodes. However, in my opinion, using sharding as a way to get around the limit on the number of connections is similar to using an atom bomb to kill a spider. It will kill the spider, but you just might have bigger problems at the end of the day. Not to mention it's pretty darn hard to build an atom bomb, not to mention implement sharding properly.
