Is my Resource Governor set up correctly?

Question

Greetings.

I have a particular server configured as follows:

1. DB1 -- this guy is the Subscriber of a transaction replication topology.
2. DB2 -- this guy is a Data Warehouse (using that term loosely). If queries data from DB1, as well as other sources regularly during the day. It mashes that data up, and Power BI then comes along every few hours to use for reports.

When DB2 gets too busy, it can cause replication latency delays. The biggest resource contention there by far is RAM. I've implemented Resource Governor -- the goal is to make sure DB2 doesn't cause these issues. It uses 4 main logins for processing, which is what I used to create my classifier function. However, Ive never used this before -- can someone please tell me if I've got it configured correctly to accomplish my goals?

Thanks!

USE [master]
GO
/****** Object:  WorkloadGroup [groupSURep]    Script Date: 9/18/2024 10:44:58 AM ******/
CREATE WORKLOAD GROUP [groupSURep] 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 [poolSURep], EXTERNAL [default]
GO
USE [master]
GO
/****** Object:  ResourcePool [poolSURep]    Script Date: 9/18/2024 10:44:29 AM ******/
CREATE RESOURCE POOL [poolSURep] WITH(min_cpu_percent=0, 
		max_cpu_percent=100, 
		min_memory_percent=0, 
		max_memory_percent=70, 
		cap_cpu_percent=70, 
		AFFINITY SCHEDULER = AUTO
, 
		min_iops_per_volume=0, 
		max_iops_per_volume=0)
GO
CREATE FUNCTION Class_funct() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
 
  IF ( SUSER_SNAME() = 'domain\thisGuy')
      SET @workload_group = 'groupSURep';
 
  IF ( SUSER_SNAME() = 'domain\thatGuy')
      SET @workload_group = 'groupSURep';
 
  IF ( SUSER_SNAME() = 'domain\theOtherGuy')
      SET @workload_group = 'groupSURep';
 
  IF ( SUSER_SNAME() = 'domain\theLastGuy')
      SET @workload_group = 'groupSURep';
    
  
  RETURN @workload_group;
END;

Answer 1

You say that the RAM is the biggest contention. But in what way? Are the logins in DB2 running wild queries that requires big memory grants for sort and hashing operations? Or are they just scanning big tables, squeezing out other data from the buffer pool?

In the first case, Resource Governor may be able to help you, because what you can control is the memory grants. Memory grants are easy, because they are accounted to a single session. Whether the numbers you have are the best ones, that is impossible to tell, since the depends on your workload.

In the second case, Resource Governor cannot help you, since the buffer pool is a common resource, and cannot be accounted to a resource pool or workload group.

Answer 2

Hi @ chrisrdba,

Finally, the classification function should be bound to the Resource Governor so that the Resource Governor can map requests to the correct workload classification based on the classification function.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFunction);
ALTER RESOURCE GOVERNOR RECONFIGURE;
 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.