RiCo技術農場

SQL2014以前只能針對CPU和記憶體做資源限制，

現在SQL2014 加強資源管理可針對IO做限制，

下面我簡單測試一下。

 

--Create group and pool
CREATE RESOURCE POOL PoolApUser
WITH (
     MIN_CPU_PERCENT = 15,
     MAX_CPU_PERCENT = 30,
     CAP_CPU_PERCENT = 40,
     --AFFINITY SCHEDULER = (0 TO 63, 128 TO 191),   
     MIN_MEMORY_PERCENT = 5,
     MAX_MEMORY_PERCENT = 20,
     MIN_IOPS_PER_VOLUME=20,
     MAX_IOPS_PER_VOLUME =50
      );
CREATE WORKLOAD GROUP ApUserIOGroup USING PoolApUser;

CREATE RESOURCE POOL PoolAdmin
WITH (
     MIN_CPU_PERCENT = 5,
     MAX_CPU_PERCENT = 10,
     CAP_CPU_PERCENT = 15,
     --AFFINITY SCHEDULER = (0 TO 63, 128 TO 191),
     MIN_MEMORY_PERCENT =5,
     MAX_MEMORY_PERCENT=15,
     MIN_IOPS_PER_VOLUME = 5,
     MAX_IOPS_PER_VOLUME = 10
      );
CREATE WORKLOAD GROUP AdminIOGroup USING PoolAdmin;

 

--建立分類函數
use master
go
CREATE FUNCTION dbo.ResourceGovernorClassifier ()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName SYSNAME;
IF SUSER_SNAME() = 'sa'
SET @GroupName = 'AdminIOGroup';
ELSE SET @GroupName = 'ApUserIOGroup';
RETURN @GroupName;
END;
GO

 

--註冊此分類函數並更新記憶體中組態
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.ResourceGovernorClassifier)
ALTER RESOURCE GOVERNOR RECONFIGURE

 

--sa 執行測試 ( MIN_IOPS_PER_VOLUME = 5,   MAX_IOPS_PER_VOLUME = 10 )

--Clear Cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO

select * into FactProductInventory_v1 from AdventureWorksDW2012.dbo.FactProductInventory

 

可以看到read /sec(紅色) 大約就在10上下震盪，而且整體執行時間較長(IO被限制較多)

 

 

--非sa使用者測試(  MIN_IOPS_PER_VOLUME=20,      MAX_IOPS_PER_VOLUME =50 )

--Clear Cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO

select * into FactProductInventory_v1 from AdventureWorksDW2012.dbo.FactProductInventory

可以看到read /sec(紅色) 大約就在50上下震盪，而且整體執行時間較短(IO被限制較少)

 

 

drop WORKLOAD GROUP ApUserIOGroup
drop RESOURCE POOL PoolApUser

drop WORKLOAD GROUP AdminIOGroup
drop RESOURCE POOL PoolAdmin

 

 

參考

資源管理員資源集區

CREATE RESOURCE POOL (Transact-SQL)

資源管理員工作負載群組

資源管理員分類函數

建立和測試分類使用者定義函數