使用sql server 2008 资源调控器限制指定用户查询所使用CPU资源
Posted on 2009-07-26 19:31 nzperfect 阅读(4525) 评论(0) 编辑 收藏 举报资源调控器是sql server 2008新增中的功能,可以限制某些用户访问sql server所消耗的cpu、内存资源或是对某个库访问所所消耗的cpu、内存资源,可以在SQL Server 的 Enterprise Edition、Developer Edition 和 Evaluation Edition中使用。
配置资源调控器基本分为以下步骤:
1. 创建并配置一个资源调控器资源池,发生 CPU 争用时,该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽。
2. 创建并配置一个使用该池的资源调控器工作负荷组。
3. 创建一个“分类器函数”,它是一个用户定义函数 (UDF),其返回值供资源调控器用来对会话进行分类,以便将它们路由到适当的工作负荷组。
4. 将分类器函数注册到资源调控器。
5. 将更改应用于资源调控器内存中配置。
本测试示例是限制某查询用户USER_READONLY限制cpu最大为10%.
示例代码(代码参考msdn联机文档):
-- 配置资源调控器.
BEGIN TRAN
USE master;
-- 创建并配置一个资源调控器资源池,发生 CPU 争用时,
-- 该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽 10%
CREATE RESOURCE POOL pMAX_CPU_PERCENT_10
WITH
(MAX_CPU_PERCENT = 10);
GO
-- 创建并配置一个使用该池的资源调控器工作负荷组。
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_10
USING pMAX_CPU_PERCENT_10;
GO
-- 创建一个“分类器函数”,它是一个用户定义函数 (UDF),
-- 其返回值供资源调控器用来对会话进行分类,以便将它们路由到适当的工作负荷组
-- 本例是限制用户为:USER_READONLY所使用的cpu资源不超过10%
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = 'USER_READONLY')
SET @workload_group_name = 'gMAX_CPU_PERCENT_10'
RETURN @workload_group_name
END;
GO
-- 将分类器函数rgclassifier_MAX_CPU注册到资源调控器
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- 将更改应用于资源调控器内存中配置
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
BEGIN TRAN
USE master;
-- 创建并配置一个资源调控器资源池,发生 CPU 争用时,
-- 该资源池将限制分配给资源池中的请求的最大平均 CPU 带宽 10%
CREATE RESOURCE POOL pMAX_CPU_PERCENT_10
WITH
(MAX_CPU_PERCENT = 10);
GO
-- 创建并配置一个使用该池的资源调控器工作负荷组。
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_10
USING pMAX_CPU_PERCENT_10;
GO
-- 创建一个“分类器函数”,它是一个用户定义函数 (UDF),
-- 其返回值供资源调控器用来对会话进行分类,以便将它们路由到适当的工作负荷组
-- 本例是限制用户为:USER_READONLY所使用的cpu资源不超过10%
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = 'USER_READONLY')
SET @workload_group_name = 'gMAX_CPU_PERCENT_10'
RETURN @workload_group_name
END;
GO
-- 将分类器函数rgclassifier_MAX_CPU注册到资源调控器
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- 将更改应用于资源调控器内存中配置
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--包含资源调控器的当前内存中配置状态的行
select * from sys.dm_resource_governor_configuration
--当前资源池状态、资源池的当前配置以及资源池统计信息的相关信息
select * from sys.dm_resource_governor_resource_pools
--工作负荷组统计信息和工作负荷组当前在内存中的配置
select * from sys.dm_resource_governor_workload_groups
这样,资源资源调控器配置完成,下面进行一下测试
创建一个public帐号:
USE master
CREATE LOGIN USER_READONLY WITH PASSWORD ='Nzperfect'
GO
CREATE LOGIN USER_READONLY WITH PASSWORD ='Nzperfect'
GO
我们用这个USER_READONLY帐号登陆sql server,
然后测试一下,执行下面的T-sql循环脚本
DECLARE @CNT INT
WHILE 1=1
BEGIN
SELECT @CNT=COUNT(*) FROM sys.tables
END
WHILE 1=1
BEGIN
SELECT @CNT=COUNT(*) FROM sys.tables
END
打开性能监视器,并添加资源调器资源池cpu使用计数器,如下多图:
在以USER_READONLY登陆执行T-sql脚本后,pMAX_CPU_PERCENT_10资源池占用cpu为50%如下图:
然后,我们以sa帐号登陆sql server ,同时也执行上面的T-sql循环脚本,再看性能监视器计数,如图:
由上图可以看到,gMAX_CPU_PERCENT_10所占用的cpu立即下降到5%左右,说明我们配置的配置资源调控器已生效。
这时,如果取消sa执行的T-sql循环,则结果如下:
由上面的测试说明,当系统资源够用时,USER_READONLY像正常情况一下,sql server不会限制其使用的cpu资源,但当存在资源竞争时,资源调控器将跟据配置的资源池及组信息自动调节,限制USER_READONLY使用的资源,以确保其它进程拥用更多的资源。
--测试结束,删除测试:
USE master
GO
DROP WORKLOAD GROUP gMAX_CPU_PERCENT_10
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP RESOURCE POOL pMAX_CPU_PERCENT_10
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= null);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP FUNCTION [dbo].[rgclassifier_MAX_CPU]
GO
writed by nzperfect 2009.07.26
作者:nzperfect
出处:http://www.cnblogs.com/nzperfect/
引用或者转载本BLOG的文章请注明原作者和出处,并保留原文章中的版权信息。