Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to set the maximum number of connections in SQL Server. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Set the maximum number of connections

The following T-SQL statement configures 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 advanced options for displaying sp_configure system stored procedures. When using user connections, the show advanced options value is required to be 1.

In the second sentence, if the maximum number of connections is 100, zero means no limit, but it does not mean infinity. We will talk about it later.

It can also be configured in Enterprise Manager, where you can right-click on the instance-> Properties-> connections.

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

@ @ max_connectionsselect @ @ max_connections

It always returns 32767, which does not refer to the user connections set above, but actually represents the maximum user connections that can be set. Since its maximum value is 32767, when user connections is 0, the maximum number of connections is 32767, not infinite.

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

Second, get the maximum number of connections currently set:

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

3. How to monitor the number of SQLServer connections

/ * 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 is, of course, the user name that logs in to Sql. Generally, a username is used to log in to SQL so that you can see the connection occupied by this user name after logging in.

If you don't write loginName, then all connections are returned.

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

This is how to set the maximum number of connections in the SQL Server shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.