- 创建资源池
- 创建负载组
- 更新内存中的配置
- 创建分类函数
* 按照登录的用户名进行判断
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.
- 注册分类函数并更新内存中的配置
- 查看基本信息
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.
- 修改分类函数(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.
- 测试