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;
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.
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.”
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()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Workload_Group AS SYSNAME
IF(SUSER_NAME() = 'Bad Guy')
SET @Workload_Group = 'Bad Guy Test'
ELSE
SET @Workload_Group = 'default'
RETURN @Workload_Group
END
GO
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.
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.udf_Resource_Governor_Classification);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
You can find more interesting information on this topic:
