Resource Governor is one of the SQL Server enterprise features that are not available in any other edition. If you are interested in various limitations across editions, I have summarized them in the article SQL Server Installation – Planning, HW and SW Requirements. If you are experiencing performance problems and have tried various optimization and scale-out options, this is another way to limit physical resources. This feature allows you to set limits on CPU and RAM resource utilization within a SQL Server instance.

This feature is effective only for operations on databases (database engine) and cannot be used to control resources for Analysis, Integration services, or Reporting services. Resources can be restricted using this feature for virtually anything – users, time-based (day/night), or other criteria.

Enabling Resource Governor in SQL Server

You can enable this feature in two ways, through the visual interface or T-SQL script: ALTER RESOURCE GOVERNOR RECONFIGURE;

Resource Governor zapnuti

Configuring Resource Governor

Configuring this tool is a bit more complex.

Resource Pools

Resource pools can be described as containers for the physical resources of the server. In each such pool, one or more Workload groups can exist. It works like this: when a session is started (e.g., a SQL unoptimized script), Resource Governor classifies this session into a workload group, and that group is assigned to a resource pool. The session is then served using the resources defined for that resource pool. Within each resource pool, you define MAX and MIN values for CPU and memory.
In the image below, I have created a Resource Pool named “Test” and assigned it a maximum of 50% CPU and 50% Memory.

Resource governor pool

Workload Groups

While Resource pools serve as containers for physical resources, workload groups are containers for sessions. These sessions are placed in workload groups using the classification function (see below). If no workload group is defined by the function, SQL Server allocates resources as defined in the default pool.
In the image below, I have created a Workload group named “Bad Guy test.”

Workgroup Resource governor

Creating a Function that Returns Workload Group

The next step is to create a user-defined function that will return the name of a Workload Group depending on whether the query was executed by the “Bad Guy.”

CREATE FUNCTION dbo.udf_Resource_Governor_Classification()
IF(SUSER_NAME() = 'Bad Guy')
SET @Workload_Group = 'Bad Guy Test'
SET @Workload_Group = 'default'
RETURN @Workload_Group

Binding the Function to Resource Governor

The final step is to specify that this function should be used as the classification function, and resources should be allocated accordingly.

WITH (CLASSIFIER_FUNCTION=dbo.udf_Resource_Governor_Classification);

You can find more interesting information on this topic:

Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *