Sql-server – Set maximum cpu usage to a single job in SQL server

sql server

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.

Best Answer

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).

Related Question