Problem
By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance. However, in some cases, database performance can be affected by such a huge number of users simultaneously querying the instance, which is not desirable.
Solution
To set a limit on the number of users simultaneously accessing the instance, we can use "Maximum number of concurrent connections" property in SQL Server. The value for this property can be set via SQL Server Management Studio as well as via a T-SQL statement.
In this article, we will see both the ways.
Configure Max Number of Concurrent Connections in SQL Server Management Studio
To limit the maximum number of concurrent connections via SQL Server Management Studio, follow these steps.
- From SQL Server Management Studio, right click on the SQL Server instance that you want to set a maximum number of connections for and click the "Properties" option at the bottom. This is shown in the following screenshot:
- A new window will appear. Select, the "Connections" option from the set of options on the left as shown in the following figure:
By default, the value for "Maximum number of concurrent connections" is set to 0 which means that an unlimited number of users can connect. The actual number depends upon the type of hardware and the version of SQL Server.
- In the "Maximum number of concurrent connections" scroll box, enter the number of maximum concurrent connections that you want for your database server. For the sake of this article, set the number of maximum number of concurrent connections to 300 and click OK.
- Restart the SQL Server Instance, refresh it and again go to Properties > Connections and you will see 300 in "Maximum number of concurrent connections" scroll box.
Dedicated Administrator Connection
If the maximum number of concurrent connections that SQL allows has already been reached, you will not be able to login to the SQL Server and change the configuration settings. In such cases you can try to connect to SQL Server via Dedicated Administrator Connection (DAC). Here is an article explaining how to connect to SQL Server using DAC. The DAC allows you to connect to SQL Server even when it cannot accept any more standard connections. Once you are connected to SQL Server using DAC, open SQL Server Management Studio execute the above steps and see if you can modify the maximum number of concurrent connections.
SQL Server T-SQL Code to Manage Maximum Number of Concurrent Connections
Apart from using SQL Server Management Studio, the maximum number of concurrent connections can also be controlled via a Transact-SQL script. To do so, you need to execute the following steps.
1 – Enable Advanced Configuration Options
Changing the maximum number of concurrent connections is a very advanced configuration task and is normally performed by Database Administrators. Therefore, before we can modify the maximum number of concurrent connections, we need to enable the "show advanced options" configuration. The following script enables this configuration:
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
In the script above, we execute the system stored procedure "sp_configure" and pass it two parameters: "show advanced option" and 1. The first parameter tells SQL Server that we want to change settings for "show advanced option" configuration. The second parameter enables the "show advanced options".
The flag "RECONFIGURE WITH OVERRIDE" sets the permissions for the configuration change. Here it means that the configuration changes executed by the user will override any existing configuration settings.
When the above statement executes, you will see the following output:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
The output shows that the configurations have successfully been updated.
2 – Change Concurrent Number of Connections
Now we have access to the configuration options that can change the number of concurrent connections. The next step is to write the script that updates the number of connections. The following script does that:
EXEC sys.sp_configure N'user connections', N'500'
In the above script, we again use the system stored procedure "sp_configure", but this time we pass it "user connections" as a parameter, which tells the parameter the stored procedure that this time we want to configure the "user_connections" settings. The second parameter is the number of concurrent user connections, which is 500 in the above script. Once you execute the above script, you will see the following output:
Configuration option 'user connections' changed from 300 to 500. Run the RECONFIGURE statement to install.
From the output, you can see that the maximum number of concurrent connections has been updated from 300 to 500. Remember, in the last section we set the total number of user connections to 300 using SQL Server Management Studio.
To make sure that your changes have actually taken effect, restart your database server and again go to Properties > Connections. You should see that the value in the "Maximum number of concurrent connections" scroll box has been updated to 500 as shown below:
3 – Disable Advanced Configuration Options
Once the advanced value for maximum concurrent connections has been set, as the last step you should always disable the advanced configuration options since they can greatly affect the behavior SQL Server. To disable advanced configuration options, execute the following script:
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
To disable advanced configuration options, we simply need to pass ‘0’ as the second parameter to the "sp_configure" stored procedure. The first parameter remains the same i.e. "show_advanced" options.
Once you execute the script above, you will see the following message on the console:
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
The message shows that the "show advanced options" configuration has been disabled.
Next Steps
The article explains the process of managing the maximum number of concurrent connections using SQL Server Management Studio as well as T-SQL. However, it is important to mention that managing maximum concurrent connections is a very advanced task and is normally performed by the database administrators. You should not modify this option unless absolutely necessary. For instance, if you set the value of the maximum number of concurrent connections to a very small number, only a limited number of users will be able to connect to the database. The other users won’t be able to connect unless the existing users terminate their connections. Therefore, you should be very careful while modifying this option.
