配置 user connections 服务器配置选项Configure the user connections Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL Server 2019SQL Server 2019 中设置 SQL Server Management StudioSQL Server Management Studio user connections Transact-SQLTransact-SQL服务器配置选项。This topic describes how to set the user connections server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. user connections 选项指定 SQL ServerSQL Server实例上允许同时建立的最大用户连接数。The user connections option specifies the maximum number of simultaneous user connections that are allowed on an instance of SQL ServerSQL Server. 实际允许的用户连接数还取决于正使用的 SQL ServerSQL Server 版本以及应用程序和硬件的限制。The actual number of user connections allowed also depends on the version of SQL ServerSQL Server that you are using, and also the limits of your application or applications and hardware. SQL ServerSQL Server 允许的最大用户连接数为 32767。allows a maximum of 32,767 user connections. 由于 user connections 是动态(自动配置)选项, SQL ServerSQL Server 将根据需要自动调整最大用户连接数,最大不超过允许的最大值。Because user connections is a dynamic (self-configuring) option, SQL ServerSQL Server adjusts the maximum number of user connections automatically as needed, up to the maximum value allowable. 例如,如果仅有 10 个用户登录,则要分配 10 个用户连接对象。For example, if only 10 users are logged in, 10 user connection objects are allocated. 在大多数情况下,没有必要更改此选项的值。In most cases, you do not have to change the value for this option. 默认值为 0,表示允许的最多用户连接数为 (32,767) 。The default is 0, which means that the maximum (32,767) user connections are allowed.

若要确定系统允许的最大用户连接数,可以执行 sp_configure 或查询 sys.configuration 目录视图。To determine the maximum number of user connections that your system allows, you can execute sp_configure or query the sys.configuration catalog view.

本主题内容In This Topic

开始之前Before You Begin

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 使用 user connections 选项有助于避免由于过多并发连接而使服务器超载。Using the user connections option helps avoid overloading the server with too many concurrent connections. 可以根据系统和用户要求估计连接数。You can estimate the number of connections based on system and user requirements. 例如,在很多用户的系统上,每个用户通常不要求唯一的连接。For example, on a system with many users, each user would not usually require a unique connection. 可以在用户间共享连接。Connections can be shared among users. 对于运行 OLE DB 应用程序的用户,每个打开的连接对象需要一个连接;对于运行开放式数据库连接 (ODBC) 应用程序的用户,每个活动连接句柄需要一个连接;对于运行 DB-Library 应用程序的用户,每个调用 DB-Library dbopen 函数启动的进程需要一个连接。Users running OLE DB applications need a connection for each open connection object, users running Open Database Connectivity (ODBC) applications need a connection for each active connection handle in the application, and users running DB-Library applications need one connection for each process started that calls the DB-Library dbopen function.

    重要

    如果必须使用此选项,请不要将值设置得太高,这是因为不管是否使用连接,每个连接都会产生开销。If you must use this option, do not set the value too high, because each connection has overhead regardless of whether the connection is being used. 如果超过了用户连接的最大允许值,将收到一条错误消息,而且直到出现一个可用连接之后才能建立连接。If you exceed the maximum number of user connections, you receive an error message and are not able to connect until another connection becomes available.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置 user connections 选项To configure the user connections option

  1. 在对象资源浏览器中,右键单击某个服务器,然后单击 “属性”In Object Explorer, right-click a server and click Properties.

  2. 单击 “连接” 节点。Click the Connections node.

  3. “连接” 下面的 “最大并发连接数” 框中,键入或选择一个介于 0 到 32767 之间的值,以设置允许与 SQL ServerSQL Server实例同时连接的最大用户数量。Under Connections, in the Max number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL ServerSQL Server.

  4. 重新启动 SQL ServerSQL ServerRestart SQL ServerSQL Server.

使用 Transact-SQLUsing Transact-SQL

配置 user connections 选项To configure the user connections option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例显示如何使用 sp_configureuser connections 选项的值配置为 325 个用户。This example shows how to use sp_configure to configure the value of the user connections option to 325 users.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'user connections', 325 ;  
GO  
RECONFIGURE;  
GO  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置用户连接选项之后Follow Up: After you configure the user connections option

必须重新启动服务器,设置才会生效。The server must be restarted before the setting can take effect.

另请参阅See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)