高級打字員的技術雲

還有技術債…

2011-06-25

[ASP.net] 利用SQL Server的活動監視器(圖形化介面)瞭解Connection Pool運作

[ASP.net] 利用SQL Server的活動監視器(圖形化介面)瞭解Connection Pool運作

好像滿多家公司應徵考試很喜歡考Connection pool觀念(縱使觀念歸觀念，實務上做起來是另一回事XD)

在網路上隨便找都有介紹Connection pool的觀念，例如：ADO.NET的Connection Pool ，但幾乎都是文字敘述為多，看完後，若沒深入瞭解過Connection Pool的經驗

以上那篇文章講的「Connection和連線」倒底哪個是指哪個呀 囧rz～會有這樣的疑問

所以為了方便以後跳槽找工作時期，可以有個筆記複習，這邊整理一個圖形畫面版，利用SQL Server的活動監視器瞭解Connection Pool的機制

 

測試環境Windows 7 家用進階版、SQL Server 2008 Express(資料庫選項Auto Close自動關閉為False和Enterprise版預設值一樣)

ASP.net 4(IIS上掛的AppPool為.net 4 Integrated管線模式)

ASP.net程式的環境配置

ASP.net 的 Max pool size預設為100，請參考SQL Server .NET Framework 資料提供者的連接共用，為方便測試，Web.config裡把Max pool size改成3，Min pool size維持預設0

Web.config設定

那個綠色註解等以下的Lab看完，就知道原因了。

.aspx 程式碼

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="立即執行完成的SQL" onclick="Button1_Click" />
    </div>
    <div>
        <asp:Button ID="Button2" runat="server" Text="SQL執行了20秒" onclick="Button2_Click" />
    </div>
    </form>
</body>
</html>

 

.cs 後置程式碼

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class _Default : System.Web.UI.Page
{
    string Conn_E = WebConfigurationManager.ConnectionStrings["Conn_E"].ConnectionString;
    protected void Button1_Click(object sender, EventArgs e)
    {
        object obj = this.GetSingle("Select 'Hi'");

        Response.Write(obj.ToString());
    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        object obj = this.GetSingle(@"Begin Transaction 
                                      Select 'Waited 20 second....'
                                      Waitfor Delay '00:00:20'
                                      Commit Transaction ");

        Response.Write(obj.ToString());
    }



    protected object GetSingle(string sql) 
    {
            SqlConnection conn = new SqlConnection(this.Conn_E);
            SqlCommand cmd = new SqlCommand(sql, conn);
            conn.Open();
            object obj = cmd.ExecuteScalar();
            conn.Close();
            return obj;
    }

}

 

程式執行畫面：

image

目前的活動監視器：

sqlready

ASP.net還沒執行到跟DB溝通的程式碼，所以目前活動監視器上的應用程式都是SSMS，跟ASP.net相關的還沒出現

 

Step1. 接著按下網頁上第一個按鈕「立即執行完成的SQL」：

新增一條連線

可以看到應用程式多了一個.Net SqlClient Data Provider (而且SQL語法馬上執行完畢，狀態非執行SQL語法中)

另也可以觀察到，SQL作業執行完，工作階段還不會立刻消失，它還會留在DB上逗留一下

 

Step2. 然後我們在網頁上多按10次第一個Button來看一下活動監視器上應用程式的變化

多按幾次按鈕

仍然只有一個工作階段。

 

這次換別顆按鈕

Step 3. 連續按5次第二個Button「SQL執行了20秒」

image

我明明按了5次按鈕，但DB的工作階段仍只有三個，所以到這邊可以歸納一下

從Step1到Step3來看，如果工作階段還有閒置的話，ADO.net就利用閒置的工作階段處理作業

當一個工作階段忙碌中的話，則程式判斷目前工作階段是否小於Max pool size的設定

是的話，就產生新的工作階段，直到Max pool size的設定值(目前就僅觀察到的結論，還沒到Connection pool那裡喔^_^)

 

然後，那些工作階段，何時會消失呢？

這邊我先把IIS的網站停止再啟動

image

 

image

SQL Server裡.net SqlClient Data Provider的應用程式也跟著不見。

 

Step 4. 然後先紀錄一下，我再度按下第一個按鈕「立即執行SQL語法」後，馬上擷取圖片該工作階段的時間點

image

網頁瀏覽器關閉，DB上的工作階段閒置著，我就邊聽音樂邊盯著活動監視器邊等吧XD

 

 

等工作階段一結束，馬上擷取圖片和時間

image

可以發現.net SqlClient Data Provider應用程式的工作階段在DB上閒置大約7分後就消失了，被誰回收，這邊就保留，我不能肯定是被IIS還是被DB給回收掉

註1：追加SQL Server Profiler的觀察時間(詳見文章底下)

 

 

Step 5. 接著再測試另一種情況，現在我們已知有閒置的工作階段，ADO.net就使用該閒置的工作階段作業

那如果沒有任何閒置的工作階段，且工作階段數都已達到Max pool size的設定值時會怎樣呢?

 

承接上面，目前DB工作階段裡沒有任何一個.net SqlClient Data Provider應用程式，我在網頁畫面上一直連續按第二個按鈕100次，然後再馬上按第一個按鈕(第一個按鈕為馬上執行完SQL)

然後網頁畫面如下：

image

 

結果：

image

 

Step 6.

我把第二個按鈕的Click事件改寫一下

    protected void Button2_Click(object sender, EventArgs e)
    {
        object obj = this.GetSingle(@"Begin Transaction 
                                      Select 'Waited 20 second....'
                                      Waitfor Delay '00:05:00' /*等5分鐘*/
                                      Commit Transaction ");

        Response.Write(obj.ToString());
    }

然後在網頁畫面上一直連續按第二個按鈕10次，再馬上按第一個按鈕，觀察情況

image

已超過連接逾時的設定。在取得集區連接之前超過逾時等待的時間，可能的原因為所有的共用連接已在使用中，並已達共用集區大小的最大值。

image

5分鐘時間太長了，連接TimeOut

 

雖然DB的工作階段看似都閒置中

image

回到網頁預設畫面，再按一次第一個按鈕，仍會出現已超過連接逾時的設定。在取得集區連接之前超過逾時等待的時間，可能的原因為所有的共用連接已在使用中，並已達共用集區大小的最大值。

的錯誤訊息

慢慢有點感受到背後有股Connection pool機制在運作

這裡就當作Connection pool裡每個Connection都去執行5分鐘的SQL語法，長時間不回歸Pool，才出現以上錯誤

 

Step 5.和Step 6.可以歸納一個結論

程式執行後，會去尋找Connection pool裡閒置的Connection，沒有的話就等待

等待有兩個結果，如果等到有閒置的Connection pool裡的Connection的話，就使用該Connection去執行閒置的DB工作階段

如果等不到的話，就出現連接逾時錯誤

 

 

Step 7.再測試另一種情況

把5分鐘改回20秒

protected void Button2_Click(object sender, EventArgs e)
    {
        object obj = this.GetSingle(@"Begin Transaction 
                                      Select 'Waited 20 second....'
                                      Waitfor Delay '00:00:20'
                                      Commit Transaction ");

        Response.Write(obj.ToString());
    }

停止>啟動網站並確定DB上的工作階段都清除

接著這次我連續按10次第二個按鈕，然後馬上模擬DBA人員把.net SqlClient Data Provider的處理序全手動砍掉

image

 

回到網頁預設畫面，點選第一個按鈕後

在傳送要求至伺服器時發生傳輸層級的錯誤。 (provider: Shared Memory Provider, error: 0 - 管道的另一端上無任何處理程序。)

image

再次輸入URL，重新整理網頁，按下第一個按鈕

image

原本我以為手動刪除工作階段，該SQL作業就不會回歸到Connection pool ，不過由上面圖來看，只要程式碼有呼叫.Close();，SqlConnection一定都會回歸到Connection Pool裡

只是DB工作階段看來又和Connection pool有關聯？

 

Step 8 最終測試

我在公司裡見過這種寫法

 

 <div>
       <asp:Button ID="Button3" runat="server" Text="執行SQL DataReader" onclick="Button3_Click" />
    </div>

 

 

protected void Button3_Click(object sender, EventArgs e)
    {
        SqlDataReader sdr = this.ExecuteReader("Select 'Hello!DataReader' ");
        string result = string.Empty;
        if (sdr.Read()) result = sdr[0].ToString();
        sdr.Close();//這裡確實把SqlDataReader Close掉，但剛剛建立的連線會一直停留在DB的工作階段上
        
        Response.Write(result);
    }
    protected SqlDataReader ExecuteReader(string sql)
    {
        SqlConnection conn = new SqlConnection(this.Conn_E);
        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();//建立連線或尋找Connection pool和DB上閒置的Connection和工作階段
        SqlDataReader sdr = cmd.ExecuteReader();

        return sdr;//為了其他方法要使用SqlDataReader物件，所以conn不可以Close()


    }

這種的我已測試過，DB上的工作階段過了7分鐘後永遠不會消失

不過還是執行一下網頁，按下第三個新增的按鈕三次

image

這邊我再點選第一個按鈕

image

正常情況下，第一個按鈕的執行應該要Reuse閒置的工作階段，但因為SqlConnection沒有Close();，所以SqlConnection一直佔用Pool Connection，回不到Connection Pool

造成看似DB掛著閒置的工作階段，實際上新的SQL指令無法進到DB

假設DBA手動把該三個工作階段殺掉，

image

之前已知道殺掉工作階段，只是造成Connection Pool裡的Connection找不到處理序

這邊來看看，我再次按下網頁上第一個按鈕時的畫面

image

由上圖可以推論，SqlConnection沒有Close();，即時DBA手動刪除工作階段，SqlConnection仍不會回歸到Pool

也就是說忘記Close();的下場就是：每執行一次未Close();的敘述句，就當作Max pool size減1 吧(￣▽￣|||)，弄不回來也沒辦法Reuse

 

 

 

 

 

最後附上個人測試結果流程概念圖(微軟官方ADO.net課程教材也只是淺談Connection Pool機制所以很難肯定真正ADO.net的Connection Pool就是照以下流程走)

註：正常情況下，一個Pool Connection對應一個DB上的工作階段。


測試的範例檔下載

 

註1：2011.6.26 追加SQL Server Profiler的觀察時間

測試環境Win2008R2、SQL2008R2 Enterprise，程式碼不變，測試方式不變

以下為SQL Server Profiler觀察到ASP.net應用程式在DB工作階段的登入執行SQL指令，閒置後的登出時間：

SQL Server Profiler觀察到大約6分鐘，該工作階段就登出了

 

2011.12.06 追記

Max Pool Size最大可以設多少？微軟相關人員表示↓

ADO.NET数据库连接池连接数

2012.3.23 追記

用以下的代碼測試

 string Conn_E = WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
        using(SqlConnection conn = new SqlConnection(Conn_E))
        {
        SqlCommand cmd = new SqlCommand(sql, conn);

        conn.Open();

        object obj = cmd.ExecuteScalar();
            return obj;
        }

就算return了，using 會把SqlConnection做Dispose()和Close()動作

另外，IIS重新啟動站台或修改儲存Web.config都會做clear pool動作

SqlConnection.ConnectionString 屬性

海阔天高  SQL超时解决方法

本頁段落