Solved

sql2008 R2 - how to configure resource governor for heavy load stored procedures

Posted on 2014-12-10
2
254 Views
Last Modified: 2015-01-07
Guys,
I'm trying to figure out how to use resource governor to  limit some of resources when specific procedures run and how to use this resource governor when doing a backups
0
Comment
Question by:motioneye
2 Comments
 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40490723
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40491597
You can't specify a limit of a resource for stored procedures. Only users or applications.
For backing up with limited resource, create a user only for running backups and set is limit in resource governor, so when that user runs a backup will run it with the specified resource limit.
Here's an example of the code:
-- Before this the Login need to be created first
EXEC sp_addrolemember 'db_backupoperator', 'LoginName';

-- Configure Resource Governor.
BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%. 
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
   WITH
      (MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool. 
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into 
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (SUSER_NAME() = 'LoginName')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
    RETURN @workload_group_name
END;
GO

-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_MAX_CPU);
COMMIT TRAN;
GO
-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Open in new window

Disclaimer: This code is based in the this MSDN article so the example is set a limit of 20% of CPU usage. Of course you can change it to a value that you want.
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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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

19 Experts available now in Live!

Get 1:1 Help Now