Resource Governor in SQL Server

SQL Server 2008 has a new feature called Resource Governor. You can limit how much CPU bandwidth or memory certain processes will consume.

Configuration comprises the following basic steps:

1. Create and configure a Resource Governor resource pool that limits either the maximum average CPU bandwidth that will be given to requests in the resource pool when CPU contention occurs or the maximum memory
  1. CREATE RESOURCE POOL pool_name [ WITH ( [ MIN_CPU_PERCENT = value ] [ [ , ] MAX_CPU_PERCENT = value ] [ [ , ] MIN_MEMORY_PERCENT = value ] [ [ , ] MAX_MEMORY_PERCENT = value ] ) ] [;]
2. Create and configure a Resource Governor workload group that uses this pool.

- 1. CREATE WORKLOAD GROUP group_name [ WITH ( [ IMPORTANCE = { LOW | MEDIUM | HIGH } ] [ [ , ] REQUEST_MAX_MEMORY_GRANT_PERCENT = value ] [ [ , ] REQUEST_MAX_CPU_TIME_SEC = value ] [ [ , ] REQUEST_MEMORY_GRANT_TIMEOUT_SEC = value ] [ [ , ] MAX_DOP = value ] [ [ , ] GROUP_MAX_REQUESTS = value ] ) ] [ USING { pool_name | "default" } ] [ ; ]

1. Create a classifier function, which is a user-defined function (UDF) whose return values are used by Resource Governor for classifying sessions so that they are routed to the appropriate workload group.

- 1. Here's an example where activity in SQL Server is classified based on a mix of who the user is and also what the application is:
  2. CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname WITH SCHEMABINDING AS BEGIN DECLARE @grp_name sysname IF (SUSER_NAME() = 'sa') SET @grp_name = 'GroupAdmin' IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%') OR (APP_NAME() LIKE '%QUERY ANALYZER%') SET @grp_name = 'GroupAdhoc' IF (APP_NAME() LIKE '%REPORT SERVER%') SET @grp_name = 'GroupReports' RETURN @grp_name END;

1. Register the classifier function with Resource Governor. Apply the changes to the Resource Governor in-memory configuration.

- 1. ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier) ALTER RESOURCE GOVERNOR RECONFIGURE GO