Solved

Using of  resource governer in sql2014

Posted on 2016-08-16
2
46 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

OfficeMate Freezes on login or does not load after login credentials are input.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 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