SQL statement for querying the maximum number of SQLServer connections

Set the T-SQL statement under the maximum number of connections to configure the maximum number of concurrent user connections allowed by SQLServer. Execsp_configureshowadvancedoptions, 1execsp_configureuserconnections, the first sentence of 100 is used to display the sp_configure system stored procedure advanced options, using userconnection

Set the T-SQL statement under the maximum number of connections to configure the maximum number of concurrent user connections allowed by SQL Server. Exec sp_configure 'show advanced options', 1 exec sp_configure 'user connections', the first sentence of 100 is used to display the advanced options of sp_configure system stored procedure, using user connection

Set the maximum number of connections

The following T-SQL statements configure the maximum number of concurrent user connections allowed by SQL Server.

Exec sp_configure 'show advanced options', 1
Exec sp_configure 'user connections', 100
The first sentence is used to display the advanced options of the sp_configure system stored procedure. When user connections is used, the value of show advanced options is required to be 1.

In the second sentence, the maximum number of connections is. 0 indicates no limit, but not unlimited.

You can also configure it in Enterprise Manager. In Enterprise Manager, you can right-click an instance and choose "properties"> "connection" to change it.

You need to restart SQL Server to make the value take effect.

@ Max_connections

Select @ max_connections
It always returns 32767, not the user connections set above. In fact, it indicates the maximum number of user connections can be set. Since the maximum value is 32767, when user connections is 0, the maximum number of connections is 32767, which is not infinite.

By default, the user connections value is 0, which means that the maximum number of connections of SQL Server is 32767 by default.

Obtain the maximum number of connections currently set:


Select value from master. dbo. sysconfigures where [config] = 103

How to monitor SQLServer connections

/* Query the number of connections */
Select loginame, count (1) as Nums
From sys. sysprocesses
Group by loginame
Order by 2 desc

Select spid, ecid, status, loginame, hostname, cmd, request_id
From sys. sysprocesses where loginame = ''and hostname =''

Method 2:

SP_WHO 'loginname'
LoginName is the user name used to log on to SQL. Generally, a username is used in the program to log on to SQL. This user name can be used to check the connection occupied by the user name after login.
If you do not enter loginName, all connections are returned.

Since the number of connections is predictable and monitorable, it is measurable, so we can evaluate or test the program concurrency based on the actual situation.

With this, I believe the above confusions can be solved.