SQL: Getting comfortable working with Resource Governor

In SQL Server versions prior to SQL Server 2008, the database engine had always tried to provide balanced performance to all concurrent users. Most DBAs however have come across situations where they have wished they had control over the priority assigned to users or workloads as the balanced approach that SQL Server provided might not have suited their business needs.

Way back in SQL Server 2008, Microsoft introduced the concept of resource governance and allowed us to exercise a degree of control in this area. The implementation still has a number of limitations but what was supplied was sufficient to deal a large number of common scenarios. As an example of how this technology might be used, I’ll describe a scenario that I commonly encounter.

Many DBAs seem to have little ability to make changes to their databases or to regulate their use. This tends to happen for a variety of reasons. One reason I often see is that over the years, various users have requested and been granted direct permissions on the tables and views in the databases. Often this has been put in place to support analysis by applications such as Microsoft Excel or Microsoft Access when IT departments have not been able to keep pace with the requirements of the end users or where those users wanted much more flexibility in their analysis. Many organizations have hundreds (or even thousands) of these applications throughout the enterprise and the DBAs have little knowledge of who is really even accessing the database. The problem is compounded by the use of fixed SQL Server logins for large number of users of applications.

This tends to lead to a situation where database refactoring such as schema changes or improvements is impossible because there is no way for the DBAs to know in advance whether the changes they make will end up breaking applications used by lots of staff. Worse, they spend their time fixing performance issues caused by the rogue queries generated by the inappropriate use of dynamic query tools. All users end up being impacted by the long running queries from these problematic users. Other DBAs know that every time certain reports are run that their systems slow substantially. Resource Governor can’t help with the refactoring problem but it can help with the performance problems and also help somewhat with identifying the applications actually being used.

Resource Governor

Resource Governor works on the basis of controlling the CPU, memory, and since SQL Server 2014, I/O allocated to a session. For CPU and memory, we can choose maximum and minimum percentages of these along with the maximum degree of parallelism to be permitted. For I/O, we can choose minimum and maximum I/Os per volume.

The first thing we need to decide on is the broad pools of resources that we want to be able to allocate sessions to:

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);

In this case, we’ve created three pools that are limited on the basis of CPU percentage. These percentages are not hard limits. For example, if a single low priority application is the only application running on the system, it will receive total use of the CPU. Governing only occurs in times of overlapping concurrent demands.

Also worth noting is that there is 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 clean up operations) and the other is the default pool used for sessions not assigned to other pools. That means it is important not to create too many too early and to carefully consider which ones might be needed.

The next thing we need to categorize is the applications:

CREATE WORKLOAD GROUP UnidentifiedApplications
  USING LowPriorityPool;

CREATE WORKLOAD GROUP WellBehavedAccessApplications
  USING MediumPriorityPool;

CREATE WORKLOAD GROUP PoorlyBehavedAccessApplications
  USING LowPriorityAppsPool;

 CREATE WORKLOAD GROUP WellBehavedExcelApplications
  USING MediumPriorityPool;

CREATE WORKLOAD GROUP CriticalCorporateApplications
  USING HighPriorityPool;

In this case, we’ve created five groups and assigned them to the three pools. We’ve decided to keep the Access and Excel applications that we know in separate pools, in case we wish to change the resources allocated to one versus the other at some later time. Poorly behaving Access applications and applications that we can’t identify will be given very low resources. Applications that we have identified as critical to our business will be given the highest priority on resources.

After this, we can start to see the current configuration using dynamic management views. Executing the command:

SELECT * FROM sys.dm_resource_governor_configuration;

produces the following output:

This tells us two things. First, most changes to the configuration for resource governor don’t take effect immediately. The is_reconfiguration_pending flag is telling us that we need to execute the command:

ALTER RESOURCE GOVERNOR RECONFIGURE;

before our changes will take effect. Second, the value of zero for the classifier_function_id tells us that no classifier function has been assigned. A classifier function is used to determine which workload group a given session should be placed in. As a side note, I feel this column should return NULL, not zero for this value. I posted the request as feedback during the CTP cycle but sadly was told that while the developer agreed, it was too late to change it.

We can see our resource pool configuration by executing the command:

SELECT * FROM sys.dm_resource_governor_resource_pools;

which returns:

Note that until we executed the reconfiguration, only the internal and default pools would have appeared in this output.

We can also see our workload pool configuration by executing the command:

SELECT * FROM sys.dm_resource_governor_workload_groups;

which returns:

The final key step is that we need a way to identify the sessions that we want to control. This is done by a classifier function which assigns a session to a specific workload pool:

CREATE FUNCTION dbo.UserClassifier ()
  RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  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;

It is worth spending a few moments discussing these classifier functions. Importantly, they need to return a value of type SYSNAME (which is currently an alias for NVARCHAR(128)).

APP_NAME

In the example shown, I have based the decision about which workload group a session goes into on the application name APP_NAME() function. This returns the value that was specified in the connection string from the application. I have a particular liking for this option as it allows decisions based on which application is running.

Applications are often built without specifying the application name in the connection string but it is important to get the developers to change this or the implementers to specify the value in application configuration files. It is useful for other purposes apart from resource governor. This value also appears in traces created by SQL Server Profiler or Extended Events and can be used to filter events within the traces. This can be very useful when you are trying to debug issues with a specific application or to isolate issues being encountered by just that application.

I have also found it to be a very 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 quickly be placed in a very low priority resource pool and it’s likely that the users involved will quickly then identify the applications involved. This helps the overall issue of trying to work out who is actually using your systems.

Other functions

While in the example, the APP_NAME() function has been used, many other functions can be used such as: HOST_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER() and IS_MEMBER(). If you decide to use either HOST_NAME() or APP_NAME() functions, also note that it is possible for these to be altered by the user. In practice however, I have found APP_NAME() to work very well.

In addition to these functions, you can use a number of properties. The LOGINPROPERTY() function has two properties that can be used in classification functions: DefaultDatabase and DefaultLanguage. The CONNECTIONPROPERTY() function provides access to the network transport and protocol being used for the connection, along with 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 interesting for these functions as it returns you the name of the database that was provided when the session first connected, as opposed to the default database.

Testing the classifier function

It is really important to test the classifier function before putting it into use or you can cause your system to become unresponsive. As an example, executing the command:

SELECT APP_NAME();

In my SQL Server Management Studio connection returns:

so we should not be surprised that executing:

SELECT dbo.UserClassifier();

returns the following:

This is because SQL Server Management Studio wasn’t in our list of known applications. We might want to change that but I’ll leave that as an exercise for the reader at present.

Our classifier function could have also looked up the workload pool name in a database table. This would allow potentially more flexibility. The performance impact of this is typically not great as the table would not be large, would quickly become cached and the classification decisions are only taken at the time a connection is first established.

Assigning the classifier function

The final step is to assign our classification function:

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

and to again reconfigure:

ALTER RESOURCE GOVERNOR RECONFIGURE;

At this point, our resource governor configuration is complete unless we want to configure something related to I/O as well. We then need to make sure it is working as expected.

Classifier function errors

If we did a poor job of testing our classifier function, we might need to use the Dedicated Admin Connection (DAC) to gain access to SQL Server. If we have not configured the DAC, an alternative in desperate situations would be to restart the server in single user mode. When running in single user mode, your connection is not subject to resource governing but this might be useful to allow disabling it until the situation is corrected.

As a general rule, Resource Governor changes are going to be best implemented by the most senior DBA available at the location, given the level of impact that misconfiguration could bring. It is also the reason I have used T-SQL scripts for implementation in this article even though there is a level of GUI interface to these functions in SQL Server Management Studio. CONTROL SERVER permission is required to change the configurations and viewing the configuration requires VIEW SERVER STATE permission. Failures of classifier functions can be detected via events in the Application Log on the server.

Monitoring

Apart from the details we can obtain from the dynamic management views, it is possible to monitor the CPU resources via the System Monitor (permon.exe) tool. Note that when we add counters, the SQLServer:Resource Pool Stats object has instances for each of our configured resource pools.

Similar options have been added in the SQLServer:Workload Group Stats counter. Relevant values can also be obtained by administrators who query the sys.dm_os_performance_counters view.

To enable more detailed monitoring additional events have been added to SQL Server: CPU Threshold Execeeded, PreConnect:Starting and PreConnect:Completed. For those that might want to control resource governor programmatically from .NET code, a ResourceGovernor class has been added to SQL Server Management Objects (SMO).

Careful configuration

When configuring resource pools, we have focussed on maximum CPU percentages. It is important to be very careful about configuring minimum percentages. It is easy to greatly limit the resources on a system by configuring the minimum values too high, particularly minimum percentages for memory.

Hopefully that all sounds interesting up to this point and you might be starting to wonder about those limitations I mentioned earlier. I can hear people saying “OK Greg, this looks great. Where’s the catch?”

Limitations

First, resource pools and workload groups cannot be dropped while they are in use. No big surprise there except they could be in use for quite a while on busy systems.

Second, you might imagine that Resource Governor would be useful for dropping the priority of that pesky query that you’ve just noticed is killing the performance of your system right now. Unfortunately, you can’t use it to do that. The groups and pools were allocated when that session connected.

Third, you might imagine that it might help allocate priorities amongst your OLTP applications. Unfortunately, short bursty OLTP queries are not good candidates for governing this way. Resource Governor works really well for those long running queries that eat up resources over a longer period.

Lastly, 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 very limited set of issues, such as plan cache pollution issues causing constant recompilation. What I originally really wanted was I/O governance and fortunately, SQL Server 2014 introduced that as well.

In the meantime, Resource Governor is a useful addition to our toolkit that’s worth exploring further and an excellent option to help those DBAs trying to regain control of their systems.

2025-10-17