I'm trying to setup Resource Governor for limiting IOPS for processes running using SQL agent jobs. The classifier function is set to identify a particular login (any spid that is running using that login should use the resource group it is assigned to). I then added EXECUTE AS LOGIN = 'ResourceGovernerUser'
within the job, but I cannot get it to work and it falls back to the default pool because the jobs are 'EXECUTED AS 'ServiceAccount''
. Although if I look at the active processes using sp_WhoisActive
, the login name is displayed as 'ResourceGovernerUser'
instead of the Service Account. So I modified the classifier function to use the Service Account and then it just worked. I verified this using the perfmon counters 'Disk Read IO/Sec'
and 'Disk Write IO/Sec'
under 'Resource Pool Stats'
object.
The question is – how do I get Resource Governor to use a login other than the Service Account (when used with agent jobs)? I do have some untested ugly ideas like – running the jobs using a CmdExec or PowerShell proxy. If anyone has come across a similar situation or has any better ideas, I would really appreciate it. Thank you.
--Example Setup:
USE [master]
GO
CREATE RESOURCE POOL [SqlJobPool] WITH(
min_iops_per_volume=1,
max_iops_per_volume=5000);
GO
CREATE WORKLOAD GROUP [IOGroup]
USING [SqlJobPool];
GO
CREATE FUNCTION [dbo].[fn_LimitedIO]()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp SYSNAME;
IF SUSER_NAME() = N'ResourceGovernerUser' -- When this is set to the Service Account, it works
BEGIN
SET @grp = N'IOGroup';
END
ELSE
BEGIN
SET @grp = N'default';
END
RETURN @grp;
END
GO
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fn_LimitedIO);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--Within the Agent job
EXECUTE AS Login = 'ResourceGovernerUser'
EXECUTE SomeSQLStuff
Best Answer
Resource governor classifier functions only run during the Login process. Impersonation via
EXECUTE AS
does not trigger the classifier function.EXECUTE AS
is how SQL Server Agent runs jobs in the context of another login or user.Specifying the
Run As ...
user or modifying theJob Owner
does not change how SQL Server Agent logs into SQL Server. The only way I can get SQL Server agent jobs to run in a specific resource group with a classifier function that looks only at the user name is to put the SQL Server Agent Service Account into it's own resource group.Since testing is a great way to understand what is really happening, I've created a small test-bed. Don't run this on a production system since it will modify the resource group configuration.
This creates the classifier function, and several resource groups attached to a test resource pool:
Here we'll create a login, and allow it to
VIEW SERVER STATE
so we can tell which resource group the session has been assigned to.This creates a SQL Server Agent job where the "owner" is set to the login we just created. Since the login is not a member of sysadmin, SQL Server agent will run this job in the context of that login.
The results from the msdb history table for the above job:
Next, we'll create a user for the login, and run a new version of the job using the
Run As
option in the advanced properties of the SQL Server Job Step.The output from that run of the job:
This cleans up the resource governor configuration, and drops the classifier function and the login.
In both result sets above, you can see the Resource Governor classifier function put each job into the
AgentServiceAccountGroup
group; this is because the SQL Server Agent initially logs into SQL Server using the Service Account used when SQL Server Agent starts up.