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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

3. 更新内存中的配置

登录后复制

ALTER RESOURCE GOVERNOR RECONFIGURE;
  GO

• 1.
• 2.

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

  * 按照用户组来控制
    Create FUNCTION user_bind_resource()
    RETURNS sysname
    WITH schemabinding
    AS
    BEGIN
        DECLARE @workload_group_name AS sysname
          IF (IS_MEMBER('domain\gp_name')=1)   #添加要限制AD域组，如果有其他判断可以参考is_rolemember,is_srvrolemember函数
              SET @workload_group_name = 'wlp_test'
        RETURN @workload_group_name
    END
    GO

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.

5. 注册分类函数并更新内存中的配置

登录后复制

ALTER RESOURCE GOVERNOR WITH (classifier_function = dbo.user_identify);
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    GO

• 1.
• 2.
• 3.

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.

8. 测试

登录后复制

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

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.