SQL: Current User Connections

Symptoms : The number of concurrent user connections has risen above the threshold level.

Impact : Medium

Normal behavior is for connections to remain open only for the duration of the action. If a connection is retained for an unjustified length, this may indicate some coding problem with one or more poorly written applications maintaining open connection. If the total number of connections rises above the physical limit of SQL Server (32k), any futher connection attempts will fail.

Expected behavior :

There is no standard metric for number of connections. You should set your own thresholds after reviewing past behavior while taking into account any planned changes in usage. If SQL Server itself has ben set with a specific limit (see background note), then make sure that your threshold is lower so that alerts are issued before the problem manifests.

Possible causes

Poor coding   Priority : Medium
Best practices for coding should be strictly enforced, to ensure that all connections are terminated and release correctly.
Recommended action :
Using AimBetter monitor, identify any queries that have remained open for an inordinate length of time. Recover the query plan, and see whether any improvements are available.

Inadequate hardware infrastructure   Priority : Medium
Recommended action :
See the background notes – either increase available memory by reconfiguration or adding more RAM, or move the SQL instance over onto a newer machine.

Index missing or corrupted  Priority : Medium
Missing or corrupted indexes can lead to queries working inefficiently and remaining open for longer.
Recommended action :
See our recommendations regarding memory optimization here.

Background

SQL Server allows a maximum of 32,767 user connections. Where the default is 0, SQL Server adjusts the maximum number of user connections automatically up to the maximum value allowable.

Any problems with connection allocation are far more likely to occur on 32-bit hardware servers. In this case, SQL Server has to fit everything bar the buffer pool in about 1.7 GB. If enough connections are live, SQL will reject connection tries because it cannot fit everything within available space. Adding extra memory will not help, because this does not add anything to the memory below the 4gb line.

In 64-bit SQL Server, memory pools can exist anywhere. While having too many connections may restrict the memory that SQL Server can use for other purposes, the threshold is far higher.