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 further connection attempts will fail.
| CRITICAL : | ||
| MEDIUM : | ||
| LOW : | 300 connections | |
| Timespan : | more than 15 minutes |
Expected behavior :
There is no standard metric for the 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 been 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 promptly and released 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.
