RESOURCE GOVERNOR를 이용해 계정별 Resource 통제

Resource Governor를 사용하여, Resource를 계정 및 application에 따라 사용 한계를 정하고 싶을때 사용

 

MS SQL 2008에서부터 제공되는 Resource Governor를 통해 사용되는 Resource를 통제

RESOURCE GOVERNOR 관련하여, POOL / GROUP / 함수를 생성합니다.

ALTER RESOURCE POOL DBADMIN_POOL
WITH (MAX_CPU_PERCENT = 90);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DROP RESOURCE POOL DBADMIN_POOL
DROP RESOURCE POOL ETC_POOL

ALTER RESOURCE POOL ETC_POOL
WITH (MAX_CPU_PERCENT = 10);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DROP WORKLOAD GROUP DBADMIN_GROUP
CREATE WORKLOAD GROUP DBADMIN_GROUP

USING DBADMIN_POOL;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
DROP WORKLOAD GROUP ETC_GROUP
CREATE WORKLOAD GROUP ETC_GROUP
USING ETC_POOL;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DROP FUNCTION dbo.rgclassifier_v1

ALTER FUNCTION dbo.rgclassifier_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name AS sysname
    IF (SUSER_NAME() = 'HOME\kim_heeseak')
        SET @grp_name = 'DBADMIN_GROUP'
	ELSE
		SET @grp_name ='ETC_GROUP'
    RETURN @grp_name
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
 
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

ALTER RESOURCE GOVERNOR RESET STATISTICS;
 
ALTER RESOURCE GOVERNOR DISABLE

JOB START , JOB DELETE, JOB CREATE 구문

DECLARE @START INT
DECLARE @END INT

SET @START=0
SET @END=30

WHILE(@START<@END)
BEGIN
	DECLARE @JOBNAME NVARCHAR(100)
	SET @JOBNAME=N'CPU_JOB_'+CONVERT(NVARCHAR,@START)
	--EXEC CREATE_JOB @JOBNAME
	--exec msdb.dbo.sp_delete_job @job_name =@JOBNAME
	exec msdb.dbo.sp_start_job @JOBNAME

	SET @START=@START+1
END

CPU에 부하를 주는 프로시저

CREATE PROC [dbo].[CPU_TEST_PROC]
AS
DECLARE @CURRENT BIGINT
DECLARE @END BIGINT
DECLARE @SUM BIGINT

SET @CURRENT=0
SET @END=10000000
SET @SUM=0

WHILE (@CURRENT<@END)
BEGIN
	SET @SUM=@SUM+@CURRENT
	SET @CURRENT=@CURRENT+1
END

PRINT @SUM
GO

작업 생성하는 프로시저

CREATE PROC [dbo].[CREATE_JOB] @JOBNAME NVARCHAR(20)
AS
BEGIN
	DECLARE @jobId BINARY(16)
	EXEC  msdb.dbo.sp_add_job @job_name=@JOBNAME, 
			@enabled=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=2, 
			@notify_level_netsend=2, 
			@notify_level_page=2, 
			@delete_level=0, 
			@category_name=N'[Uncategorized (Local)]', 
			@owner_login_name=N'HOME\kim_heeseak', 
			@job_id = @jobId OUTPUT

	select @jobId

	EXEC msdb.dbo.sp_add_jobserver @job_name=@JOBNAME, 
		@server_name = N'KRSADBADMIN'

	EXEC msdb.dbo.sp_add_jobstep @job_name=@JOBNAME, 
			@step_name=N'CPU_TEST', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_fail_action=2, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, @subsystem=N'TSQL', 
			@command=N'exec CPU_TEST_PROC', 
			@database_name=N'master', 
			@flags=0


	EXEC msdb.dbo.sp_update_job @job_name=@JOBNAME, 
			@enabled=1, 
			@start_step_id=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=2, 
			@notify_level_netsend=2, 
			@notify_level_page=2, 
			@delete_level=0, 
			@description=N'', 
			@category_name=N'[Uncategorized (Local)]', 
			@owner_login_name=N'HOME\kim_heeseak', 
			@notify_email_operator_name=N'', 
			@notify_netsend_operator_name=N'', 
			@notify_page_operator_name=N''
END