SQL Server 連接池 (ADO.NET) | Microsoft Docs

黃金屋1 2018-08-05

展開全文

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

2017/03/30
作者

連接到數(shù)據(jù)庫服務器通常由幾個需要很長時間的步驟組成,。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.

實際上,，大多數(shù)應用程序僅使用一個或幾個不同的連接配置。In practice, most applications use only one or a few different configurations for connections. 這意味著在執(zhí)行應用程序期間,，許多相同的連接將反復地打開和關閉,。This means that during application execution, many identical connections will be repeatedly opened and closed. 若要打開的連接，成本降至最低ADO.NETADO.NET使用名為的優(yōu)化方法連接池,。To minimize the cost of opening connections, ADO.NETADO.NET uses an optimization technique called connection pooling.

連接池使新連接必須打開的次數(shù)得以減少,。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. 還根據(jù)連接是否已在事務中登記來建立池連接,。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. 除非顯式禁用,，否則,，在應用程序中打開和關閉連接時，池進程會對連接進行優(yōu)化,。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.

備注

啟用連接池后,，如果發(fā)生超時錯誤或其他登錄錯誤，則將引發(fā)異常,，并且在接下來的五秒內進行的后續(xù)連接嘗試將失敗,，此段時間稱為“阻塞期”。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'. 如果應用程序嘗試在阻塞期內進行連接,，則將再次引發(fā)第一個異常,。If the application attempts to connect within the blocking period, the first exception will be thrown again. 阻塞期結束后的后續(xù)失敗將導致新的阻塞期，該阻塞期的持續(xù)時間是上一個阻塞期的兩倍,，最長為一分鐘,。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.

池的創(chuàng)建和分配Pool Creation and Assignment

在初次打開連接時，將根據(jù)完全匹配算法創(chuàng)建連接池，該算法將池與連接中的連接字符串關聯(lián),。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. 每個連接池都與一個不同的連接字符串相關聯(lián),。Each connection pool is associated with a distinct connection string. 打開新連接時，如果連接字符串并非與現(xiàn)有池完全匹配,，將創(chuàng)建一個新池,。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# 示例中創(chuàng)建了三個新的 SqlConnection 對象,，但是管理時只需要兩個連接池,。In the following C# example, three new SqlConnection objects are created, but only two connection pools are required to manage them. 注意,，根據(jù)為 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. 非活動或空池的維護只需要最少的系統(tǒng)開銷,。Maintenance of inactive or empty pools involves minimal system overhead.

備注

當出現(xiàn)故障轉移等錯誤時，會自動清除池,。The pool is automatically cleared when a fatal error occurs, such as a failover.

添加連接Adding Connections

連接池是為每個唯一的連接字符串創(chuàng)建的,。A connection pool is created for each unique connection string. 當創(chuàng)建一個池后，將創(chuàng)建多個連接對象并將其添加到該池中,，以滿足最小池大小的要求,。When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. 連接根據(jù)需要添加到池中，但是不能超過指定的最大池大?。J值為 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. 連接要可用,，必須未使用,，具有匹配的事務上下文或未與任何事務上下文關聯(lián)，并且具有與服務器的有效鏈接。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). 如果池進程在連接超時之前無法滿足請求,，將引發(fā)異常。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. 你可以使用任一Close或Dispose方法的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 語句或如何：釋放系統(tǒng)資源適用于 Visual Basic,。For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

備注

不要在類的 Close 方法中對 Dispose、Connection 或任何其他托管對象調用 DataReader 或 Finalize,。Do 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.

有關打開和關閉連接與關聯(lián)的事件的詳細信息,，請參閱Audit Login Event Class并Audit 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. 如果發(fā)現(xiàn)某連接不再連接到服務器，則會將其標記為無效,。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. 這種情況是因為檢查連接是否仍有效的系統(tǒng)開銷將造成與服務器的另一次往返，從而抵消了池進程的優(yōu)勢,。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. 發(fā)生此情況時,，初次嘗試使用該連接將檢測連接是否曾斷開，并引發(fā)異常,。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 引入了兩種新的方法來清除池：ClearAllPools 和 ClearPool,。 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools 清除指定提供程序的連接池，ClearPool 清除與特定連接關聯(lián)的連接池,。ClearAllPools 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

連接是根據(jù)事務上下文來從池中取出并進行分配的,。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. 如果發(fā)出這樣的請求,，而沒有可用的池連接，則會從池的非事務性部分取出一個連接并登記,。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. 如果在池的每個區(qū)域都沒有可用的連接,，則會創(chuàng)建一個新的連接并登記。If no connections are available in either area of the pool, a new connection is created and enlisted.

當連接關閉時,，它將被釋放回池中,，并根據(jù)其事務上下文放入相應的子部分。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. 有關詳細信息,，請參閱ConnectionString。For more information, see ConnectionString.

池碎片Pool Fragmentation

池碎片是許多 Web 應用程序中的一個常見問題,，應用程序可能會創(chuàng)建大量在進程退出后才會釋放的池,。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

連接根據(jù)連接字符串以及用戶標識來建立池連接。Connections are pooled according to the connection string plus the user identity. 因此,，如果使用網(wǎng)站上的基本身份驗證或 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. 盡管這樣可以提高單個用戶的后續(xù)數(shù)據(jù)庫請求的性能,，但是該用戶無法利用其他用戶建立的連接,。Although this improves the performance of subsequent database requests for a single user, that user cannot take advantage of connections made by other users. 這樣還使每個用戶至少產生一個與數(shù)據(jù)庫服務器的連接。It also results in at least one connection per user to the database server. 這對特定 Web 應用程序結構會產生副作用,，因為開發(fā)人員必須衡量安全性和審計要求,。This is a side effect of a particular Web application architecture that developers must weigh against security and auditing requirements.

因為許多數(shù)據(jù)庫產生的池碎片Pool Fragmentation Due to Many Databases

許多 Internet 服務提供商在一臺服務器上托管多個網(wǎng)站。Many Internet service providers host several Web sites on a single server. 他們可能使用單個數(shù)據(jù)庫確認窗體身份驗證登錄,，然后為該用戶或用戶組打開與特定數(shù)據(jù)庫的連接,。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. 與身份驗證數(shù)據(jù)庫的連接將建立池連接，供每個用戶使用,。The connection to the authentication database is pooled and used by everyone. 但是,，每個數(shù)據(jù)庫的連接存在一個獨立的池，這會增加與服務器的連接數(shù),。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. 連接到服務器上的相同數(shù)據(jù)庫而不是為每個用戶或組連接到單獨的數(shù)據(jù)庫，然后執(zhí)行 Transact-SQLTransact-SQL USE 語句來切換到所需數(shù)據(jù)庫,。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. 以下代碼段演示如何創(chuàng)建與 master 數(shù)據(jù)庫的初始連接,，然后切換到 databaseName 字符串變量中指定的所需數(shù)據(jù)庫。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 系統(tǒng)存儲過程激活了 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 中創(chuàng)建應用程序角色,。For more information, see Creating Application Roles in SQL Server.