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. 即使使用者 ID 和密碼相同,不同的 SqlCredential 執行個體仍將使用不同的連線集區。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物件,或藉由開啟內的所有連線usingC# 中的陳述式或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. 如需詳細資訊,請參閱使用陳述式如何: 處置系統資源適用於 Visual Basic。For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

注意

請不要在您類別之 Close 方法中的 DisposeConnection 或任何其他 Managed 物件上呼叫 DataReaderFinalizeDo not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. 在完成項中,只需釋放類別直接擁有的 Unmanaged 資源。In a finalizer, only release unmanaged resources that your class owns directly. 如果類別未擁有任何 Unmanaged 資源,請不要在類別定義中包含 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.

注意

從連接集區中擷取連接或將連接傳回連接集區時,系統不會在伺服器上引發登入和登出事件。Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool. 這是因為當連接傳回連接集區時,連接實際上並未關閉。This is because the connection is not actually closed when it is returned to the connection pool. 如需詳細資訊,請參閱Audit Login Event ClassLogout Event Class< SQL Server 線上叢書 》 中。For more information, see Audit Login Event Class and Audit Logout Event Class in SQL Server Books Online.

移除連接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 引進兩種清除集區的新方法:ClearAllPoolsClearPool 2.0 introduced two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools 會清除指定提供者的連接集區, ClearPool 會清除與特定連接相關聯的連接集區。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

從集區中描繪連接,並根據交易內容進行指派。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

許多網際網路服務提供者在單一伺服器上裝載多個網站。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. 如需詳細資訊,請參閱知識庫文件中,「SQL 應用程式角色錯誤 OLE DB 資源共用。 」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

連接共用Connection Pooling
SQL Server 和 ADO.NETSQL Server and ADO.NET
效能計數器Performance Counters
ADO.NET Managed 提供者和 DataSet 開發人員中心ADO.NET Managed Providers and DataSet Developer Center