SQL Server Connection Pool基礎

串連到資料庫一般情況下是比較耗費時間的, 因為這個過程是由一些耗時較長的步驟組成的.

1. 建立一個物理串連, 要麼是socket, 要麼是named pipe.

2. 與資料庫伺服器之間的握手是必須的.

3. 連接字串必須要被解析.

4. 串連需要經過伺服器認證(authenticated).

5. 還需要經過一些許可權等方面的校正.

6. 等等.

 

在實際應用中, 絕大多數的應用程式僅需使用一個或幾個對資料庫連接的配置. 這意味著, 在應用程式執行的過程中, 許多一模一樣的串連會被重複地開啟和關閉. 為了最小化開啟資料庫連接的損耗, ADO.NET中使用了一種叫做connection pooling的最佳化技術.

 

Connection Pooling會減少開啟建立串連的次數. Pooler擁有物理串連的所有權. 對每一種資料庫連接配置它都讓幾個處於活動狀態的串連持續地處於活動狀態, Pooler通過這種方式來管理資料庫串連. 每當一個使用者對一個connection調用Open方法, Pooler都會在串連池中尋找一個可用的active的串連. 如果有這樣的一個connection可用, 它就返回給調用者這個connection, 而不是去開啟一個新串連. 當應用程式對connection調用Close方法的時候, pooler會把這個connection返回給pooler, 放入活動狀態的可用的connection的池中, 而不是將之關閉. 一旦串連返回了pool中, 它就準備好了下一次再Open調用的時候被返回給調用者.

 

只有當connection是同一種configuration的時候, 他們才可以被放入同一個pool中. ADO.NET會同時維護多個pool, 每個pool對應一種configuration. Connection通過connection string(連接字串)來分類到不同的pool中, 另一個標準就是當ingetrated security被使用的時候的Windows identity. Connection還按是否被列入transaction而被分類放入池中.

 

Connection被按池的方式管理可以顯著地增強你的應用程式的效能和可擴充性. 預設地, connection pool是由ADO.NET來開啟的. 除非你顯式地關掉它, 只要你的應用程式開啟和關閉connection, pooler都會對connection的操作進行最佳化. 你還可以使用一些對資料庫連接字串的修飾符來控制pooler的行為.

 

Pool的建立和分配

===========

當一個connection第一次被開啟的時候, 一個connection pool就會按照連接字串的精確匹配演算法被建立出來. 每個connection pool都與一個獨一無二的連接字串相關聯. 當一個新的串連建立起來, 如果連接字串與已經存在的pool的連接字串都不匹配, 那麼一個新的pool就會被建立出來. Connection會分進程, 分application domain, 分connection string來進入不同的pool. 還有, 如果開啟了integrated security, 還要按照windows identity來分配.

 

為pool添加connection

============

當一個pool被建立出來的時候, 就會有很多connection對象也被建立出來並添加到pool裡, 這樣的話minimum pool size的條件會被滿足. 之後connection會按照需求不斷地被添加進來, 直到到達maximum pool size的值(預設該值為100).

當connection被closed或disposed之後, 會被放回到pool中.

當一個SqlConnection對象被請求, 如果pool中有可用的connection, 那麼這個connection就會被返回. 為了滿足"可用"這個條件, 這個connection必須是當前未被使用的, 有匹配的transacton context的, 或沒被與任何transaction context關聯的, 並且有對伺服器的一個合法的連結.

 

Connection pooler會返回之前別人釋放的connection給新的請求. 如果pool的最大值已經到了, 並且仍然沒有可用的connection的話, 那麼該請求就會進入隊列(預設的進入隊列後的timeout的值是15秒). Pooler然後會嘗試回收任何connection.如果pooler不能在timeout之前滿足隊列裡的請求, 那麼就會拋出一個異常.

該異常應該是這樣的:

Exception type: System.InvalidOperationException
Message: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
InnerException: <none>
StackTrace (generated):
    SP       IP       Function
    0636F4B8 653CF486 System_Data_ni!System.Data.ProviderBase.DbConnectionFactory.GetConnection(System.Data.Common.DbConnection)+0x133f46
    0636F4C4 652D69BA System_Data_ni!System.Data.ProviderBase.DbConnectionClosed.OpenConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory)+0x6a
    0636F4F8 652F5440 System_Data_ni!System.Data.SqlClient.SqlConnection.Open()+0x70

 

移除connection

===========

Connection pooler會一處閑置時間過久的connection.

另外, 如果connection沒有在跟目的伺服器有串連的話, 這個connection就會被標記為invalid, Invalid的connection僅會在closed, 或reclaimed的時候被從pool中移除出去.

 

注意:

SQL server作為一個應用程式不會也不該主動地檢測用戶端地串連並確定這個串連的狀態. 底層的跨進程通訊(Inter-Process Communications (IPCs)), 比如說named pipes, IPX/SPX 或 TCP/IP sockets會負責管理用戶端的串連.

跨進程通訊一般來說有自己的機制來管理用戶端的串連. 當用戶端串連有一段時間不響應的時候, 典型地, SQL的Windows Server要麼會通過發送"keep alive"訊號來維持這種狀態, 要麼會在等待該連結閑置某段時間之後,清除掉這個串連. 然而, "keep-alive"包預設不會被用戶端應用程式發送. 應用程式需要在他的串連上開啟這項特性. 

在某些情況下(比如client general protection fault), 用戶端還會相應伺服器的keep-alive檢測包, 即使用戶端應用程式已經掛了. 在這種情況下, 伺服器可能會永遠保持這這個連結, 只要用戶端機器不關機.

如果一個Windows Server基於某種原因不關掉死去的connection, 那麼SQL Server有全假設這個connection還是處於活動狀態的, 所以就不會把它清除掉.

 

補充:

SQL connection是種很便宜的資源, 一台SQL Server裡有兩三千個也不是問題.

並不是一個connection就會關聯一個SQL的線程.

提起了SQL的線程, 為了給大家一個直觀的感受, 大家可以運行一下下面的語句. 它返回的是SQL的工作者線程以及他們的狀態.

select * from sys.dm_os_workers

這些工作者線程是按照線程池的方式來管理的. 用白話說就是有活兒就幹, 沒活兒就把cpu放掉去歇著.

 

可以通過下面的語句來查看全部的connection

select * from sys.dm_exec_sessions where session_id > 50

 

通過下面的語句來查看所有非SQL系統的正在運行中的session

select db_name(database_id), * from sys.dm_exec_requests where session_id > 50 

 

通過下面的語句來確定處於閑置狀態中的connection:

select * from sys.dm_exec_sessions where status = 'sleeping' and session_id > 50

 

通過下面的語句來查看哪些主機上的哪些進程連到了哪些資料庫, 同樣的connection有多少.

select db_name(dbid),hostname,hostprocess,COUNT(*) as countsfrom sysprocesses where spid > 50group by hostname, dbid, hostprocessorder by counts desc

拿到了進程號, 就可以繼續抓dump或者找研發人員來排查該進程上的可能的sql connection泄露的問題了.

 

如果connection很多, 然而閑置的也很多, 那麼說明SQL的狀態還正常.

Connection數量多本身並不說明問題.

 

參考資料

===================

SQL Server Connection Pooling (ADO.NET)

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

How to troubleshoot orphaned connections in SQL Server

http://support.microsoft.com/kb/137983