将资源调控器与SQL代理作业一起使用

Arun Gopinath 08/29/2017. 1 answers, 301 views
sql-server sql-server-2014 resource-governor

我正在尝试设置资源调控器以限制使用SQL代理作业运行的进程的IOPS。 分类器功能设置为标识特定登录(使用该登录运行的任何spid应使用分配给它的资源组)。 然后我在作业中添加了EXECUTE AS LOGIN = 'ResourceGovernerUser' ,但我无法使其工作并且它回退到默认池,因为作业是'EXECUTED AS 'ServiceAccount'' 。 虽然如果我使用sp_WhoisActive查看活动进程,登录名将显示为'ResourceGovernerUser'而不是服务帐户。 所以我修改了分类器功能以使用服务帐户,然后它才起作用。 我使用'Resource Pool Stats'对象下的perfmon计数器'Disk Read IO/Sec''Disk Write IO/Sec'验证了这一点。

问题是 - 如何让资源调控器使用服务帐户以外的登录(与代理作业一起使用时)? 我确实有一些未经检验的丑陋想法 - 使用CmdExec或PowerShell代理运行作业。 如果有人遇到类似的情况或有任何更好的想法,我会非常感激。 谢谢。

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

1 Answers


Max Vernon 08/30/2017.

资源调控器分类器功能仅在登录过程中运行。 通过EXECUTE AS模拟不会触发分类器功能。 EXECUTE AS是SQL Server代理在另一个登录或用户的上下文中运行作业的方式。

指定“ Run As ...用户或修改“ Job Owner ”不会更改SQL Server代理登录到SQL Server的方式。 我可以让SQL Server代理作业在具有仅查看用户名的分类器函数的特定资源组中运行的唯一方法是将SQL Server代理Service Account放入其自己的资源组。

由于测试是了解真实情况的好方法,因此我创建了一个小型试验台。 不要在生产系统上运行它,因为它将修改资源组配置。

这将创建分类器函数,以及附加到测试资源池的多个资源组:

USE master;
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @GroupName sysname = NULL;
    IF SUSER_SNAME() = 'DOMAIN\USER'
        SET @GroupName = N'AgentServiceAccountGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestLogin'
        SET @GroupName = N'ResourceGovernorTestLoginGroup';

    IF SUSER_SNAME() = 'ResourceGovernorTestUser'
        SET @GroupName = N'ResourceGovernorTestUserGroup';

    IF @GroupName IS NULL
        SET @GroupName =  N'default';

    RETURN @GroupName;
END
GO
CREATE RESOURCE POOL TestPool
WITH (MAX_CPU_PERCENT = 10); 
CREATE WORKLOAD GROUP AgentServiceAccountGroup 
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestLoginGroup 
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
CREATE WORKLOAD GROUP ResourceGovernorTestUserGroup 
WITH (
      group_max_requests=0
    , importance=Medium
    , request_max_cpu_time_sec=0
    , request_max_memory_grant_percent=25
    , request_memory_grant_timeout_sec=0
    , max_dop=0
    )
USING TestPool;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO 

在这里,我们将创建一个登录,并允许它VIEW SERVER STATE以便我们可以告诉会话已分配给哪个资源组。

CREATE LOGIN ResourceGovernorTestLogin
WITH PASSWORD = '!NnrtiHummusPlenumPoodle2'
    , DEFAULT_LANGUAGE = us_english
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF;
GRANT VIEW SERVER STATE TO ResourceGovernorTestLogin;
GO 

这将创建一个SQL Server代理作业,其中“owner”设置为我们刚刚创建的登录名。 由于登录不是sysadmin的成员,因此SQL Server代理将在该登录的上下文中运行此作业。

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job 
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';
GO 

上述作业的msdb历史记录表的结果:

  Job'TestResourceGovernorJob'成功启动。  以用户身份执行:ResourceGovernorTestLogin。   -    (1行受影响)  UserName SYSTEM_USER SESSION_USER ORIGINAL_LOGIN WorkloadGroup  ------------------------------ -------------------- ---------- ------------------------------ ---------- -------------------- ------------------------------  ResourceGovernorTestLogin ResourceGovernorTestLogin guest DOMAIN \ USER AgentServiceAccountGroup  (1行受影响)。 这一步成功了。 

接下来,我们将为登录创建一个用户,并使用SQL Server作业步骤的高级属性中的“ Run As选项运行新版本的作业。

CREATE USER ResourceGovernorTestUser
FOR LOGIN ResourceGovernorTestLogin;

DECLARE @JobID uniqueidentifier;
EXEC msdb.dbo.sp_add_job @job_name = 'TestResourceGovernorJob'
    , @enabled = 1
    , @description = 'Tests resource governor classification'
    , @start_step_id = 1
    , @owner_login_name = 'ResourceGovernorTestLogin'
    , @job_id = @JobID OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = 'Step1'
    , @subsystem = 'TSQL'
    , @database_name = 'master'
    , @database_user_name = 'ResourceGovernorTestUser'
    , @command = 'SELECT '''';
SELECT UserName = CONVERT(nvarchar(30), SUSER_SNAME())
    , [SYSTEM_USER] = CONVERT(nvarchar(30), SYSTEM_USER)
    , [SESSION_USER] = CONVERT(nvarchar(30), SESSION_USER)
    , [ORIGINAL_LOGIN] = CONVERT(nvarchar(30), ORIGINAL_LOGIN())
    , WorkloadGroup = CONVERT(nvarchar(30), wg.name)
FROM sys.dm_exec_requests der
    INNER JOIN sys.dm_resource_governor_workload_groups wg ON der.group_id = wg.group_id
WHERE der.session_id = @@SPID;'
    , @flags = 4 --write step output into msdb.dbo.sysjobstephistory
    , @on_success_action = 1;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(LOCAL)';
/*
    Call SQL Server Agent via msdb.dbo.sp_notify_job 
    which in turn calls msdb.dbo.xp_notify_job.
    The SQL Server Agent executable logs into the
    SQL Server instance using the service account,
    then performs an "EXECUTE AS ..." to run
    job step(s).
*/
EXEC msdb.dbo.sp_start_job @job_id = @JobID;
GO

WAITFOR DELAY N'00:00:01';
DECLARE @msg nvarchar(max);
SELECT @msg = sjh.message
FROM msdb.dbo.sysjobhistory sjh
    INNER JOIN msdb.dbo.sysjobs sj ON sjh.job_id = sj.job_id
WHERE sj.name = 'TestResourceGovernorJob'
    AND sjh.step_id = 1;
PRINT (N'');
PRINT (@msg);
PRINT (N'');
EXEC msdb.dbo.sp_delete_job @job_name = 'TestResourceGovernorJob';

DROP USER ResourceGovernorTestUser;
GO 

该作业运行的输出:

  Job'TestResourceGovernorJob'成功启动。  以用户身份执行:ResourceGovernorTestLogin。   -    (1行受影响)  UserName SYSTEM_USER SESSION_USER ORIGINAL_LOGIN WorkloadGroup  ------------------------------ -------------------- ---------- ------------------------------ ---------- -------------------- ------------------------------  ResourceGovernorTestLogin ResourceGovernorTestLogin ResourceGovernorTestUser DOMAIN \ USER AgentServiceAccountGroup  (1行受影响)。 这一步成功了。 

这将清除资源调控器配置,并删除分类器功能和登录。

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
DROP WORKLOAD GROUP AgentServiceAccountGroup;
DROP WORKLOAD GROUP ResourceGovernorTestUserGroup;
DROP WORKLOAD GROUP ResourceGovernorTestLoginGroup;
DROP RESOURCE POOL TestPool;
ALTER RESOURCE GOVERNOR RECONFIGURE;
DROP FUNCTION dbo.fnDummyClassifier
DROP LOGIN ResourceGovernorTestLogin;
GO 

在上面的两个结果集中,您可以看到Resource Governor分类器函数将每个作业放入AgentServiceAccountGroup组中; 这是因为SQL Server代理最初使用SQL Server代理启动时使用的服务帐户登录到SQL Server。


HighResolutionMusic.com - Download Hi-Res Songs

1 Alan Walker

Diamond Heart flac

Alan Walker. 2018. Writer: Alan Walker;Sophia Somajo;Mood Melodies;James Njie;Thomas Troelsen;Kristoffer Haugan;Edvard Normann;Anders Froen;Gunnar Greve;Yann Bargain;Victor Verpillat;Fredrik Borch Olsen.
2 Sia

I'm Still Here flac

Sia. 2018. Writer: Sia.
3 Cardi B

Taki Taki flac

Cardi B. 2018. Writer: Bava;Juan Vasquez;Vicente Saavedra;Jordan Thorpe;DJ Snake;Ozuna;Cardi B;Selena Gomez.
4 Little Mix

Woman Like Me flac

Little Mix. 2018. Writer: Nicki Minaj;Steve Mac;Ed Sheeran;Jess Glynne.
5 Halsey

Without Me flac

Halsey. 2018. Writer: Halsey;Delacey;Louis Bell;Amy Allen;Justin Timberlake;Timbaland;Scott Storch.
6 Lady Gaga

I'll Never Love Again flac

Lady Gaga. 2018. Writer: Benjamin Rice;Lady Gaga.
7 Bradley Cooper

Shallow flac

Bradley Cooper. 2018. Writer: Andrew Wyatt;Anthony Rossomando;Mark Ronson;Lady Gaga.
8 Bradley Cooper

Always Remember Us This Way flac

Bradley Cooper. 2018. Writer: Lady Gaga;Dave Cobb.
9 Kelsea Ballerini

This Feeling flac

Kelsea Ballerini. 2018. Writer: Andrew Taggart;Alex Pall;Emily Warren.
10 Mako

Rise flac

Mako. 2018. Writer: Riot Music Team;Mako;Justin Tranter.
11 Dewain Whitmore

Burn Out flac

Dewain Whitmore. 2018. Writer: Dewain Whitmore;Ilsey Juber;Emilio Behr;Martijn Garritsen.
12 Avril Lavigne

Head Above Water flac

Avril Lavigne. 2018. Writer: Stephan Moccio;Travis Clark;Avril Lavigne.
13 Khalid

Better flac

Khalid. 2018. Writer: Charlie Handsome;Jamil Chammas;Denis Kosiak;Tor Erik Hermansen;Mikkel Stoleer Eriksen;Khalid.
14 Lady Gaga

Look What I Found flac

Lady Gaga. 2018. Writer: DJ White Shadow;Nick Monson;Mark Nilan Jr;Lady Gaga.
15 Deep Chills

Run Free flac

Deep Chills. 2018.
16 Dynoro

In My Mind flac

Dynoro. 2018. Writer: Georgi Kay;Feenixpawl;Ivan Gough.
17 Charli XCX

1999 flac

Charli XCX. 2018. Writer: Charli XCX;Troye Sivan;Leland;Oscar Holter;Noonie Bao.
18 NCT 127

Regular (English Version) flac

NCT 127. 2018.
19 Lukas Graham

Love Someone flac

Lukas Graham. 2018. Writer: Don Stefano;Morten "Rissi" Ristorp;Morten "Pilo" Pilegaard;Jaramye Daniels;James Alan;David LaBrel;Lukas Forchhammer Graham.
20 Rita Ora

Let You Love Me flac

Rita Ora. 2018. Writer: Rita Ora.

Related questions

Hot questions

Language

Popular Tags