Resource Governor questions.

Question

Greetings. I've never used RG before and dont want to screw it up my first time. ;-) One one specific instance, we have a resource heavy DB. What I want to do is cap memory and CPU usage for that DB to 75%. Most examples of RG/ classifier functions that I can find are either based on login name or application name -- I want to cap based on DB name itself, regardless of login name or app name. Will the below code get me there? Also, is there a better way to be doing this?

USE [master]
GO
alter FUNCTION [dbo].[fn_workloadGroup_myDB]()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
declare @workload_group sysname
IF ORIGINAL_DB_NAME() = 'myDB'
set @workload_group = 'myDB';
RETURN @workload_group
END
GO
USE [master]
GO

/****** Object:  ResourcePool [myDB]    Script Date: 4/25/2023 8:17:52 AM ******/
CREATE RESOURCE POOL [myDB] WITH(min_cpu_percent=0, 
		max_cpu_percent=75, 
		min_memory_percent=0, 
		max_memory_percent=75, 
		cap_cpu_percent=100, 
		AFFINITY SCHEDULER = AUTO
, 
		min_iops_per_volume=0, 
		max_iops_per_volume=0)
GO
USE [master]
GO

/****** Object:  WorkloadGroup [myDB]    Script Date: 4/25/2023 8:18:24 AM ******/
CREATE WORKLOAD GROUP [myDB] WITH(group_max_requests=0, 
		importance=Medium, 
		request_max_cpu_time_sec=0, 
		request_max_memory_grant_percent=25, 
		request_memory_grant_timeout_sec=0, 
		max_dop=0) USING [myDB], EXTERNAL [default]
GO

Answer 1

As they say, that depends. More precisely, it depends on whether original_db_name() returns something you can trust.

That is, if users are logging in from an application and have that database in the connection string it will work, at least sort of. (And assuming that the application does not work in that database.)

But if you have users that connect to the server to run ad-hoc statements through SSMS, the chances for success are smaller. Blenda may have master as her default database, and she does not specify the database when she connects, but she is content with switching to the database when she has connected. (And maybe she's moving around among the databases.) Thus, Blenda will run without the cap. (And surely if she discovers that things run faster if she takes the roundabout way, she will do that.)

So I would say that in the general cases, this will not work, but some confined cases it may.