Limit the Maximum Number of Concurrent Connections in SQL Server for Better Performance

All applications and software retrieve information from SQL server to display the required things and to process the data. By default, you can make 32767 simultaneous connections to the SQL server at max. But in most cases, this is undesirable since so many connections at one time can slow down the server performance.

In this article, learn how you can limit the number of concurrent connection to your SQL server.

Use SQL Server Management Studio

The best solution for this problem is to use the SQL Server Management Studio. Here are the steps you need to follow:

• Open SQL Server Management Studio
• Right-click on the SQL Server instance for which you want to limit the connections. Then click on “Properties”
• When a new window opens, click on “Options” from the left-hand side panel under “Select a Page”
• By default, you’ll notice that the “Maximum Number of Concurrent Connections” is set to 0. This implies that there’s no limit to how many users can connect to it at once. The number will depend on type of hardware and SQL Server current version
• You need to specify the maximum number of concurrent connections in the scroll box and click on “OK”
• Now restart the SQL Server Instance. You’ll see the number has been changed to the one you set

Dedicated Administrator Connection

In case the maximum number of simultaneous connections have exceeded the number, you won’t be able to make further connections -- even as an admin. This would make it impossible to change configuration settings.

In such cases, you need to connect to the sever via the Dedicated Administration Connection or DAC. After connecting to server via DAC, you need to follow the steps mentioned above to change the configuration.

Use SQL Server T-SQL Code

Another way to change the maximum number of concurrent connections is to use the T-SQL or Transact-SQL script. Here are the steps involved:

• Enable Advanced Configuration

First, you need to enable Advanced Configuration that’s because changing maximum number of concurrent connection is an advanced task. It’s usually reserved for and performed by database administrators with technical expertise.

You need to execute the following script to enable advanced configuration:

The code execute the system stored procedure “sp_configure.” Then, two parameters are passed to it, which are “show advanced option” and 1. While the first parameter tells the SQL server that the user need to change settings in the “show advanced options” configuration, the second parameter enables it.

You’ll notice that there’s a flag called “RECONFIGURE WITH OVERRIDE”. It is used to set the permission. In this case, it means that any change the user makes should override the existing settings.

After executing the above code, you’ll get this output:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

• Change the Concurrent Number of Connections

In the next step, you need to execute the command to update the concurrent number of connections. Here’s the command:

Again, you have to use the “sp_configure” procedure. But this time, you need to pass “user connections” as the parameter. It informs the SQL server that you want to change the “user_connections” settings. The second parameter “500” is the number of concurrent user connections you want to set.

After executing the script, you’ll get this output:

Configuration option 'user connections' changed from 200 to 500. Run the RECONFIGURE statement to install.

You can then confirm that the maximum number of concurrent connections have been updated from 200 to 500. Restart the SQL server and go into Properties > Connections. Here, you should see the Maximum number of concurrent connections updated to 500.

• Disable Advanced Configuration

Finally, you need to disable advanced configuration. If left open, you may accidentally affect other important settings and alter the behavior of SQL Server.

Execute this command to disable it:

You’ll get this output which will confirm that the task was successful:

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

So that’s you can limit the maximum number of concurrent connections made to the SQL server. You can change the number any time you wish to. But follow the steps properly as mentioned above.