SQL Server 連線架構用戶端深度解析 (2)：連線記憶體、語言事件與 RPC、執行計畫重用機制

連線記憶體配置：封包緩衝區

• 每條連線保留 3 個網路封包緩衝區
• 緩衝區大小 = sp_configure 'network packet size'（預設 4 KB）
• 記憶體來源判斷：
  • 封包大小 < 8 KB → 來自 Buffer Pool (緩衝池)
  • 封包大小 ≥ 8 KB → 來自 MemToLeave (保留記憶體區域，非緩衝池管理)

不同提供者預設封包大小差異

建議：ODBC/OLE DB 連接字串加入 Packet Size=8192 統一使用 8 KB，將緩衝區移至 MemToLeave。

事件類別：語言事件 vs 遠程程序呼叫 (RPC)

語言事件

• T-SQL 批次以純文字傳送
• ADO 範例：CommandType = adCmdText、CommandText = "SELECT * FROM Orders WHERE OrderID = 10248"
• 甚至 adCmdTable 也會產生 SELECT * FROM TableName 文字批次

RPC 事件

• 參數化呼叫預存程序或參數化批次
• ADO 範例：CommandType = adCmdStoredProc、CommandText = "usp_GetOrder"、Parameters.Append ...
• TDS 封包結構：程序名 + 參數中繼資料 + 參數值，非文字解析

為何 RPC 效能更優？

1. 執行計畫重用：參數化查詢產生參數化計畫，後續不同參數值直接重用，避免重複編譯
2. 編譯成本降低：省去解析、標準化、簡單參數化嘗試
3. 記憶體效率：計畫快取僅存儲單一參數化計畫，而非大量臨時計畫
4. 安全性：原生參數化免疫 SQL 注入

SQL Server 自動參數化限制

• 僅針對簡單查詢嘗試簡單參數化 (Simple Parameterization)
• 複雜查詢（JOIN、子查詢、聚合、DML）不會自動參數化
• 強制參數化：ALTER DATABASE SET PARAMETERIZATION FORCED（風險：某些查詢計畫不穩定）

最佳實務：強制使用 RPC 模式

ADO.NET 範例

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("usp_GetOrdersByDate", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = startDate;
    cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = endDate;
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        // 處理結果
    }
}

動態 SQL 參數化 (sp_executesql)

DECLARE @sql NVARCHAR(MAX) = N'
    SELECT OrderID, OrderDate, CustomerID
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
      AND CustomerID = @CustID';

EXEC sp_executesql @sql,
    N'@StartDate DATE, @EndDate DATE, @CustID NCHAR(5)',
    @StartDate = '2024-01-01',
    @EndDate   = '2024-01-31',
    @CustID    = 'ALFKI';

效能監控指標

總結

• 連線記憶體：統一 8 KB 封包 → MemToLeave，減輕緩衝池壓力
• 事件類型：盡可能使用 RPC (參數化)，避免臨時文字批次
• 計畫重用：參數化是核心，配合 sp_executesql 或預存程序
• 監控編譯比率：高編譯率通常源於非參數化查詢或計畫快取壓力