Setting Up Resource Governor

-- CREATE RESOURCE POOL FOR SQL AGENT JOBS

CREATE RESOURCE POOL

       SQLAgentJobsPool

WITH

(

       MIN_CPU_PERCENT=0,

       MAX_CPU_PERCENT=85,

       MIN_MEMORY_PERCENT=0,

       MAX_MEMORY_PERCENT=85

)

GO

-- CREATE WORKLOAD GROUP for SQL AGENT JOBS

CREATE WORKLOAD GROUP

       SQLAgentJobsGroup

USING

       SQLAgentJobsPool;

GO

-- CREATE UDF TO ASSIGN WORK LOAD GROUP

CREATE FUNCTION dbo.UDFAssign_RG_WorkLoadGroup()

RETURNS SYSNAME

WITH SCHEMABINDING

AS

BEGIN

DECLARE @WorkloadGroup AS SYSNAME

IF(SUSER_NAME() = 'Put SQLSERVERAgent Service Account Here')

       SET @WorkloadGroup = 'SQLAgentJobsGroup'

ELSE

       SET @WorkloadGroup = 'default'

RETURN @WorkloadGroup

END

GO

-- ALTER RESOURCE GOVERNOR TO USE WORK LOAD GROUP UDF

USE [master]

GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[UDFAssign_RG_WorkLoadGroup]);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO