The SQL Server Resource Governor is a (Enterprise) feature to allow an administrator to limit the CPU and/or memory usage by a given workload.
There are three major components to setup the Resource Governor.
First, a resource pool is created. Here is where we will set the physical limits that we want to impose.
Second is the workload group. This group will be linked to a resource pool. We’ll also map workloads to this group.
Third is the classifier function. This is a user-defined function that will map a particular workload to a workload group.
Below is a code sample where I run through the setup. In this case, all requests that come from SSMS will be subject to the workload limits (in this case, no more than half of the CPU and half of the available memory).
use master; go -- 1) Drop objects if they already exist if exists (select * from sys.resource_governor_workload_groups where name = 'TestWorkloadGroup') drop workload group TestWorkloadGroup go if exists (select * from sys.resource_governor_resource_pools where name = 'TestResourcePool') drop resource pool TestResourcePool go alter resource governor with (classifier_function = null); alter resource governor reconfigure; go if object_id('TestClassifier') is not null drop function TestClassifier go -- 2) Enable Resource Governor alter resource governor reconfigure; go -- 3) Create resource pool create resource pool TestResourcePool with ( max_memory_percent = 50, min_memory_percent = 0, max_cpu_percent = 50, min_cpu_percent = 0 ); go -- 4) Create workload group create workload group TestWorkloadGroup using TestResourcePool; go -- 5) Create classifier function create function dbo.TestClassifier() returns sysname with schemabinding as begin declare @ReturnGroup sysname = 'default' if app_name() = 'Microsoft SQL Server Management Studio - Query' set @ReturnGroup = 'TestWorkloadGroup' return @ReturnGroup end; go -- 6) Link function to group alter resource governor with (classifier_function = dbo.TestClassifier); alter resource governor reconfigure; go