Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

Using of resource governer in sql2014

Guys,
I'm trying to understands how do I use resource governer here to limit cpu or memory used when  some queries are running or during the backup activities were perform.
I have so far created a workgroup and also a resource pool, but I can't seems find a way how to bind the backups or some of the query that going to be candidate here.

any advise ?
0
motioneye
Asked:
motioneye
1 Solution
 
Barry CunneyCommented:
Hi MotionEye,
One of the key components of setting up Resource Governor is the 'classifier function' which dictates what processes are assigned to what workload groups and in turn how much resource each process is allocated
Please see sample classifier function below which has logic to decide the workload group based on the logged in user and the application name - you could possibly set this up so as it checks for the user under which your backup job runs:
USE master;
GO
 
CREATE FUNCTION resource_Class_funct() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
  
  IF (USER_NAME() LIKE '%YOUR_BACKUP_USER%')
      SET @workload_group = 'YourWorkGroupA';
  IF (APP_NAME() LIKE '%REPORT%')
      SET @workload_group = 'YourWorkGroupB';
      
  RETURN @workload_group;
END;

Open in new window


Resource Governor must then be told to use this classifier function:
ALTER RESOURCE GOVERNOR
      WITH (CLASSIFIER_FUNCTION=dbo.resource_Class_funct);
ALTER RESOURCE GOVERNOR RECONFIGURE

Open in new window

0
 
motioneyeAuthor Commented:
Thanks Barry Cunney :),
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now