SQL Server max number of connections query statement

Set maximum number of connections

The following T-SQL statement can 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 shows advanced options for displaying the sp_configure system stored procedure, and when using user connections, the show advance options value is 1.

The second sentence configures the maximum number of connections to 100,0, but does not indicate infinity, and will talk later.

Can also be configured in Enterprise Manager, in Enterprise Manager, you can change the instance on the right-click Properties, "Connections".

SQL Server needs to be restarted for this value to take effect.

@ @max_connections

SELECT @ @max_connections
It always returns 32767, it does not refer to the user connections set above, in fact it indicates the maximum number of user connections can be set. Since its maximum value is 32767, then user connections is 0 o'clock, the maximum number of connections is 32767, not infinite.

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

Get the maximum number of connections currently set:


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

How to monitor the number of SQL Server 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 Two:

sp_who ' LoginName '
LoginName is of course the user name of the login SQL, the general program will use a username to log in to the SQL so that the user name can be seen after the login of the connection.
If you do not write loginname, then all connections are returned.

Since the number of connections is predictable and measurable, then it is measurable, so we can evaluate or test the program and distribute it according to the actual situation.

With these, I believe that the above confusion should be able to untie it.

SQL Server max number of connections query statement