SQL Server Resource Governor

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