SQL Server Resource Governor Configuration with T-SQL and SSMS

By:   |   Updated: 2019-07-09   |   Comments (1)   |   Related: > Resource Governor


Problem

SQL Server is usually accessed by numerous users and applications. When all applications are in use, overall performance can impact higher priority tasks. There can be cases where some of the applications are business critical therefore, they should not be disturbed by other applications. So, the problem is how can we allocate resources to specific processes that are run in SQL Server, so high priority processes are not impacted? In addition, there can be instances where DBAs or developers need to execute ad hoc queries. So, what is the solution to enable a business-critical application to not be impacted or impacted less by other running applications.

Solution

Before SQL Server 2008 there were a few things you could do to solve this problem such as:

  • Moving databases to a different server. There are two issues with this. You need additional server licenses for which you would need to spend money. Also, if the databases are shared between systems then you will gain little to nothing by moving the databases to another server.
  • You can use SQL Server techniques like database mirroring, log shipping, database snapshots, and replication. Except for replication, all the other options will result in read-only databases and the data will not always be current. All of these systems (including replication) need additional licenses for SQL Server along with the installation of the OS which will cost additional money. In addition to this, replication might require some schema changes to the database, which may not be feasible in all scenarios.

Another option is to use the SQL Server Resource Governor.

SQL Server Resource Governor

In simple terms, the Resource Governor is a mechanism where you allocate your resources to resource groups, where you can define your groups based on applications or users and therefore limit how much resources are used.

Let me put forward the mechanism in simple terms. First, we will create a workload group called GroupAdmin. Then we can define which connections will consume this workload group by defining a Classifier function. Then we allocate that group to a resource pool called PoolAdmin which will be configured for maximum CPU usage of 40%. This means all connections that belong to the GroupAdmin resource pool will consume a maximum CPU% of 40 and this will not pull resources from other operations.

With that basic understanding (it may not be all the concepts of Resource Governor, but this is sufficient for the moment) let's perform an implementation of Resource Governor. These configurations can be done either through T-SQL or through the SQL Server Management Studio interface. I will first do them using T-SQL code.

Configuring SQL Server Resource Governor with T-SQL

Enable SQL Server Resource Governor

First, you need to enable the Resource Governor feature by running the following script.

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

Create Resource Governor Workload Groups

Then, we need to create workload groups. These will be the different groups that connect to SQL Server to determine resources limits for the group.

CREATE WORKLOAD GROUP [GroupReports] 
CREATE WORKLOAD GROUP [GroupAdmin] 
CREATE WORKLOAD GROUP [GroupDWH] 
GO

Create Resource Governor Classifier Function

The next step is to direct relevant connections to these workload groups by means of a classifier function. Make sure you create this in the master database. (I had some trouble the first time when I had created the function in another database). This function is nothing but a scalar-valued function.

USE [master]
GO
 
CREATE FUNCTION [dbo].[RG_Classifier]() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname

    IF (SUSER_NAME()  = 'sa')
        SET @grp_name = 'GroupAdmin'

    IF (SUSER_NAME()  =  'Reports')
        SET @grp_name = 'GroupReports'

    IF (SUSER_NAME() LIKE 'DataW%')
        SET @grp_name = 'GroupDWH'

    RETURN @grp_name 
END;
GO

The above classifier function is overly simplified for the sake of demonstration purposes. What this function says is, if the user name is sa, it belongs to the GroupAdmin and if the user name contains "Reports" then it belongs to the GroupReports workload group and so on.

You can create multiple classifier functions, but only one can be registered with the Resource Governor at a time.

A good example of multiple classifier functions is payroll calculation. On payday, you need to provide your payroll application extra resources. So, you can activate that classifier function on payday.

Assign Resource Governor Classifier Function to Use

Below we are stating that we want to use the classifier function RG_Classifier.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo. RG_Classifier);
GO

-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

A thing to note is that after you create the classifier function and assign it to the Resource Governor, you are not allowed to drop or alter it. Even if you disable Resource Governor, you are not allowed to alter the classifier function once it is assigned.

To work around this issue, you can create another classifier function and assign it to the Resource Governor and do the changes to the first function and allocate it back to the Resource Governor. Yes, I agree that this should not be the way to do this, but this is a way to make a change.

Define Resource Governor Pool Settings

The next step is to define each work group’s resource limits as in the following code where all connections which belong to GroupAdmin can utilize a maximum of 50% of CPU.

-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolAdmin
WITH (MAX_CPU_PERCENT = 50);
 
ALTER WORKLOAD GROUP GroupAdmin
USING PoolAdmin;

Similarly, we will create resource pools and assign to the relevant work groups. 

-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolReports
WITH (MAX_CPU_PERCENT = 80);
 
ALTER WORKLOAD GROUP GroupReports
USING PoolReports;
 
-- Create a new resource pool and set a maximum CPU limit.
CREATE RESOURCE POOL PoolDWH
WITH (MAX_CPU_PERCENT = 30);
 
ALTER WORKLOAD GROUP GroupDWH
USING PoolDWH;

Testing SQL Server Resource Governor Settings

Testing the Resource Governor is the difficult since you have to generate some load which will "eat" your resources substantially.  The following query will create a workload which will eat up resources. This is query is written with the AdventureWorks database and has multiple cross joins to make this query consume large resources, other than that this query does not have any real purpose.

SELECT * 
FROM Production.TransactionHistory TH 
FULL OUTER JOIN (
   SELECT
      T.ReferenceOrderID 
   FROM HumanResources.Department D 
   CROSS JOIN Production.ProductSubcategory P 
   CROSS JOIN Production.TransactionHistory T 
   CROSS JOIN Production.Location 
   WHERE
      CAST(T.TransactionDate AS DATE) NOT IN (SELECT DISTINCT ModifiedDate FROM HumanResources.Department) 
      OR P.Name Like '%Teee%'  
   ) O 
  ON TH.ReferenceOrderID = O.ReferenceOrderID

Another possible way to create high CPU usage is to create an index on a large table using several columns.

To measure each load from different users, we can use Windows Performance Monitor a.k.a. Perfmon. With the Resource Governor, there is an improvement with this tool. In perfmon, you get a new performance object called SQL Server:Resource Pool Stats, where you can select Counters and Pools (instances). For this test, CPU usage % is added for all the Resources Pools.

CPU usage % for is added all the Resources Pools

The following graphs show how each resource pool consumes CPU on the server.

The graphs show you how each resource pool consumes CPUs of your server differently

An important thing is that when you set the CPU MAX to 50%, it is limiting the CPU to 50% only when there is CPU pressure. When there is no CPU pressure it will consume more than 50% even though the maximum limit is set to 50%.

Configuring Resource Governor Using SSMS

All these configurations can be performed with SQL Server Management Studio as well.

To create a new resource pool in SSMS, go to Management > Resource Governor, right click and select New Resource Pool.

You can also manage Resource Governor settings by right clicking on Resource Governor and selecting Properties and you will get a screen like below where you can set the values.

Resource Governor in SQL Server Management Studio

SQL Server Resource Governor Template

In the Template Browser of SSMS, there is one template for Resource Governor. From this template, you can create Resource Pools, Resource Groups and the Classifier Function.  To get this go to View > Template Explorer and then in the Template Browser look for Resource Governor.  If you double click on the Configure Resource Governor it will generate a script template you can use to configure the Resource Governor.

Important Notes about SQL Server Resource Governor

  • Not all resources can be governed by the Resource Governor. IO, CPU, Memory, Degree of Parallelism are the resources you can control from Resource Governor.
  • Resource Governor supports SQL Server and Azure SQL Database (Managed Instance). It does not support Azure SQL Data Warehouse and Parallel Data Warehouse.
  • Resource Governor can only be applied to SQL Server database engine services. You cannot enable Resource Governor for SQL Server Analysis Services (SSAS), SQL Server Integration Service (SSIS) or SQL Server Reporting Services (SSRS).
  • Resource Governor is available only with Enterprise Edition and Developer Edition. Since Enterprise Edition is costly, unfortunately, you need to pay more money to use this feature.
  • Actual resources may exceed settings. Let us say you have two Resource Governor pools, A and B. A is configured for a Maximum of 70% of CPU and B is configured for 50% Maximum CPU. Question is, what percentage would pool A utilize when only pool A resources are there. (i.e. no workloads for pool B). Many believe though it is 70% since it is configured for pool A. But this is not the case. Although it is configured for 70%, when the specific pool has the ability consume more resources without conflicting with other resource pools it will consume more than 70%, which means although it is configured for 70%, when there are no other workloads it can go to 100%.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dinesh Asanka Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2019-07-09

Comments For This Article




Monday, August 24, 2020 - 9:09:29 AM - Uwe Ricken Back To Top (86350)
Hi Dinesh,

very informative sum up for use of RG. You wrote that it is not allowed to drop the classifier function if it is bound to the RG.
Your workaround by creating another function and replacement of the former function is ONE solution.

But if you don't want to have any function you can deallocate it by using the following statment

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

This will work without replacing the original function by another one :)














get free sql tips
agree to terms