The Resource Governor in SQL Server 2008 allows workloads to be differentiated and prioritized. This can be incredibly useful when you need to guarantee that certain critical processes have enough resources to operate efficiently, while still sharing resources with other less important jobs. It can also be used to insulate key processes from runaway queries and to prioritize queries. Prior to the Resource Governor,you could use multiple instances and a careful array of configuration values (such as affinity masks) to divide up resources. This solution was less than ideal because, in most cases, the resources used by one instance were reserved and could not be shared with other instances. The Resource Governor solves this problem by sharing resources when they are available and only kicking in to enforce limits when there is resource contention.
At this time, the Resource Governor is only available in the Enterprise, Developer, and Evaluation versions of SQL Server 2008.
The Resource Governor is comprised of three key components: a classifier function that differentiates workloads, resource pools that describe limits for shared server resources, and workload groups that are used to enforce policies for similar workloads. Figure shows how these components work together.
Configuring the Resource Governor The Resource Governor must be configured before you can use it in a meaningful way. Configuration consists of the following four steps:
1.Configure resource pools.
2.Configure workload groups.
3.Create a classifier function.
4.Assign a classifier function and reconfigure.
1 Configure Resource Pools
Resource pools are used to define logical boundaries for physical resources. It is helpful to think of a resource pool as a kind of virtual server instance. Each pool allows you to specify minimum and maxi-mum utilization percentages for CPU and memory usage. The minimum values represent the minimum guaranteed resource availability, and the maximum values represent the shared resource limit.
It is normal to see resource usage for a particular pool exceed the defined maximum value. If a resource is not required by another pool, then whatever is available to SQL Server as a whole is free to be used. The maximums are only enforced when the server has to divvy resources between competing pools.Two resource pools are automatically created by SQL Server — the internal pool and the default pool.
The internal pool is used by SQL Server itself for critical system tasks and cannot be altered in any way. It is an unrestricted pool and will consume resources as necessary, even if it means violating the limits established for the other pools. The default pool is used for everything else that has not been explicitly placed into a custom pool. The default pool cannot be created or deleted, but it can be re-configured to limit resource use.
Additional resource pools can be created as needed, but the total of all minimum values for a resource cannot exceed 100 percent, and the total of all maximum values for a resource must be between the total minimum and 100 percent. Resource pools can be created, altered, or dropped from SSMS, or by using DDL statements. For example, the following command will create a new resource pool that is limited to 50 percent of the total system memory and 25 percent of the total CPU resources:
CREATE RESOURCE POOL poolFinance
WITH
(
MAX_CPU_PERCENT=25,
MAX_MEMORY_PERCENT=50
);
2 Configure Workload Groups
Workload groups are used to define policies that apply to similar requests as determined by the classifier function. There are two built-in workload groups the internal group and the default group which are assigned to the internal pool and the default pool, respectively. The internal group is used by SQL Server itself for critical tasks and cannot be changed. The default group is used for everything else that has not been explicitly assigned to a user-defined group. The default group cannot be moved or deleted; however, you can configure it. Additional user-defined workload groups can be created as needed and assigned to either the default pool or to a user-defined pool. If necessary, user-defined workload groups can even be moved between pools.
The following policies (parameters) are available when creating a workload group:
a) IMPORTANCE — This indicates the relative importance of tasks within a given resource pool. Validvalues are HIGH, MEDIUM, and LOW. The default value is MEDIUM.
b)REQUEST_MAX_MEMORY_GRANT_PERCENT — This is the maximum memory that a single request can use from the pool. If insufficient memory is available, then the request will be held until a memory grant becomes available or the request times out. The default value is 25 percent.
c) REQUEST_MAX_CPU_TIME_SEC — This setting specifies the maximum CPU time that a request can use. Exceeding this value will not stop a request from processing; instead, a CPU Threshold Exceeded event will be raised, and the request will continue. The default is 0, which means unlimited.
d)REQUEST_MEMORY_GRANT_TIMEOUT_SEC — This parameter sets the number of seconds that arequest will wait for a memory grant before failing. The default is 0, which uses an internal calculation to determine the value based on the query cost.
e)MAX_DOP — This sets the maximum degree of parallelism for requests. This setting overrides the server ‘‘max degree of parallelism’’ setting and sets an upper limit on the MAX_DOP query hint. The default is 0, which uses the system default setting.
f)GROUP_MAX_REQUESTS — Sets the maximum number of simultaneous requests for the group. The default is 0, which means unlimited.
The following example will create a workload group and assign it to resource pool pool Finance:
CREATE WORKLOAD GROUP wrkgroupFinance
WITH
(
IMPORTANCE = MEDIUM,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MAX_CPU_TIME_SEC = 0,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
MAX_DOP = 4,
GROUP_MAX_REQUESTS = 25
)
USING poolFinance;
3)Create a Classifier Function:-
The classifier function is used by the Resource Governor to decide which workload group to use for an incoming session. When the Resource Governor is enabled, this function will be executed after authentication and any logon triggers. It must return the name of the workload group to assign the incoming
session to. If the classifier function fails for any reason or returns an invalid group name, then the session will be assigned to the ‘‘default group.
Make sure to test your classifier function before putting it into production. A poorly written classifier function can render the system unusable by causing all new sessions to time out. In addition, make sure to enable the Dedicated Administrator Connection. The DAC bypasses the classifier function and can be
used to gain access to the server if there is a problem.
Classifier functions are subject to a few special conditions:
a)They should always be created with server scope, meaning they should reside in the master database.
b)Only one classifier function can be active at any point in time, and the active function cannot be dropped from the database.
c) The classifier function must finish quickly to avoid causing connection time-outs.
The following table includes several functions that are useful in classifying workload groups. Included are a few functions that are new to SQL Server 2008 and are intended specifically for this task:
Function Description
HOST_NAME Returns the name of the workstation.APP_NAME Returns the name of the application; however, not every application sets this value.
SUSER_NAME Returns the login name of the user in the syslogins table.
SUSER_SNAME Returns the login name of the user based on their security
identifier.
IS_SRVROLEMEMBER Determines if the login is a member of a fixed server role.
IS_MEMBER Determines if the login is a member of a Windows group or
database role.
LOGONPROPERTY Returns information about the login, including the default
database.
CONNECTIONPROPERTY Returns information about the connection that the request originated from, including the IP address and authentication mode.
ORIGINAL_DB_NAME Returns the name of the database that was specified in the connection string.
The following example demonstrates how to create a classifier function:
USE master
GO
CREATE FUNCTION fnTestClassifier()RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME
IF (SUSER_SNAME() = ‘sa’)
SET @grpName = ‘wrkgroupAdmin’
ELSE IF (APP_NAME() like ‘%Logistics%’)
SET @grpName = ‘wrkgroupLogDep’
ELSE IF (APP_NAME() like ‘%REPORT SERVER%’)
SET @grpName = ‘wrkgroupReports’
ELSE
SET @grpName = ‘default’
RETURN @grpName
END;
4 )Assign the Classifier Function and Reconfigure
The final step in configuring the Resource Governor is to assign the classifier function. Before completing the configuration, make sure that you have thoroughly tested the function, and also make sure to enable the Dedicated Administrator Connection just in case something goes wrong.
The following example shows how to complete the configuration:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnTestClassifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGUREThe new configuration should take effect immediately.
At this time, the Resource Governor is only available in the Enterprise, Developer, and Evaluation versions of SQL Server 2008.
The Resource Governor is comprised of three key components: a classifier function that differentiates workloads, resource pools that describe limits for shared server resources, and workload groups that are used to enforce policies for similar workloads. Figure shows how these components work together.
Configuring the Resource Governor The Resource Governor must be configured before you can use it in a meaningful way. Configuration consists of the following four steps:
1.Configure resource pools.
2.Configure workload groups.
3.Create a classifier function.
4.Assign a classifier function and reconfigure.
1 Configure Resource Pools
Resource pools are used to define logical boundaries for physical resources. It is helpful to think of a resource pool as a kind of virtual server instance. Each pool allows you to specify minimum and maxi-mum utilization percentages for CPU and memory usage. The minimum values represent the minimum guaranteed resource availability, and the maximum values represent the shared resource limit.
It is normal to see resource usage for a particular pool exceed the defined maximum value. If a resource is not required by another pool, then whatever is available to SQL Server as a whole is free to be used. The maximums are only enforced when the server has to divvy resources between competing pools.Two resource pools are automatically created by SQL Server — the internal pool and the default pool.
The internal pool is used by SQL Server itself for critical system tasks and cannot be altered in any way. It is an unrestricted pool and will consume resources as necessary, even if it means violating the limits established for the other pools. The default pool is used for everything else that has not been explicitly placed into a custom pool. The default pool cannot be created or deleted, but it can be re-configured to limit resource use.
Additional resource pools can be created as needed, but the total of all minimum values for a resource cannot exceed 100 percent, and the total of all maximum values for a resource must be between the total minimum and 100 percent. Resource pools can be created, altered, or dropped from SSMS, or by using DDL statements. For example, the following command will create a new resource pool that is limited to 50 percent of the total system memory and 25 percent of the total CPU resources:
CREATE RESOURCE POOL poolFinance
WITH
(
MAX_CPU_PERCENT=25,
MAX_MEMORY_PERCENT=50
);
2 Configure Workload Groups
Workload groups are used to define policies that apply to similar requests as determined by the classifier function. There are two built-in workload groups the internal group and the default group which are assigned to the internal pool and the default pool, respectively. The internal group is used by SQL Server itself for critical tasks and cannot be changed. The default group is used for everything else that has not been explicitly assigned to a user-defined group. The default group cannot be moved or deleted; however, you can configure it. Additional user-defined workload groups can be created as needed and assigned to either the default pool or to a user-defined pool. If necessary, user-defined workload groups can even be moved between pools.
The following policies (parameters) are available when creating a workload group:
a) IMPORTANCE — This indicates the relative importance of tasks within a given resource pool. Validvalues are HIGH, MEDIUM, and LOW. The default value is MEDIUM.
b)REQUEST_MAX_MEMORY_GRANT_PERCENT — This is the maximum memory that a single request can use from the pool. If insufficient memory is available, then the request will be held until a memory grant becomes available or the request times out. The default value is 25 percent.
c) REQUEST_MAX_CPU_TIME_SEC — This setting specifies the maximum CPU time that a request can use. Exceeding this value will not stop a request from processing; instead, a CPU Threshold Exceeded event will be raised, and the request will continue. The default is 0, which means unlimited.
d)REQUEST_MEMORY_GRANT_TIMEOUT_SEC — This parameter sets the number of seconds that arequest will wait for a memory grant before failing. The default is 0, which uses an internal calculation to determine the value based on the query cost.
e)MAX_DOP — This sets the maximum degree of parallelism for requests. This setting overrides the server ‘‘max degree of parallelism’’ setting and sets an upper limit on the MAX_DOP query hint. The default is 0, which uses the system default setting.
f)GROUP_MAX_REQUESTS — Sets the maximum number of simultaneous requests for the group. The default is 0, which means unlimited.
The following example will create a workload group and assign it to resource pool pool Finance:
CREATE WORKLOAD GROUP wrkgroupFinance
WITH
(
IMPORTANCE = MEDIUM,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 50,
REQUEST_MAX_CPU_TIME_SEC = 0,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
MAX_DOP = 4,
GROUP_MAX_REQUESTS = 25
)
USING poolFinance;
3)Create a Classifier Function:-
The classifier function is used by the Resource Governor to decide which workload group to use for an incoming session. When the Resource Governor is enabled, this function will be executed after authentication and any logon triggers. It must return the name of the workload group to assign the incoming
session to. If the classifier function fails for any reason or returns an invalid group name, then the session will be assigned to the ‘‘default group.
Make sure to test your classifier function before putting it into production. A poorly written classifier function can render the system unusable by causing all new sessions to time out. In addition, make sure to enable the Dedicated Administrator Connection. The DAC bypasses the classifier function and can be
used to gain access to the server if there is a problem.
Classifier functions are subject to a few special conditions:
a)They should always be created with server scope, meaning they should reside in the master database.
b)Only one classifier function can be active at any point in time, and the active function cannot be dropped from the database.
c) The classifier function must finish quickly to avoid causing connection time-outs.
The following table includes several functions that are useful in classifying workload groups. Included are a few functions that are new to SQL Server 2008 and are intended specifically for this task:
Function Description
HOST_NAME Returns the name of the workstation.APP_NAME Returns the name of the application; however, not every application sets this value.
SUSER_NAME Returns the login name of the user in the syslogins table.
SUSER_SNAME Returns the login name of the user based on their security
identifier.
IS_SRVROLEMEMBER Determines if the login is a member of a fixed server role.
IS_MEMBER Determines if the login is a member of a Windows group or
database role.
LOGONPROPERTY Returns information about the login, including the default
database.
CONNECTIONPROPERTY Returns information about the connection that the request originated from, including the IP address and authentication mode.
ORIGINAL_DB_NAME Returns the name of the database that was specified in the connection string.
The following example demonstrates how to create a classifier function:
USE master
GO
CREATE FUNCTION fnTestClassifier()RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME
IF (SUSER_SNAME() = ‘sa’)
SET @grpName = ‘wrkgroupAdmin’
ELSE IF (APP_NAME() like ‘%Logistics%’)
SET @grpName = ‘wrkgroupLogDep’
ELSE IF (APP_NAME() like ‘%REPORT SERVER%’)
SET @grpName = ‘wrkgroupReports’
ELSE
SET @grpName = ‘default’
RETURN @grpName
END;
4 )Assign the Classifier Function and Reconfigure
The final step in configuring the Resource Governor is to assign the classifier function. Before completing the configuration, make sure that you have thoroughly tested the function, and also make sure to enable the Dedicated Administrator Connection just in case something goes wrong.
The following example shows how to complete the configuration:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fnTestClassifier)
GO
ALTER RESOURCE GOVERNOR RECONFIGUREThe new configuration should take effect immediately.
