Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Managing Maximum Number of Concurrent Connections in SQL Server


By:   |   Last Updated: 2018-11-08   |   Comments (1)   |   Related Tips: More > SQL Server Configurations

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.

  1. 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:
sql server object explorer
  1. A new window will appear. Select, the "Connections" option from the set of options on the left as shown in the following figure:
sql server connection properties

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.

  1. 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.
  2. 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:

sql server connection properties

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.



Last Updated: 2018-11-08


next webcast button


next tip button



About the author
MSSQLTips author Ben Richardson Ben is the owner of Acuity Training, a UK based IT training business offering SQL training up to advanced administration courses.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, November 09, 2018 - 2:33:45 AM - Thomas Franz Back To Top

Strange, that I have to restart my SQL server when I increase the number of connections allowed.

Assuming, that there are already 300 people connected, I would have to kick of all of them only to allow person 301 to connect after increasing to 500 ...


Learn more about SQL Server tools