25

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.

enter image description here

  • 5
    If these are from an app, then the app should close its connection once it's finished with. Leaving a connection open is a possible reason for hitting this limit – Mark Sinkinson Jan 5 '16 at 16:54
32

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.

| improve this answer | |
  • 1
    Can you explain why we should identify "killable" sessions by using most_recent_sql_handle in sys.dm_exec_connections rather than by using, say, status and last_request_start_time and is_user_process in sys.dm_exec_sessions? It seems an odd choice. – Mike Sherrill 'Cat Recall' Mar 14 '17 at 19:56
  • That's a good point, @Mike - At the time I was thinking purely about connections that have been opened by connection pooling and that haven't ever been used. It would be a good idea to add in the is_user_process qualifier, and certainly wouldn't hurt to exclude sessions that have a last_request_start_time that is somewhat recent. How recent? Another good question. – Max Vernon Mar 14 '17 at 20:38
  • The last_request_start_time should probably be older rather than newer. I think a safely "killable" user session would be one that's sleeping and hasn't had a request for a couple of days. I guess the cutoff time depends on how good our application programmers are at cleaning up after themselves. – Mike Sherrill 'Cat Recall' Mar 14 '17 at 20:52
12

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.