Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using of  resource governer in sql2014

Posted on 2016-08-16
2
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 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