In SQL Server 2012 Standard edition, I know that the max number of user connections is 32,767. What should I do as a DBA if I am heading towards this number?
Currently there are 30,000 user connections, and this number is expected to increase.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The maximum 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;
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.
I've run into strange behavior with connection pooling in the past, and your scenario aligns well with one of those situations. If your application is using connection pooling (and that's still speculation, at this point, until you confirm or deny that) then you're going to have many connections that remain open. This is by design.
Connection pooling aims to reduce the overhead of creating a database connection. Let's take, for example, a connection pool of 3. As far as I can tell the lifecycle goes something like this (starting from a cold connection pool cache):
sp_reset_connectionon thread 1
This is an oversimplification, but the salient points include:
Here's the reference material that I used to come to these conclusions.