[资源管理] SQL Server 通过Resouce Governor来限制用户资源的使用

1. 创建资源池
   CREATE RESOURCE POOL [rp_test] WITH(min_cpu_percent=0,
   max_cpu_percent=1,
   min_memory_percent=0,
   max_memory_percent=1,
   AFFINITY SCHEDULER = AUTO
   )
   GO
2. 创建负载组
   CREATE WORKLOAD GROUP [wlp_test] WITH(group_max_requests=10,
   importance=High,
   request_max_cpu_time_sec=0,
   request_max_memory_grant_percent=25,
   request_memory_grant_timeout_sec=60,
   max_dop=10) USING [rp_test]
   GO
3. 更新内存中的配置
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   GO
4. 创建分类器函数
   USE master
   GO

create FUNCTION user_bind_resource()
RETURNS sysname
WITH schemabinding
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME()  in ('usser1','user2))
          SET @workload_group_name = 'wlp_test'
    RETURN @workload_group_name
END
GO
5. 注册分类器函数并更新内存中的配置。
ALTER RESOURCE GOVERNOR WITH (classifier_function = dbo.user_identify);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
6. 查看基本信息

a. 查看所有内部资源池配置
select * from sys.resource_governor_resource_pools

b. 查看所有外部资源池配置
SELECT * FROM sys.resource_governor_external_resource_pools;

c. 查看所有工作负载组配置
SELECT * FROM sys.resource_governor_workload_groups;

d. 验证分类器函数是否存在以及是否启用
--- Get the classifier function Id and state (enabled).  
SELECT * FROM sys.resource_governor_configuration;  
GO  
--- Get the classifer function name and the name of the schema  
--- that it is bound to.  
SELECT   
	  object_schema_name(classifier_function_id) AS [schema_name],  
	  object_name(classifier_function_id) AS [function_name]  
FROM sys.dm_resource_governor_configuration; 

e. 使用以下查询获取资源池和工作负荷组的当前运行时数据。
SELECT * FROM sys.dm_resource_governor_resource_pools;  
SELECT * FROM sys.dm_resource_governor_workload_groups;  
GO
f. 查询每个组中包含的会话
SELECT s.group_id, CAST(g.name as nvarchar(20)),s.login_name, s.session_id, s.login_time, 
	CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))  
FROM sys.dm_exec_sessions AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS g  
	ON g.group_id = s.group_id  
ORDER BY g.name;  
GO

g. 查看每个组中包含的请求
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, 
	r.start_time, r.command, r.sql_handle, t.text   
FROM sys.dm_exec_requests AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS g  
	ON g.group_id = r.group_id  
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t  
ORDER BY g.name;  
GO

7. 修改function
   a. 取消分类函数注册并更新内存中的配置
   ALTER RESOURCE GOVERNOR WITH (classifier_function = null);
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   GO

   b. 修改function
   USE master
   GO

   alter FUNCTION user_bind_resource()
   RETURNS sysname
   WITH schemabinding
   AS
   BEGIN
       DECLARE @workload_group_name AS sysname
         IF (SUSER_NAME()  in ('user1','user2','wxbtest'))
             SET @workload_group_name = 'wlp_test'
       RETURN @workload_group_name
   END
   GO

   c. 注册分类器函数并更新内存中的配置。
   ALTER RESOURCE GOVERNOR WITH (classifier_function = dbo.user_bind_resource);
   ALTER RESOURCE GOVERNOR RECONFIGURE;
   GO

8. 测试
   开启两个窗口，分别执行如下语句，通过permon监控资源使用情况
   DECLARE @CNT INT
   WHILE 1=1
   BEGIN
   SELECT @CNT=COUNT(*) FROM sys.tables
   END