Handling workloads on SQL Server 2008 with Resource Governor

Written By: Arshad Ali -- 4/1/2009 -- 2 comments

     Stay informed - get the MSSQLTips.com newsletter and win - click here    

Problem
Handling workloads has been quite difficult until SQL Server 2005. For example consider a scenario where one SQL instance is serving two applications i.e. an OLTP application and a reporting/data warehousing application. Since reporting applications are normally resource intensive, it may consume all the SQL Server available resources and may hamper the OLTP application which ideally should have more preference over the reporting application.

To handle this scenario, in earlier version of SQL Server there was one option to create multiple instances for these application (segregating by running one application on each instance) and setting CPU affinity mask for these instances appropriately. But the problems with this approach are, first it works for CPU only and second the dedicated CPUs cannot be shared by other SQL Server instances. For example, if there are two SQL Server instances and instance one has been assigned CPU 1 and 2 and instance two has been assigned CPU 3 and 4 on a four processor machine, even if instance one is idle and instance two is in need of additional resources, it can only use CPU 3 and 4.  So what does SQL 2008 offer to solve this issue?

Solution
SQL Server 2005 resource allocation policies treat all workloads equally, and allocate shared resources as they are requested. It sometimes causes a disproportionate distribution of resources, which in turn results in uneven performance or unexpected slowdowns whereas the new Resource Governor of SQL Server 2008 allows organizations to define resource limits and priorities for different workloads, which enables concurrent workloads to provide consistent performance to the end users.

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests. In an environment where multiple distinct workloads are present on the same server, Resource Governor enables us 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. In other words, Resource Governor enables you to assign a relative importance to workloads. In other words, one workload can be allowed to proceed faster than another or is guaranteed to complete if there is resource contention. It allows a DBA or ITPros to define resource limits and priorities for different workloads.

Resource Governor Components

There are three new components of Resource Governor which are important to understand : resource pools, workload groups and classification (or classifier user-defined functions).

• Pool: A resource pool, or pool, is a collection of system resources such as memory or CPU; it represents a portion of the physical resources of the server. Depending on its settings, a pool may have a fixed size (its minimum and maximum resource usage settings are equal to each other) or have a part which is shared between multiple pools (its minimum is less than its effective maximum). “Shared” in this case simply means that resources go to the pool that requests the resources first. In the default configuration all resources are shared, thus maintaining backward compatibility with SQL Server 2005 policies. Two resource pools (internal and default) are created when SQL Server 2008 is installed. Resource Governor also supports 18 user-defined resource pools. You specify MIN and MAX values for resources (CPU or Memory) which represents the minimum guaranteed resource availability of the pool and the maximum size of the pool, respectively. The sum of MIN values across all pools cannot exceed 100 percent of the server resources. MAX value can be set anywhere in the range between MIN and 100 percent inclusive. The internal pool represents the resources consumed by the SQL Server itself. This pool always contains only the internal group, and the pool is not alterable in any way. Resource consumption by the internal pool is not restricted. Any workloads in the pool are considered critical for server function, and Resource Governor allows the internal pool to pressure other pools even if it means the violation of limits set for the other pools. The default pool is the first predefined user pool. Prior to any configuration the default pool only contains the default group. The default pool cannot be created or dropped but it can be altered. The default pool can contain user-defined groups in addition to the default group.

• Group: A workload group, or group, is a user-specified category of requests that are similar according to the classification rules that are applied to each session request. A group defines the policies for its members. A resource pool is assigned to a Workload Group, which is in turn is assigned to the Resource Governor. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2008 is installed, apart from that the Resource Governor also supports user-defined workload groups. The internal workload group is populated with requests that are for internal SQL Server use only. You cannot change the criteria used for routing these requests and you cannot classify requests into the internal workload group whereas requests are mapped to default workload group, if there is a classification failure, an attempt to map to a non-existent workload group and there is no criteria to classify. If the Resource Governor is disabled, all new connections are automatically classified into the default group and System-initiated requests are classified into the internal workload group.

• Classification: Classification is a set of user-written rules that enable Resource Governor to classify session requests into the workload groups as described previously; for example classifying on the basis of user, application etc. It is implemented through a scalar Transact-SQL user-defined function (UDF) which is designated as a “classifier UDF” for the Resource Governor in the master database. Only one user-defined function can be designated as a classifier at a time.

Putting it all together

The incoming connection request for a session is classified by a classifier UDF and is routed to an appropriate workload group. This workload group in turn uses the resource pool associated with it and finally the resource pool provides and limits on the resources required by the session. Let's see this with an example, I will consider the same problem discussed in the problem section where we have one SQL Server instance serving an OLTP application and a reporting application, though it can be used in variety of different circumstances where you have to manage workloads:

• First I will create two resource pools to be used by OLTP and Reporting application, then I will create two workload groups which will categorize the request coming from these applications.

Working with Resource Governor in SQL Server 2008 - Part 1

--Resource pool to be used by OLTP ApplicationCREATE RESOURCE POOL OLTPPoolWITH( MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100, MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100)GO--Resource pool to be used by Report ApplicationCREATE RESOURCE POOL ReportPoolWITH( MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100, MIN_MEMORY_PERCENT=50, MAX_MEMORY_PERCENT=100)GO--Workload Group to be used by OLTP ApplicationCREATE WORKLOAD GROUP OLTPGroup    USING OLTPPool ;GO--Workload Group to be used by Report ApplicationCREATE WORKLOAD GROUP ReportGroup    USING ReportPool ;GO    

• Next I will create the classifier UDF to route incoming request to different workload groups and finally I will enable Resource Governor with ALTER RESOURCE GOVERNOR RECONFIGURE statement. Assumption here is, the OLTP application uses "OLTPUser" login whereas reporting application uses "ReportUser" login.

Working with Resource Governor in SQL Server 2008 - Part 2

USE master;GOCREATE FUNCTION dbo.ResourceClassifier() RETURNS SYSNAME WITH SCHEMABINDINGASBEGIN --Declare the variable to hold the value returned in sysname. DECLARE @WorkloadGroup AS SYSNAME --If the user login is 'OLTPUser', map the connection to the  --OLTPGroup workload group.  IF (SUSER_NAME() = 'OLTPUser')  SET @WorkloadGroup = 'OLTPGroup' --If the user login is 'ReportUser', map the connection to  --the ReportGroup workload group.  ELSE IF (SUSER_NAME() = 'ReportUser')  SET @WorkloadGroup = 'ReportGroup' ELSE   SET @WorkloadGroup = 'default' RETURN @WorkloadGroupENDGO--Register the classifier user-defined function and update the --the in-memory configuration.ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.ResourceClassifier);GO--Enabling Resource Governor(By default when you install --SQL Server, Resource Governor is disabled)--It loads the stored configuration metadata into memory ALTER RESOURCE GOVERNOR RECONFIGUREGO--Disabling Resource GovernorALTER RESOURCE GOVERNOR DISABLEGO--It resets statistics on all workload groups and resource pools. ALTER RESOURCE GOVERNOR RESET STATISTICSGO

• Resource Governor can also be managed using SQL Server Management Studio (SSMS), you can CREATE, ALTER, DROP resource pools, workload groups, change classifier UDF and ENABLE/DISABLE Resource Governor as shown below.

Resource Governor’s Catalog Views and Dynamic Management Views

There are three new Catalog Views and three new Dynamic Management Views introduced for Resource Governor.

• sys.resource_governor_configuration - used to display the Resource Governor configuration as stored in metadata.

• sys.resource_governor_resource_pools - used to display resource pool configuration as stored in metadata.

• sys.resource_governor_workload_groups - used to display workload group configuration as stored in metadata.

• sys.dm_resource_governor_configuration - used to get the current in-memory configuration state of Resource Governor

• sys.dm_resource_governor_resource_pools - used to get the current resource pool state, the current configuration of resource pools, and resource pool statistics.

• sys.dm_resource_governor_workload_groups - used to get the workload group statistics and the current in-memory configuration of the workload group.

In addition to new views that are specific to Resource Governor, existing system views have been modified to include information about Resource Governor as well.

Note

• Only one resource pool can be assigned to a workload, though a single resource pool can serve multiple workload groups. If there are multiple workload groups in a given resource pool, you can set relative importance of each workload group to either LOW, MEDIUM or HIGH.

• The resource governor limits can easily be reconfigured in real time with minimal impact on the workloads that are executing; for that purpose you use ALTER RESOURCE GOVERNOR statement with the RECONFIGURE parameter.

• To monitor utilization of Resource governor you can monitor different performance counters under SQLServer:Resource Pool Stats and SQLServer:Workload Group Stats performance counter categories.

Limitations

There are also some limitations to the Resource Governor. They are as follows:

• Resource management is limited to the SQL Server Database Engine. Resource Governor cannot be used for Analysis Services, Integration Services, and Reporting Services.

• Only a single instance can be managed through this. An organization may have more than a single instance, but must manage each separately.

• Limited to only two resources i.e. CPU bandwidth and memory management.

• You are allowed to create only 18 resource pools apart from Default and Internal pool. Creating more resource pool than this throws an error, “The resource pool cannot be created. The maximum number of resource pools cannot exceed current limit of 20 including predefined resource pools.” Though in one sense it is good to have fewer resource pools and assign multiple workloads to it if required.

• Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

Next Steps

• Review “Managing SQL Server Workloads with Resource Governor” on MSDN.