使用 SQL Server Resource Governor 限制特定資料庫的資源使用

1. 簡介

某次在正式環境使用者抱怨網頁速度太慢，會卡好幾分鐘，於是開始排查問題，發現自己連接SSMS的搜尋也是一樣卡，不太對勁，結果最後發現了是其他專案資料庫排程執行時過度消耗資源，導致其他專案的效能受到影響，最好的解決方案是應該切其他主機，不過沒有其他資源只能想想其他方法。本文將介紹如何使用 SQL Server 的 Resource Governor 功能來解決這個問題，有效地限制特定資料庫的資源使用。

2. Resource Governor 簡介

Resource Governor 是 SQL Server 提供的一個強大工具，用於管理 SQL Server 實例中的工作負載和資源消耗。它允許我們為不同的工作負載設置資源使用限制，確保關鍵任務能夠獲得所需的資源。

相比其他方法（如設置 MAXDOP），Resource Governor 提供了更細緻和動態的控制，能夠根據多種條件（如資料庫名稱、登入帳號等）來分配資源。

3. 設定 Resource Governor 的步驟

3.1 建立資源池 (Resource Pool)

資源池定義了可用的資源限制。

1
2
3
CREATE RESOURCE POOL PoolAdhocDB
WITH (MAX_CPU_PERCENT = 20, 
      MAX_MEMORY_PERCENT = 25);

3.2 建立工作負載群組 (Workload Group)

工作負載群組關聯到特定的資源池。

1
2
CREATE WORKLOAD GROUP GroupAdhocDB
USING PoolAdhocDB;

3.3 建立分類函數 (Classifier Function)

分類函數決定將請求分配到哪個工作負載群組。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
USE master;
GO

CREATE FUNCTION dbo.fn_WorkloadClassifier()
RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE 
               WHEN DB_NAME() = 'AdhocDatabase' THEN 'GroupAdhocDB'
               ELSE 'default'
           END
END;
GO

3.4 啟用 Resource Governor 設定

1
2
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_WorkloadClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

4. 驗證設定

執行以下查詢來檢查 Resource Governor 的組態：

1
2
3
SELECT * FROM sys.resource_governor_configuration;
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;

5. 注意事項

  • 確保在 master 資料庫中創建分類函數
  • 確保有足夠的權限（通常需要 CONTROL SERVER 權限）
  • 根據實際情況調整資源池的限制參數

6. 可能遇到的問題與解決方案

  1. 分類函數找不到的錯誤
    • 解決方案：確保函數名稱正確，且存在於 master 資料庫中
  2. 權限不足的問題
    • 解決方案：確保執行組態的帳號具有足夠的權限

7. 優化建議

  • 定期監控資源使用情況，可以使用 Dynamic Management Views (DMVs) 來追蹤資源使用
  • 根據監控結果調整資源池設定，確保資源分配合理

8. 總結

Resource Governor 為我們提供了一個強大的工具，用於管理 SQL Server 的資源分配。通過合理組態，我們可以：

  • 防止單一資料庫過度消耗資源
  • 確保關鍵業務的效能
  • 提高整體資料庫伺服器的穩定性和可預測性

在複雜的多租戶或多應用環境中，Resource Governor 的使用尤為重要，它能夠幫助 DBA 更好地平衡各種工作負載的需求。

9. 延伸閱讀