27,579
社区成员
发帖
与我相关
我的任务
分享
--创建资源池
Create Resource Pool AdminQueries with(max_cpu_percent=100)
Create Resource Pool UserQueries with(max_cpu_percent=100)
--创建负载组
Create WorkLoad Group NightlyMaintenanceTasks USING AdminQueries;
Create WorkLoad Group AdhocAdmin USING AdminQueries;
Create WorkLoad Group SAPUsers USING UserQueries;
Create WorkLoad Group DailyExecReports USING UserQueries;
--创建分类器函数
USE master
GO
create FUNCTION class_func_1()
Returns sysname with schemabinding
begin
Declare @val sysname
--Handle workload groups defined by login names
IF db_name()='haozhaogang'
begin
SET @val='SAPUsers';
Return @val;
end
Return @val;
end
--将分类器函数绑定到资源调控器上
alter Resource Governor With(Classifier_Function=dbo.class_func_1);
--启用
ALter Resource Governor Reconfigure;
--禁用
ALTER RESOURCE GOVERNOR DISABLE;
--测试脚本(分别使用SAP_Login和sysadmin用户调用此脚本)
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
--删除自定义函数
DROP FUNCTION [dbo].[class_func_1];
alter resource governor with (classifier_function = null)
go
alter resource governor disable
go
drop function dbo.[class_func_1]
go