Max number of user connections

Question

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.

Answer 1

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;


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.

Answer 2

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):

1. Application user A asks for a connection to the database
2. Connection pool starts connection thread 1 to the database
3. Application user B asks for a connection to the database
4. Connection pool starts connection thread 2 to the database
5. Application user A closes their connection ... to the connection pool
6. Application user C asks for a connection to the database
7. Connection pool issues sp_reset_connection on thread 1
8. Connection pool assigns thread 1 to Application user C

This is an oversimplification, but the salient points include:

• The connection will remain open between the connection pool thread pool and the database until the database or the connection pool forcibly closes the connection
• The connection remains open with the last sessions execution context until that thread is re-used by another user, at which point in time sp_reset_connection is called.

Here's the reference material that I used to come to these conclusions.

Connection Pooling for SQL Server DBA

The case of orphaned transaction