What is Resource Governor?
Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.
In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.
Microsoft has provided two options to configure the resource governor. They are as follows:
- T-SQL Commands
- Object Explorer
Both of these options are also available with SQL Server Management Studio.
The Difference between the Work Load Governor and Resource Governor
Now many readers may confuse Resource Governor with the Workload Governor, which was part of some earlier versions of SQL Server. This governor was used to limit the performance, but it was used to limit the performance of the SQL Server instance as a whole. When the number of connections used to be increased by a certain level, then it used to limit the responsiveness of the SQL Server. It was not possible to limit the resources available to any particular application or user. This is available in SQL Server 2008 in the form of Resource Governor, so we should not confuse the two of them.
Terminologies:
The Resource Governor is implemented by defining resource pools, workload groups and Classifier functions. So we must have an understanding of all of three of them. Each is described below.
Resource Pool
It is the collection of physical resources of the database management system. It is viewed as a virtual SQL Server instance within the SQL Server as visible by the application. It is defined with minimum and maximum values of resource (memory and CPU) utilization. SQL Server 2008 allows a maximum of 18 user defined pools and two built-in pools. There are following built-in resource pools in SQL Server 2008:
Internal: Used solely by the database engine. This cannot be re-configured by the user.
Default: Used by all those workloads that have not been assigned to any specific resource pool.
The database administrator may also define their own custom resource pools. For these resource pools, they may define the limit of the usage of each resource by any workload by defining minimum and maximum values of allowed resources for the particular workload. Now what value could be assigned to minimum and maximum values? The minimum values are defined in a way that sum of minimum values of each workload does not exceed 100. The max value of each can vary between the minimum value and 100.
Now it seems impossible that any workload could be assigned resources equal to its maximum value because other resource pools are working with at least their minimum resource pool, so a new term is coined called the Effective Maximum Rate. It is equal to the difference between the maximum value of said resource pool and sum of minimum values of all others.
To understand this, we consider an instance in which there are 3 custom resource pools. Now it must be understood that the internal resource pool is on the highest priority list of the server, so it is said to have Effective Max % equal to its Maximum value (100). It means that it is the task that will always have full resources of the server even if it means it violates the requirements of resources for other pools.
The shared % is the difference between Effective Max % and Minimum % for any resource pool. It is to have some idea if no workload is under process for any pool then how much of the resources can be shared by the said resource pool.
|
Resource Pool
|
Minimum %
|
Maximum %
|
Effective Max %
|
Shared %
|
|
Internal
|
0
|
100
|
100
|
100
|
|
Default
|
0
|
100
|
25
|
25
|
|
PoolA
|
10
|
100
|
35
|
25
|
|
PoolB
|
35
|
90
|
50
|
15
|
|
PoolC
|
30
|
80
|
35
|
5
|
Now we would discuss how to define any resource pool. First we consider defining the resource pool using T-SQL statement. See Code Listing 1.
There are also DROP and ALTER statements available for the resource pool.
Note: It must be remembered to make any changes effective for the SQL Server Resource governor, the Resource Governor must be reconfigured by running the command: ALTER RESOURCE GOVERNOR RECONFIGURE.
You can also define new resource pool using SQL Server management studio. See Figure 1.
Figure 1
To define a new pool Resource Governor must be enabled. When you select 'New Resource Pool', the following form appears: (See Figure 2)
Figure 2
Workload Group
Now you might guess that these resource pools are then assigned to particular users. Though it seems OK for a little number of users, but for an organizational environment this would be very difficult to manage. To make this management easier for database administrator, workload groups are introduced. All workloads are assigned to some specific resource pool. A resource pool may have zero or more workload groups assigned to it.
Like built-in resource pools, there are built-in workload groups as well. They are as follows:
Internal: Assigned to the internal resource pool. Used by database engine.
Default: All those sessions that do not classify to any other workload group, are assigned to it.
CREATE WORKLOAD GROUP Workload_Name
USING ResourcePool_Assigned
ALTER and DROP statements are also available for workload group.
Classifier function
Now you may ask how each user session would be assigned to any particular workload group. Well the answer is the Classifier function. It is a normal user defined function (UDF) defined in SQL Server (scalar valued). Any property may be used to do this job like IP Address, Application Name and Username etc. See Code Listing 2.
Assigning Classifier function to Resource Governor:
How would Resource Governor know which classifier function to use. For that a classifier function has to be assigned to the resource governor.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.My_Classifer_func)
Enabling Resource Governor:
There are two states of Resource Governor. They are as follows:
1. Enabled
2. Disabled
For performing its operation a Resource Governor must be enabled. You can execute following statement to enable resource governor:
ALTER RESOURCE GOVERNOR RECONFIGURE
It can also be done through SQL Server management studio by the popup menu shown when resource governor is right clicked in Object Browser. You can have a look at the following to understand this: (See Figure 3)
Figure 3
Alternatively, it can also be enabled when a new Resource pool, Workload etc is defined. See Figure 4.
Disabling Resource Governor
If you think it is some feature that you don't need in your organization, then you may disable this feature altogether. After disabling this, all the resources are assigned by the server in nearly the same fashion as SQL Server 2005.
ALTER RESOURCE GOVERNOR DISABLE
The same result can also be achieved by taking the properties of Resource Manager in SQL Server Management Studio 2008. Just un-check the 'Enable Resource Governor' check box (See Figure 4).
Figure 4
After disabling this, all new session requests are assigned to the default workload group. All the sessions which are already assigned to some group remain assigned to that until they are disconnected.
How this all works?
Whenever there is a request for a connection, the request is classified through classifier function and a workload group is identified that this session request should be handled with. Now a workload group may be assigned to only a single resource pool. This resource pool is used to assign and limit the resources required by the session.
Performance Monitoring of Resource Governor:
You can check the performance through Reliability and Performance Monitor. There are following performance counters available:
1. SQLServer: Resource Pool Stats
2. SQLServer: Workload Stats. See Figure 5.
Figure 5
Catalog Views
There are following catalog views introduced for Resource governor.
1. sys.resource_governor_configuration
2. sys.resource_governor_resource_pools
3. sys.resource_governor_workload_groups
Dynamic Management Views (DMVs) for Resource Governor
There are following DMVs introduced to support resource governor.
1. sys.dm_resource_governor_workload_groups
2. sys.dm_resource_governor_configuration
3. sys.dm_resource_governor_resource_pools
Limitations:
There are also some limitations to the Resource Governor. They are as follows:
1. It is only limited to the Database engine. But there are some services which are out of the database engine like Analysis, Integration and Reporting services. For these, we still have to rely on other solutions to manage the resource problem i.e. installing separate SQL Server instances.
2. Only a single instance can be managed through this. An organization may have more than a single instance, but must manage each separately.
3. Limited to only two resources i.e. CPU bandwidth and memory management.
Code Listing 1
CREATE RESOURCE POOL MyPool
WITH
(
MIN_CPU_PERCENT=25,
MAX_CPU_PERCENT=75,
MIN_MEMORY_PERCENT=25,
MAX_MEMORY_PERCENT=50
)
Code Listing 2
CREATE FUNCTION My_Classifer_func()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
--workload definition based on login names
DECLARE @WorkLoadName sysname
IF SUSER_SNAME()= 'admin'
BEGIN
SET @WorkLoadName='AdminUsers';
END
--workload definition based on application
IF APP_NAME() = 'My Application'
BEGIN
SET @WorkLoadName='MyApp';
END
RETURN @WorkLoadName;
END
Note: Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.