--創(chuàng)建資源池
 Create Resource Pool UserQueries with(max_cpu_percent=100)

--刪除資源池
drop Resource Pool UserQueries

--創(chuàng)建負(fù)載組
Create WorkLoad Group DailyExecReports USING UserQueries;

--刪除負(fù)載組
drop WorkLoad Group DailyExecReports

--創(chuàng)建資源池
Create Resource Pool AdminQueries with(max_cpu_percent=100)
Create Resource Pool UserQueries with(max_cpu_percent=100)

--創(chuàng)建負(fù)載組
Create WorkLoad Group NightlyMaintenanceTasks USING AdminQueries;
Create WorkLoad Group AdhocAdmin USING AdminQueries;
Create WorkLoad Group SAPUsers USING UserQueries;
Create WorkLoad Group DailyExecReports USING UserQueries;

--創(chuàng)建分類器函數(shù)
 USE master
 GO
 create FUNCTION class_func_1()
 Returns sysname with schemabinding
 begin
 Declare @val sysname
 --Handle workload groups defined by login names
 IF SUSER_SNAME()='SAP_Login'
 begin
 SET @val='SAPUsers';
 Return @val;
 end
 
 IF APP_NAME() like 'Microsoft SQL Server Management Studio%'
 begin
 Set @val='AdhocAdmin';
 Return @val;
 end
 
 IF IS_MEMBER('ReportUsers')=1
 begin
 Set @val='DailyExecReports';
 Return @val;
 end
 
 IF CONNECTIONPROPERTY('net_transport')='Shared memory' and IS_MEMBER('NightlyAdmin')=1
 begin
 Set @val='NightlyMaintenanceTasks';
 Return @val;
 end
 
 Return @val;
 end

--將分類器函數(shù)綁定到資源調(diào)控器上
 Alter Resource Governor With(Classifier_Function=dbo.class_func_1);

--啟用
 ALter Resource Governor Reconfigure;
--禁用
 ALTER RESOURCE GOVERNOR DISABLE;

--測(cè)試腳本(分別使用SAP_Login和sysadmin用戶調(diào)用此腳本）
 set nocount on 
 Declare @i int=100000000;
 Declare @s varchar(100),@count int;
 While @i>0
 begin
 Select @s=@@VERSION;
 select @count=COUNT(0) from sys.sysobjects 
 set @i=@i-1;
 end

Create Resource Pool AdminQueries with(max_cpu_percent=100)
Create Resource Pool UserQueries with(max_cpu_percent=100)

Create Resource Pool AdminQueries with(max_cpu_percent=10)
Create Resource Pool UserQueries with(max_cpu_percent=90)

--查看Session所在的資源池
 select s.session_id,s.login_name ,s.program_name,s.group_id,g.name 
 from 
 sys.dm_exec_sessions s join sys.dm_resource_governor_workload_groups g
 on s.group_id=g.group_id
 where session_id>50

--查看資源池情況
 select * from sys.dm_resource_governor_resource_pools