Solved

Using of  resource governer in sql2014

Posted on 2016-08-16
2
54 Views
Last Modified: 2016-09-27
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
Comment
Question by:motioneye
2 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 41757655
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
 

Author Closing Comment

by:motioneye
ID: 41817383
Thanks Barry Cunney :),
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now