SQL Server Magazine

Prior to SQL Server 2008, the database engine tried to provide balanced performance to all concurrent users, so you had little control over the priority assigned to users or workloads. In addition, many organizations have hundreds (or even thousands) of applications, and DBAs have little knowledge of which applications are being used to access their databases. The problem is made worse by the use of fixed SQL Server logins for a large number of users of applications.

SQL Server 2008 introduces the Resource Governor, which provides you with a degree of control over your resources. Resource Governor can help prevent performance problems and somewhat identify the applications being used. Although Resource Governor currently has several limitations, it’s sufficient for dealing with many common resource problems. In this article, I’ll show you how to manage your CPU and memory resources by creating resource pools, assigning applications to resource pools, and using classifier functions to assign sessions to workload groups.

Creating Resource Pools
Resource Governor works by controlling the CPU and memory allocated to a session. You can specify the maximum and minimum percentages of CPU and memory that an application can use, as well as the maximum degree of parallelism permitted. To do so, create broad pools of resources to allocate sessions into and set the minimum and maximum percentages on them using the CREATE RESOURCE POOL command. For example, the commands in Listing 1 create three resource pools that are limited based on CPU percentage. The CPU percentages used in these commands aren’t hard limits. For instance, if a single low priority application is the only application running on the system, it will be allocated total use of the CPU. The governing of resources based on the CPU percentage occurs only in times of overlapping concurrent demands.

In Resource Governor there’s a hard-coded maximum limit of 20 pools, 18 of which are available for configuration. Of the remaining two pools, one is allocated to internal processes, such as cleanup operations, and the other is the default pool used for sessions not assigned to other pools. Therefore, you shouldn’t create too many resource pools too early and should carefully consider which pools will be needed.

Creating Workload Groups
Once you’ve grouped the available resources, you can group the applications that will be assigned to these resource pools by creating workload groups using the CREATE WORKLOAD GROUP command. Listing 2 shows some sample CREATE WORKLOAD GROUP commands.

In Listing 2, five workload groups were created and assigned to three resource pools. The Access and Excel applications are kept in separate pools, in case you want to change the resources allocated to one versus the other in the future. Poorly behaving Access applications and applications that we can’t identify will be given few resources. Applications that we have identified as critical to our business will be given the highest priority on resources.

Viewing Your Resource Governor Configuration
Once you’ve created the workload groups and assigned them to resource pools they don’t start working immediately. To determine what else needs to be configured to get Resource Governor up and running you can view the current Resource Governor configuration, which is provided in a new dynamic management view. Executing the command

SELECT * FROM sys.dm_resource_governor_configuration;

produces the output shown in Figure 1.

(Note that this output is shown in the Results tab in SQL Server Management Studio—SSMS.) The value of 1 in the left column is the output row number.

This output tells you two things. First, the is_reconfiguration_pending flag means that you need to execute the command

ALTER RESOURCE GOVERNOR RECONFIGURE;

before your changes will take effect. After you run this command, the is_reconfiguration_pending value is zero. Second, the value of zero for the classifier_function_id means no classifier function has been assigned. (As a side note, I feel this column should return NULL, not zero, for this value.) A classifier function is used to determine which workload group a session should be placed in. I’ll show you how to use classifier functions in a moment.

Next, you can see the resource pool configuration by executing the command

SELECT * FROM sys.dm_resource_governor_resource_pools;

which returns the output shown in Figure 2.

Notice that until you execute the reconfiguration command, only the internal and default pools will appear in this output. You can see the workload pool configuration by using the command

SELECT * FROM sys.dm_resource_governor_workload_groups;

which returns the output shown in Figure 3.

Classifying Applications
The final step to configuring Resource Governor is identifying the sessions that you want to control. You can do so by using a classifier function, which assigns a session to a specific workload group, as Listing 3 shows.

Classifier functions must return a value of type SYSNAME, which is currently an alias for NVARCHAR(128). For the purpose of this article, I have based the decision about which workload group a session should go into on the application name function (APP_NAME()). This function returns the value that was specified in the connection string from the application. I have a particular liking for this option as it lets you make classification decisions based on which application is running. There are several other options you can use when classifying sessions, which I’ll describe later.