Using of  resource governer in sql2014

motioneye
motioneye used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
Thanks Barry Cunney :),

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial