Resource Governor questions.

Question

https://www.sqlshack.com/sql-server-2014-resource-governor/

Greetings. I'm using the link above to configure Resource Governor. I run some queries to max out the RAM usage but my PerfMon counters dont elevate the way theirs do. I can see RAM usage increase from my queries via Task Manager. It should be noted this is a DEV box so I've had the luxury of restarting SQL to free up the RAM and start from near 0%. I've also made sure that my connections make it in to the correct pools. Any ideas?

alter RESOURCE POOL [poolSURep] WITH(
min_cpu_percent=00, 
		cap_cpu_percent=80, 
		min_memory_percent=00, 
		max_memory_percent=70, 
		AFFINITY SCHEDULER = AUTO
)
GO
create RESOURCE POOL [poolNonSURep] WITH(
min_cpu_percent=00, 
		cap_cpu_percent=20, 
		min_memory_percent=00, 
		max_memory_percent=30, 
		AFFINITY SCHEDULER = AUTO
)
CREATE WORKLOAD GROUP [groupSURep] 
USING [poolSURep]
GO
CREATE WORKLOAD GROUP [groupNonSURep] 
USING [poolNonSURep]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
USE master;
GO
 
 --select SUSER_SNAME()
  /*
 ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
*/
alter FUNCTION Class_funct() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
 
  IF ( SUSER_SNAME() = 'myLogin')
      SET @workload_group = 'groupSURep';
  IF ( SUSER_SNAME() <> 'myLogin')
      SET @workload_group = 'groupNonSURep';
     
  RETURN @workload_group;
END;
go 
 USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_funct);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
go
--make sure the pools are being used
SELECT ConSess.session_id, ConSess.login_name,  WorLoGroName.name
  FROM sys.dm_exec_sessions AS ConSess
  JOIN sys.dm_resource_governor_workload_groups AS WorLoGroName
      ON ConSess.group_id = WorLoGroName.group_id
	  where WorLoGroName.name not in ('internal','default')

Answer 1

Hi @chrisrdba ,

Thanks for your kindly feedback!

However, the the max_memory_percent still isn't being enforced for either of my pools, as shown in the attached.

MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT: These settings are the minimum and maximum amount of memory reserved for the resource pool that cannot be shared with other resource pools. The memory referenced here is query execution grant memory, not buffer pool memory (for example, data and index pages).

Please refer to this official document, it will show you more useful information in detail.

The one you posted didn't either, but I launched some DBCC CHECKDB 's and they made my RAM usage take off.

SQL will always use up the RAM it has to cache data. Setting a min/max RAM for your instance is always a good idea (especially MAX, so you keep some RAM for your OS).

Right click your instance in SSMS -> Memory -> Fill in min/max memory. It's a good idea to leave at least 4 GB for your OS on a SQL dedicated machine. If you have other services running, keep those in mind as well.

Hope the explanation above can solve your concerns!

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

Hi @chrisrdba ,

Thank you for reaching out and welcome to Microsoft Q&A!

I run some queries to max out the RAM usage but my PerfMon counters dont elevate the way theirs do. I can see RAM usage increase from my queries via Task Manager.

Please login SQL Server with the 'mylogin' you mentioned and run your test script.

We should add your resource pool to the PerfMon counter first and then run the test script, observe the changes of the performance counter.

You can also use the test script below:

SET NOCOUNT ON 
DECLARE @i INT
WHILE 1=1
BEGIN
   SELECT @I=COUNT(*) FROM sys.objects
END
SET NOCOUNT OFF

Feel free to share your issues here if you have any concerns.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications