SQL Server 连接池 (ADO.NET)SQL Server Connection Pooling (ADO.NET)

连接到数据库服务器通常由几个需要很长时间的步骤组成。Connecting to a database server typically consists of several time-consuming steps. 必须建立物理通道(例如套接字或命名管道),必须与服务器进行初次握手,必须分析连接字符串信息,必须由服务器对连接进行身份验证,必须运行检查以便在当前事务中登记,等等。A physical channel such as a socket or a named pipe must be established, the initial handshake with the server must occur, the connection string information must be parsed, the connection must be authenticated by the server, checks must be run for enlisting in the current transaction, and so on.

实际上,大多数应用程序仅使用一个或几个不同的连接配置。In practice, most applications use only one or a few different configurations for connections. 这意味着在执行应用程序期间,许多相同的连接将反复地打开和关闭。This means that during application execution, many identical connections will be repeatedly opened and closed. 若要打开的连接,成本降至最低ADO.NETADO.NET使用名为的优化方法连接池To minimize the cost of opening connections, ADO.NETADO.NET uses an optimization technique called connection pooling.

连接池使新连接必须打开的次数得以减少。Connection pooling reduces the number of times that new connections must be opened. 池进程保持物理连接的所有权。The pooler maintains ownership of the physical connection. 通过为每个给定的连接配置保留一组活动连接来管理连接。It manages connections by keeping alive a set of active connections for each given connection configuration. 每当用户在连接上调用 Open 时,池进程就会查找池中可用的连接。Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. 如果某个池连接可用,会将该连接返回给调用者,而不是打开新连接。If a pooled connection is available, it returns it to the caller instead of opening a new connection. 应用程序在该连接上调用 Close 时,池进程会将连接返回到活动连接池集中,而不是关闭连接。When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. 连接返回到池中之后,即可在下一个 Open 调用中重复使用。Once the connection is returned to the pool, it is ready to be reused on the next Open call.

只有配置相同的连接可以建立池连接。Only connections with the same configuration can be pooled. ADO.NETADO.NET 在同一时间,一个用于每个配置中保留多个池。keeps several pools at the same time, one for each configuration. 在使用集成的安全性时,连接按照连接字符串以及 Windows 标识分到多个池中。Connections are separated into pools by connection string, and by Windows identity when integrated security is used. 还根据连接是否已在事务中登记来建立池连接。Connections are also pooled based on whether they are enlisted in a transaction. 在使用 ChangePassword 时,SqlCredential 实例影响连接池。When using ChangePassword, the SqlCredential instance affects the connection pool. SqlCredential 的不同实例将使用不同的连接池,即使用户 ID 和密码相,也是如此。Different instances of SqlCredential will use different connection pools, even if the user ID and password are the same.

池连接可以显著提高应用程序的性能和可缩放性。Pooling connections can significantly enhance the performance and scalability of your application. 默认情况下,在 ADO.NETADO.NET 中启用连接池。By default, connection pooling is enabled in ADO.NETADO.NET. 除非显式禁用,否则,在应用程序中打开和关闭连接时,池进程会对连接进行优化。Unless you explicitly disable it, the pooler optimizes the connections as they are opened and closed in your application. 还可以提供几个连接字符串修饰符来控制连接池的行为。You can also supply several connection string modifiers to control connection pooling behavior. 有关更多信息,请参见本主题后面的“使用连接字符串关键字控制连接池”。For more information, see "Controlling Connection Pooling with Connection String Keywords" later in this topic.

备注

启用连接池后,如果发生超时错误或其他登录错误,则将引发异常,并且在接下来的五秒内进行的后续连接尝试将失败,此段时间称为“阻塞期”。When connection pooling is enabled, and if a timeout error or other login error occurs, an exception will be thrown and subsequent connection attempts will fail for the next five seconds, the "blocking period". 如果应用程序尝试在阻塞期内进行连接,则将再次引发第一个异常。If the application attempts to connect within the blocking period, the first exception will be thrown again. 阻塞期结束后的后续失败将导致新的阻塞期,该阻塞期的持续时间是上一个阻塞期的两倍,最长为一分钟。Subsequent failures after a blocking period ends will result in a new blocking periods that is twice as long as the previous blocking period, up to a maximum of one minute.

池的创建和分配Pool Creation and Assignment

在初次打开连接时,将根据完全匹配算法创建连接池,该算法将池与连接中的连接字符串关联。When a connection is first opened, a connection pool is created based on an exact matching algorithm that associates the pool with the connection string in the connection. 每个连接池都与一个不同的连接字符串相关联。Each connection pool is associated with a distinct connection string. 打开新连接时,如果连接字符串并非与现有池完全匹配,将创建一个新池。When a new connection is opened, if the connection string is not an exact match to an existing pool, a new pool is created. 按进程、应用程序域、连接字符串以及 Windows 标识(在使用集成的安全性时)来建立池连接。Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. 连接字符串还必须是完全匹配的;按不同顺序为同一连接提供的关键字将分到单独的池中。Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

在以下 C# 示例中创建了三个新的 SqlConnection 对象,但是管理时只需要两个连接池。In the following C# example, three new SqlConnection objects are created, but only two connection pools are required to manage them. 注意,根据为 Initial Catalog 分配的值,第一个和第二个连接字符串有所不同。Note that the first and second connection strings differ by the value assigned for Initial Catalog.

using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();        
        // Pool A is created.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=pubs"))  
    {  
        connection.Open();        
        // Pool B is created because the connection strings differ.  
    }  
  
using (SqlConnection connection = new SqlConnection(  
  "Integrated Security=SSPI;Initial Catalog=Northwind"))  
    {  
        connection.Open();        
        // The connection string matches pool A.  
    }  

如果 MinPoolSize 在连接字符串中未指定或指定为零,池中的连接将在一段时间不活动后关闭。If MinPoolSize is either not specified in the connection string or is specified as zero, the connections in the pool will be closed after a period of inactivity. 但是,如果指定的 MinPoolSize 大于零,在 AppDomain 被卸载并且进程结束之前,连接池不会被破坏。However, if the specified MinPoolSize is greater than zero, the connection pool is not destroyed until the AppDomain is unloaded and the process ends. 非活动或空池的维护只需要最少的系统开销。Maintenance of inactive or empty pools involves minimal system overhead.

备注

当出现故障转移等错误时,会自动清除池。The pool is automatically cleared when a fatal error occurs, such as a failover.

添加连接Adding Connections

连接池是为每个唯一的连接字符串创建的。A connection pool is created for each unique connection string. 当创建一个池后,将创建多个连接对象并将其添加到该池中,以满足最小池大小的需求。When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. 连接根据需要添加到池中,但是不能超过指定的最大池大小(默认值为 100)。Connections are added to the pool as needed, up to the maximum pool size specified (100 is the default). 连接在关闭或断开时释放回池中。Connections are released back into the pool when they are closed or disposed.

在请求 SqlConnection 对象时,如果存在可用的连接,将从池中获取该对象。When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available. 连接要可用,必须未使用,具有匹配的事务上下文或未与任何事务上下文关联,并且具有与服务器的有效链接。To be usable, a connection must be unused, have a matching transaction context or be unassociated with any transaction context, and have a valid link to the server.

连接池进程通过在连接释放回池中时重新分配连接,来满足这些连接请求。The connection pooler satisfies requests for connections by reallocating connections as they are released back into the pool. 如果已达到最大池大小且不存在可用的连接,则该请求将会排队。If the maximum pool size has been reached and no usable connection is available, the request is queued. 然后,池进程尝试重新建立任何连接,直至到达超时时间(默认值为 15 秒)。The pooler then tries to reclaim any connections until the time-out is reached (the default is 15 seconds). 如果池进程在连接超时之前无法满足请求,将引发异常。If the pooler cannot satisfy the request before the connection times out, an exception is thrown.

注意

我们强烈建议您在使用完连接时一定要关闭连接,以便连接可以返回池。We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. 你可以使用任一CloseDispose方法的Connection对象,或通过打开内的所有连接using语句在 C# 中,或Using在 Visual Basic 中的语句。You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. 不是显式关闭的连接可能不会添加或返回到池中。Connections that are not explicitly closed might not be added or returned to the pool. 有关详细信息,请参阅using 语句如何:释放系统资源适用于 Visual Basic。For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

备注

不要在类的 Close 方法中对 DisposeConnection 或任何其他托管对象调用 DataReaderFinalizeDo not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 在终结器中,仅释放类直接拥有的非托管资源。In a finalizer, only release unmanaged resources that your class owns directly. 如果类不拥有任何非托管资源,则不要在类定义中包含 Finalize 方法。If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. 有关详细信息,请参阅垃圾回收For more information, see Garbage Collection.

有关打开和关闭连接与关联的事件的详细信息,请参阅Audit Login Event ClassAudit Logout Event Class SQL Server 文档中。For more info about the events associated with opening and closing connections, see Audit Login Event Class and Audit Logout Event Class in the SQL Server documentation.

移除连接Removing Connections

如果空闲时间达到大约 4-8 分钟,或池进程检测到与服务器的连接已断开,连接池进程会将该连接从池中移除。The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed. 注意,只有在尝试与服务器进行通信之后才能检测到断开的连接。Note that a severed connection can be detected only after attempting to communicate with the server. 如果发现某连接不再连接到服务器,则会将其标记为无效。If a connection is found that is no longer connected to the server, it is marked as invalid. 无效连接只有在关闭或重新建立后,才会从连接池中移除。Invalid connections are removed from the connection pool only when they are closed or reclaimed.

如果存在一个与已消失的服务器的连接,即使连接池进程尚未检测到断开的连接,也可以从池中取出此连接并将连接标记为无效。If a connection exists to a server that has disappeared, this connection can be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. 这种情况是因为检查连接是否仍有效的系统开销将造成与服务器的另一次往返,从而抵消了池进程的优势。This is the case because the overhead of checking that the connection is still valid would eliminate the benefits of having a pooler by causing another round trip to the server to occur. 发生此情况时,初次尝试使用该连接将检测连接是否曾断开,并引发异常。When this occurs, the first attempt to use the connection will detect that the connection has been severed, and an exception is thrown.

清除池Clearing the Pool

ADO.NETADO.NET 2.0 引入了两个新方法来清除池:ClearAllPoolsClearPool2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools 对于给定的提供程序,清除连接池和ClearPool清除与特定连接关联的连接池。clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. 如果在调用时连接正在使用,将对它们进行相应的标记。If there are connections being used at the time of the call, they are marked appropriately. 连接关闭时,将被丢弃,而不是返回池中。When they are closed, they are discarded instead of being returned to the pool.

事务支持Transaction Support

连接是根据事务上下文来从池中取出并进行分配的。Connections are drawn from the pool and assigned based on transaction context. 除非在连接字符串中指定了 Enlist=false,否则连接池将确保连接在 Current 上下文中登记。Unless Enlist=false is specified in the connection string, the connection pool makes sure that the connection is enlisted in the Current context. 如果连接使用登记的 System.Transactions 事务关闭并返回到池中,连接将保留在池中,以便使用相同 System.Transactions 事务对该连接池的下一次请求将返回相同的连接(如果可用)。When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection if it is available. 如果发出这样的请求,而没有可用的池连接,则会从池的非事务性部分取出一个连接并登记。If such a request is issued, and there are no pooled connections available, a connection is drawn from the non-transacted part of the pool and enlisted. 如果在池的每个区域都没有可用的连接,则会创建一个新的连接并登记。If no connections are available in either area of the pool, a new connection is created and enlisted.

当连接关闭时,它将被释放回池中,并根据其事务上下文放入相应的子部分。When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. 因此,即使分布式事务仍然挂起,仍可以关闭该连接而不会生成错误。Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. 这样,你就可以在之后提交或中止分布式事务。This allows you to commit or abort the distributed transaction later.

使用连接字符串关键字控制连接池Controlling Connection Pooling with Connection String Keywords

ConnectionString 对象的 SqlConnection 属性支持连接字符串键/值对,可以用于调整连接池逻辑的行为。The ConnectionString property of the SqlConnection object supports connection string key/value pairs that can be used to adjust the behavior of the connection pooling logic. 有关详细信息,请参阅 ConnectionStringFor more information, see ConnectionString.

池碎片Pool Fragmentation

池碎片是许多 Web 应用程序中的一个常见问题,应用程序可能会创建大量在进程退出后才会释放的池。Pool fragmentation is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. 这样,将打开大量的连接,占用许多内存,从而导致性能降低。This leaves a large number of connections open and consuming memory, which results in poor performance.

因为集成安全性产生的池碎片Pool Fragmentation Due to Integrated Security

连接根据连接字符串以及用户标识来建立池连接。Connections are pooled according to the connection string plus the user identity. 因此,如果使用网站上的基本身份验证或 Windows 身份验证以及集成的安全登录,每个用户将获得一个池。Therefore, if you use Basic authentication or Windows Authentication on the Web site and an integrated security login, you get one pool per user. 尽管这样可以提高单个用户的后续数据库请求的性能,但是该用户无法利用其他用户建立的连接。Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. 这样还使每个用户至少产生一个与数据库服务器的连接。It also results in at least one connection per user to the database server. 这对特定 Web 应用程序结构会产生副作用,因为开发人员必须衡量安全性和审计要求。This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.

因为许多数据库产生的池碎片Pool Fragmentation Due to Many Databases

许多 Internet 服务提供商在一台服务器上托管多个网站。Many Internet service providers host several Web sites on a single server. 他们可能使用单个数据库确认窗体身份验证登录,然后为该用户或用户组打开与特定数据库的连接。They may use a single database to confirm a Forms authentication login and then open a connection to a specific database for that user or group of users. 与身份验证数据库的连接将建立池连接,供每个用户使用。The connection to the authentication database is pooled and used by everyone. 但是,每个数据库的连接存在一个独立的池,这会增加与服务器的连接数。However, there is a separate pool of connections to each database, which increase the number of connections to the server.

这也会对应用程序设计产生副作用。This is also a side-effect of the application design. 但是,可以通过一个相对简单的方式避免此副作用,而又不会影响连接 SQL Server 时的安全性。There is a relatively simple way to avoid this side effect without compromising security when you connect to SQL Server. 连接到服务器上的相同数据库而不是为每个用户或组连接到单独的数据库,然后执行 Transact-SQLTransact-SQL USE 语句来切换到所需数据库。Instead of connecting to a separate database for each user or group, connect to the same database on the server and then execute the Transact-SQLTransact-SQL USE statement to change to the desired database. 以下代码段演示如何创建与 master 数据库的初始连接,然后切换到 databaseName 字符串变量中指定的所需数据库。The following code fragment demonstrates creating an initial connection to the master database and then switching to the desired database specified in the databaseName string variable.

' Assumes that command is a valid SqlCommand object and that  
' connectionString connects to master.  
    command.Text = "USE DatabaseName"  
Using connection As New SqlConnection(connectionString)  
    connection.Open()  
    command.ExecuteNonQuery()  
End Using  
// Assumes that command is a SqlCommand object and that  
// connectionString connects to master.  
command.Text = "USE DatabaseName";  
using (SqlConnection connection = new SqlConnection(  
  connectionString))  
  {  
    connection.Open();  
    command.ExecuteNonQuery();  
  }  

应用程序角色和连接池Application Roles and Connection Pooling

通过调用 sp_setapprole 系统存储过程激活了 SQL Server 应用程序角色之后,该连接的安全上下文无法重置。After a SQL Server application role has been activated by calling the sp_setapprole system stored procedure, the security context of that connection cannot be reset. 但是,如果启用了池,连接将返回池,在重复使用池连接时会出错。However, if pooling is enabled, the connection is returned to the pool, and an error occurs when the pooled connection is reused. 有关详细信息,请参阅知识库文章"通过使用 OLE DB 资源池的 SQL 应用程序角色错误。"For more information, see the Knowledge Base article, "SQL application role errors with OLE DB resource pooling."

应用程序角色替代项Application Role Alternatives

建议您利用可以使用的安全机制,而不使用应用程序角色。We recommend that you take advantage of security mechanisms that you can use instead of application roles. 有关详细信息,请参阅SQL Server 中创建应用程序角色For more information, see Creating Application Roles in SQL Server.

请参阅See also