您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005/2008 SqlCacheDependency 查詢通知：從輪詢到推播的緩存失效實戰

編輯：關於SqlServer

演進：輪詢 vs 查詢通知

SQL 7.0/2000：`SqlCacheDependency` 採**輪詢** (`polling`)，定時查詢系統表檢查資料變更，延遲高、持續消耗資源。
SQL 2005/2008+：引入**查詢通知** (`Query Notification`)，透過 **Service Broker** 非同步訊息隊列，資料變更即時推播通知 .NET 端，`SqlDependency` 觸發緩存失效，**零輪詢、低延遲、資源極省**。

完整啟用 6 步驟

1. 檢查/啟用 Service Broker


-- 檢查
SELECT DATABASEPROPERTYEX('YourDB', 'IsBrokerEnabled'); -- 1=已啟用

-- 啟用 (若卡住，重啟資料庫後優先執行此句)
ALTER DATABASE YourDB SET ENABLE_BROKER;

2. 授予訂閱權限 (關鍵！)


GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourAppUser];
-- ⚠️ sa/系統管理員無法授予此權限，必須使用普通資料庫使用者

3. 連接字串配置 (web.config)

4. 應用程式啟動/結束時啟動/停止監聽 (Global.asax)


void Application_Start(object sender, EventArgs e) {
    string connStr = ConfigurationManager.ConnectionStrings["AppConn"].ConnectionString;
    SqlDependency.Start(connStr);
}

void Application_End(object sender, EventArgs e) {
    string connStr = ConfigurationManager.ConnectionStrings["AppConn"].ConnectionString;
    SqlDependency.Stop(connStr);
}

5. 核心資料存取程式碼


private DataSet GetData() {
    const string CacheKey = "CityList";
    if (HttpRuntime.Cache[CacheKey] != null)
        return (DataSet)HttpRuntime.Cache[CacheKey];

    var ds = new DataSet();
    string connStr = ConfigurationManager.ConnectionStrings["AppConn"].ConnectionString;
    
    using (var conn = new SqlConnection(connStr)) {
        // 查詢必須符合嚴格限制：明確列名、兩部分命名、無 *、無聚合、無 UNION 等
        string sql = "SELECT CityId, CityName FROM dbo.Tbl_City";
        using (var cmd = new SqlCommand(sql, conn)) {
            var dep = new SqlCacheDependency(cmd);
            using (var adapter = new SqlDataAdapter(cmd)) {
                adapter.Fill(ds);
            }
            HttpRuntime.Cache.Insert(CacheKey, ds, dep);
        }
    }
    return ds;
}

6. 查詢語句嚴格限制 (必讀 MSDN)

必須指定完整欄位清單，禁用 `SELECT *`。
資料表必須使用兩部分名稱 (`dbo.Table`)。
不支援：`TOP`、`DISTINCT`、`聚合函數`、`JOIN`、`UNION`、`子查詢`、`NOLOCK`、`參數化視圖` 等。
參考：MSDN - Creating a Query for Notification

常見坑與排查

現象	原因	解決
緩存永不失效	使用 `sa` 連線，無 `SUBSCRIBE QUERY NOTIFICATIONS` 權限	建立專用低權限帳號並授權
啟用 Broker 卡死	資料庫有作用中連線/鎖	重啟資料庫 (WITH ROLLBACK IMMEDIATE) 後立即執行啟用
收到通知但資料未變	查詢不符合限制 (如用了 * 或 JOIN)，導致無效訂閱	重寫查詢符合規範
開發機正常，部署失效	防火牆阻擋 Service Broker 端口 (預設 4022)	開放端口或配置固定端口 `ALTER DATABASE SET NEW_BROKER`