2

Is it possible to restrict cpu usage for a job in SQL Server?

I found Resource Governor does work for an instance of mssql for a specific users group. However, I only need to set the maximum cpu usage to a single Job not to the instance of mssql as there is only one user that performs all the operations in the database.

2
  • Why can't you create a separate login to run this job? Jul 4, 2014 at 4:50
  • I have created a new user and assigned some Jobs to that user. However, I can't see any difference in cpu usage from the Resource Monitor. I would like to assign some cpu usage restrictions to this new user. Is this possible? In this way these jobs won't chew up all the cpu. Thank you.
    – Francisco
    Jul 4, 2014 at 5:41

1 Answer 1

3

You can set the job to be owned by a specific login, and use the Resource Governor to limit CPU for that login.

CREATE RESOURCE POOL [Pool_ThatJob] WITH
(
    max_cpu_percent=50, 
    cap_cpu_percent=50 -- 2012+ only 
);
GO

CREATE WORKLOAD GROUP [Group_ThatJob]
  USING Pool_ThatJob;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT CASE ORIGINAL_LOGIN() WHEN N'that_login'
    THEN N'Group_ThatJob' ELSE N'default' END);
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

This won't take effect if the job is already running; it will come into play the next time the job starts (or if that user establishes any other sessions).

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.