Configuring SQL Server 2008’s Resource Governor

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.

Applications are often built without the application name being specified in the connection string, but it’s important to add the application name to the connection string or to specify the value in the application configuration files. This practice is also useful outside of Resource Governor because the application name appears in traces created by SQL Server Profiler and can be used to filter events within the traces. Having access to application names via the connection strings also assists with tracing access to SQL Server when trying to isolate an application-related problem. However, some less well designed applications have hard-coded connection strings that don’t lend themselves to this approach. Hard-coding connection strings is a practice that should be avoided.

Also, I have found classifying applications by name to be a good starting point for identifying unknown applications that are connected to a server. The beauty of Resource Governor in this situation is that the unknown applications would be placed in a low priority resource pool, and it’s likely that the users involved would then quickly identify the applications they’re using. This capability lets you determine who is actually using your systems.

Although the APP_NAME() function was used in Listing 3, many other functions can be used to classify sessions, such as HOST_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), and IS_MEMBER(). If you decide to use either HOST_NAME() or APP_NAME() as the classifier function, be aware that it’s possible for these functions to be altered by users. In practice, however, I have found APP_NAME() to work very well.

In addition to these functions, you can use several properties when making classification decisions. The LOGINPROPERTY() function now includes two properties (DefaultDatabase and DefaultLanguage) that can be used in classification functions. In addition, the CONNECTIONPROPERTY() function provides access to the network transport and protocol being used for the connection, as well as details of the authentication scheme, the local IP address and TCP port, and the client’s IP address. The ORIGINAL_DB_NAME() function is also useful because it returns the name of the database that was provided when the session first connected, as opposed to the default database. These properties let you to make classification decisions based on any of these values. For example, you could assign a connection to a workload group based on which database the user typically uses.

It’s important to test the classifier function before putting it into use; otherwise your system can become unresponsive. For example, you can test the APP_NAME() classifier function in SSMS by executing the command

SELECT APP_NAME();

which returns the output shown in Figure 4.

Because SSMS wasn’t on the list of applications that the classifier function knew about, you shouldn’t be surprised that executing the command

SELECT dbo.UserClassifier();

returns the output shown in Figure 5.

If you enabled the classifier function without further modification, SSMS would run in the low priority application resource pool. You can also use a classifier function to look up the workload group name in a database table, instead of hard-coding all application names into the function. Typically, performance won’t be greatly affected when accessing a table to look up the workload group because the table wouldn’t be large, would quickly become cached, and the classification decisions that the function is used to make are taken only when a connection is first established.

You can now implement the classifier function using the following command:

ALTER RESOURCE GOVERNOR  WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);

Then you must reconfigure Resource Governor to put the pending configuration into operation. You can do so using the command

ALTER RESOURCE GOVERNOR RECONFIGURE;

At this point, your Resource Governor configuration is complete. You then need to make sure it’s working as it should. If you did a poor job of testing your classifier function, the system could become unresponsive to new logins and you might need to use the dedicated administrator connection (DAC) to gain access to SQL Server. If you haven’t configured the DAC, you can restart the server in single-user mode. When running in single-user mode, your connection isn’t subject to Resource Governor, so you can disable Resource Governor until the situation is corrected.

Should you need to change the classifier function, you’ll need to first disable Resource Governor because you can’t change it while Resource Governor is in use. You can disable the current classifier function by executing the following command:

ALTER RESOURCE GOVERNOR  WITH (CLASSIFIER_FUNCTION = NULL);

As a general rule, Resource Governor changes should be implemented by the most senior DBA within your organization, given the problems that can arise if Resource Governor is misconfigured. That’s also the reason I’ve used T-SQL scripts for implementation in this article even though you can perform these operations directly in SSMS. The CONTROL SERVER permission is required to change the configurations, and the VIEW SERVER STATE permission is required to view the configuration. Classifier function failures can be detected via events in the application log on the server.

Other Techniques for Monitoring and Configuring Resources

You can also monitor the CPU resources being allocated by SQL Server via the System Monitor (perfmon.exe) tool. Note that when you add counters that you want to monitor, the SQLServer:Resource Pool Stats object shows instances of each of the configured resource pools, as shown in Figure 6.

Similar instance-related selections have been added to SQLServer:Workload Group Stats counter. Relevant values can also be obtained by querying the sys.dm_os_performance_counters view.

To enable more detailed monitoring, additional events have been added to SQL Server, including the CPU Threshold Exceeded, PreConnect:Starting, and PreConnect:Completed events. If you want to control Resource Governor programmatically using .NET code, a ResourceGovernor class has been added to SQL Server Management Objects (SMO).

For the purposes of this article, I focused on maximum CPU percentages when configuring resource pools because you must be very careful about how you configure minimum percentages. It’s easy to greatly limit the resources on a system by configuring the minimum values too high, particularly minimum percentages for memory.

Caveats

You might be wondering about the limitations I mentioned earlier. I can hear people saying "OK Greg, this feature looks great. What’s the catch?" First, resource pools and workload groups can’t be dropped while they’re in use. No big surprise there, except they could be in use for quite a while on busy systems. Also, you might think that you can use Resource Governor to drop the priority of that pesky query that you’ve just noticed is currently killing system performance. However, you can’t use Resource Governor to do that because the workload groups and resource pools were allocated when the session connected. In addition, you can’t use Resource Governor to allocate priorities amongst your OLTP applications. Short OLTP queries aren’t good candidates for being governed this way. Resource Governor works really well for long-running queries that eat up resources over a long period.

Finally, at nearly every site that I work on, SQL Server is I/O bound, not CPU or memory bound. SQL Server systems that are CPU bound tend to have a limited set of problems, such as plan cache pollution problems that are causing constant recompilation. Although limiting memory can help avoid starve out smaller queries in some data warehousing scenarios, what I would like to see is the ability to govern file I/O on a file-by-file basis. Despite these shortcomings, I think you’ll find that Resource Governor is a great addition to your toolkit and an excellent option to help you regain control of your systems.

Listing 1: Sample CREATE RESOURCE POOL Commands

CREATE RESOURCE POOL LowPriorityAppsPool  WITH (MAX_CPU_PERCENT = 20);CREATE RESOURCE POOL MediumPriorityAppsPool  WITH (MAX_CPU_PERCENT = 60);CREATE RESOURCE POOL HighPriorityAppsPool  WITH (MAX_CPU_PERCENT = 100);

Listing 2: Sample CREATE WORKLOAD GROUP Commands

CREATE WORKLOAD GROUP UnidentifiedApplications  USING LowPriorityAppsPool;CREATE WORKLOAD GROUP WellBehavedAccessApplications  USING MediumPriorityAppsPool;CREATE WORKLOAD GROUP PoorlyBehavedAccessApplications  USING LowPriorityAppsPool; CREATE WORKLOAD GROUP WellBehavedExcelApplications  USING MediumPriorityAppsPool;CREATE WORKLOAD GROUP CriticalCorporateApplications  USING HighPriorityAppsPool;

Listing 3: Code to Assign Sessions to Workload Groups

CREATE FUNCTION dbo.UserClassifier ()  RETURNS SYSNAME WITH SCHEMABINDINGASBEGIN  DECLARE @WorkloadGroup SYSNAME    = N'UnidentifiedApplications';  SET @WorkloadGroup = CASE APP_NAME()    WHEN N'Excel_Link_BR3' THEN      N'WellBehavedExcelApplications'    WHEN N'Excel_Link_GR8' THEN  N'WellBehavedExcelApplications'    WHEN N'HR_Access_G13' THEN  N'WellBehavedAccessApplications'    WHEN N'HR_Access_G19' THEN  N'PoorlyBehavedAccessApplications'    WHEN N'Corp_HR_Maintenance' THEN  N'CriticalCorporateApplications'    ELSE N'UnidentifiedApplications'  END;  RETURN @WorkloadGroup;END;