SqlServer性能优化 性能调控（十）

如何做资源的调控：

 1.建立资源池。

 2.创建工作负荷组

	create resource pool ImporPool with
	(
	min_cpu_percent=30,max_cpu_percent=100,
	min_memory_percent=40,max_memory_percent=95	
	)

	--不太重要的资源池
	create resource pool PooPool with
	(
	min_cpu_percent=5,max_cpu_percent=100,
	min_memory_percent=10,max_memory_percent=95
	)

	--创建工作负荷组
	create workload group ERPGroup with
	(
	--重要性
	importance=high
	)using ImporPool

	create workload group ADGroup with
	(
	--重要性
	importance=medium
	)using ImporPool


	create workload group ReportingGroup with
(
importance=high
)using PooPool

create workload group PrintGroup with
(
importance=medium
)using PooPool

 

--如何将应用与资源池进行对应呢？
--创建分类器函数

create function fn_resourceslip()
returns sysname with schemabinding
as
begin
return 
  case app_name()
  when 'erpapp' then 'ERPGroup'
  when 'adapp' then 'ADGroup'
  when 'reportingapp' then 'ReportingGroup'
  when 'printapp' then 'PrintGroup'
  end
end

  属性资源调控器：

 

在Web.config中：

--资源调控器的配置信息
select * from sys.dm_resource_governor_configuration
--查看资源池的信息
select * from sys.dm_resource_governor_resource_pools
--工作负荷组的情况
select * from sys.dm_resource_governor_workload_groups

--数据备份
create function fn_resourceslip()
returns sysname with schemabinding
as
begin
return 
  case user_name()
  when 'msshcj\administrator' then 'PrintGroup'
  when 'hruser' then 'ERPGroup'
  end
end