1.	创建资源池
CREATE RESOURCE POOL [rp_test] WITH(min_cpu_percent=0, 
max_cpu_percent=5, 
min_memory_percent=0, 
max_memory_percent=5, 
MIN_IOPS_PER_VOLUME=1000,
MAX_IOPS_PER_VOLUME=5000
AFFINITY SCHEDULER = AUTO
)
GO
2.	创建负载组
CREATE WORKLOAD GROUP [wg_test] WITH(group_max_requests=50, 
importance=High, 
request_max_cpu_time_sec=0,   #控制单个request的cpu使用
request_max_memory_grant_percent=50,   #控制单个request的内存使用
request_memory_grant_timeout_sec=60, 
max_dop=4) 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 = 'wg_test'
    RETURN @workload_group_name
END
GO
5.	注册分类器函数并更新内存中的配置。
ALTER RESOURCE GOVERNOR WITH  (classifier_function = dbo.user_identify);
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.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.

 https://learn.microsoft.com/zh-cn/sql/relational-databases/resource-governor/resource-governor-resource-pool?view=sql-server-ver16