SQL set SQL Server maximum number of connections and query statement _mssql

First, set the 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 is used to indicate sp_configure advanced options for displaying system stored procedures, with user connections show advanced options A required value of 1.

The second sentence configures the maximum number of connections for 100,0, but does not represent infinity, which will be talked about later.

Can also be configured in Enterprise Manager, in Enterprise Manager, you can right-click on an instance to-> "Properties"-> "Connection" changes.

You will need to restart SQL Server for this value to take effect.

@ @max_connections

SELECT @ @max_connections

It always returns 32767, and it does not mean that it is set above user connections , and in fact it represents the maximum size to user connections be set. Since its maximum value is 32767, then user connections 0 o'clock, the maximum number of connections is 32767, not infinite.

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

Get the maximum number of connections currently set:

Iii. How to monitor the number of connections to SQL Server

/* Query Connection Number *
/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 login SQL username, the general program inside will use a username to login SQL so through this user name can see the user name after the connection occupied.

If it is not written loginName , then all the 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 release the amount according to the actual situation.

Summarize

Well, the above is the entire content of this article, with these, I believe the above confusion should be able to untie it. I hope the content of this article for everyone's study or work can bring certain help, if there is doubt you can message exchange.