SQL sets the maximum number of connections and query statements of SQL Server.

SQL sets the maximum number of connections and query statements of SQL Server.

1. 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', 1exec sp_configure 'user connections', 100

The first sentence is used to indicate the displaysp_configureSystem Stored Procedure advanced options, useuser connections Requiredshow advanced options The value is 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. It does not refer touser connectionsIn fact, it indicatesuser connectionsThe maximum value. Because the maximum value is 32767user connections When the value is 0, the maximum number of connections is 32767, which is not infinite.

By defaultuser connectionsThe value is 0, that is, the maximum number of connections of SQL Server is 32767 by default.

2. obtain the maximum number of connections currently set:

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

Iii. How to monitor the connections of SQLServer

/* Query connections */select loginame, count (1) as Numsfrom sys. sysprocessesgroup by loginameorder by 2 descselect spid, ecid, status, loginame, hostname, cmd, request_id from sys. sysprocesses where loginame = ''and hostname =''

Method 2:

SP_WHO 'loginName'

loginName Of course, the user name used to log on to SQL is usually used in the programusernameIn this way, you can view the connection occupied by this user name after login.

If you do not writeloginNameAnd all the 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.

Summary

Well, the above is all the content of this article. With this, I believe the above confusions can be solved. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message.