單一 SQL Server 實例承載多個高負載資料庫時,單一佔用資源導致其他業務餓死是常見痛點。Resource Governor(資源管理員)透過資源池與工作負載群組,實現 CPU/記憶體/並行度的硬性配額隔離。
場景:MarketingDB(報表查詢,低優先級)與 CoreDB(核心交易,高優先級)共享實例。
USE master; GO SELECT * FROM sys.dm_resource_governor_configuration; GO
若 is_enabled = 0 且無自訂資源池,表示未啟用。
-- 核心交易池:保障 90% CPU
CREATE RESOURCE POOL CorePool
WITH (MIN_CPU_PERCENT = 50, MAX_CPU_PERCENT = 90,
MIN_MEMORY_PERCENT = 50, MAX_MEMORY_PERCENT = 90);
GO
-- 行銷報表池:限制 10% CPU,防止大查詢佔滿資源
CREATE RESOURCE POOL MarketingPool
WITH (MIN_CPU_PERCENT = 0, MAX_CPU_PERCENT = 10,
MIN_MEMORY_PERCENT = 0, MAX_MEMORY_PERCENT = 20);
GO
CREATE WORKLOAD GROUP CoreGroup
USING CorePool
WITH (IMPORTANCE = HIGH,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
MAX_DOP = 4);
GO
CREATE WORKLOAD GROUP MarketingGroup
USING MarketingPool
WITH (IMPORTANCE = LOW,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 10,
MAX_DOP = 1); -- 串行執行防止並行壓垮資源
GO
CREATE FUNCTION dbo.ClassifierFunction()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME;
IF APP_NAME() LIKE '%CoreApp%'
SET @GroupName = 'CoreGroup';
ELSE IF APP_NAME() LIKE '%ReportApp%'
SET @GroupName = 'MarketingGroup';
ELSE
SET @GroupName = 'default';
RETURN @GroupName;
END;
GO
SELECT * FROM sys.dm_resource_governor_resource_pools; SELECT * FROM sys.dm_resource_governor_workload_groups; SELECT * FROM sys.dm_exec_sessions WHERE group_id > 2; -- 查看被分類的會話
sys.dm_resource_governor_resource_pools:即時 CPU/記憶體使用量 vs 配額。sys.dm_exec_query_stats 結合 group_id 分析各群組查詢效能。ALTER RESOURCE POOL ... WITH (MAX_CPU_PERCENT = ...); ALTER RESOURCE GOVERNOR RECONFIGURE; 即時生效,無需重啟。| 版本 | Resource Governor 支援 |
|---|---|
| Enterprise / Developer / Evaluation | 完整支援 |
| Standard / Express / Web | 不支援 |