Início / dba / Perguntas / 184625
Accepted
Arun Gopinath
Asked: 2017-08-30 12:09:52 +0800 CST

Usando o Resource Governor com trabalhos do SQL Agent

  • 772

Estou tentando configurar o Resource Governor para limitar IOPS para processos em execução usando trabalhos de agente SQL. A função do classificador é definida para identificar um login específico (qualquer spid que esteja sendo executado usando esse login deve usar o grupo de recursos ao qual está atribuído). Em seguida, adicionei EXECUTE AS LOGIN = 'ResourceGovernerUser'no trabalho, mas não consigo fazê-lo funcionar e ele volta para o pool padrão porque os trabalhos são 'EXECUTED AS 'ServiceAccount''. Embora se eu olhar para os processos ativos usando sp_WhoisActive, o nome de login é exibido como 'ResourceGovernerUser'em vez da conta de serviço. Então modifiquei a função do classificador para usar a conta de serviço e funcionou. Eu verifiquei isso usando os contadores perfmon 'Disk Read IO/Sec'e 'Disk Write IO/Sec'sob 'Resource Pool Stats'object.

A questão é - como faço para que o Administrador de Recursos use um login diferente da Conta de Serviço (quando usado com trabalhos de agente)? Eu tenho algumas ideias feias não testadas, como - executar os trabalhos usando um proxy CmdExec ou PowerShell. Se alguém se deparou com uma situação semelhante ou tiver alguma idéia melhor, eu realmente aprecio isso. Obrigada.

    --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
sql-server sql-server-2014

1 respostas

  1. Best Answer

    As funções do classificador do administrador de recursos são executadas apenas durante o processo de Login. A representação via EXECUTE ASnão aciona a função de classificador. EXECUTE ASé como o SQL Server Agent executa trabalhos no contexto de outro logon ou usuário.

    Especificar o Run As ...usuário ou modificar o Job Ownernão altera como o SQL Server Agent efetua login no SQL Server. A única maneira de fazer com que os trabalhos do agente do SQL Server sejam executados em um grupo de recursos específico com uma função de classificador que analisa apenas o nome do usuário é colocar a conta de serviço do SQL Server Agent em seu próprio grupo de recursos.

    Como os testes são uma ótima maneira de entender o que realmente está acontecendo, criei um pequeno banco de testes. Não execute isso em um sistema de produção, pois isso modificará a configuração do grupo de recursos.

    Isso cria a função de classificador e vários grupos de recursos anexados a um pool de recursos de teste:

    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

    Aqui, criaremos um login e permitiremos que VIEW SERVER STATEpossamos dizer a qual grupo de recursos a sessão foi atribuída.

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

    Isso cria um trabalho do SQL Server Agent em que o "proprietário" é definido para o logon que acabamos de criar. Como o logon não é membro do sysadmin, o agente do SQL Server executará esse trabalho no contexto desse logon.

    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

    Os resultados da tabela de histórico do msdb para o trabalho acima:

    O trabalho 'TestResourceGovernorJob' foi iniciado com sucesso.

Executado como usuário: ResourceGovernorTestLogin.
-

(1 linha(s) afetada)
Nome de usuário SYSTEM_USER SESSION_USER ORIGINAL_LOGIN WorkloadGroup
------------------------------ -------------------- ---------- ------------------------------ ---------- -------------------- ------------------------------
ResourceGovernorTestLogin ResourceGovernorTestLogin convidado DOMAIN\USER AgentServiceAccountGroup
(1 linha(s) afetada). O passo deu certo.

    Em seguida, criaremos um usuário para o logon e executaremos uma nova versão do trabalho usando a Run Asopção nas propriedades avançadas da Etapa de Trabalho do SQL Server.

    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

    A saída dessa execução do trabalho:

    O trabalho 'TestResourceGovernorJob' foi iniciado com sucesso.

Executado como usuário: ResourceGovernorTestLogin.
-

(1 linha(s) afetada)
Nome de usuário SYSTEM_USER SESSION_USER ORIGINAL_LOGIN WorkloadGroup
------------------------------ -------------------- ---------- ------------------------------ ---------- -------------------- ------------------------------
ResourceGovernorTestLogin ResourceGovernorTestLogin ResourceGovernorTestUser DOMAIN\USER AgentServiceAccountGroup
(1 linha(s) afetada). O passo deu certo.

    Isso limpa a configuração do administrador de recursos e elimina a função de classificador e o login.

    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

    Em ambos os conjuntos de resultados acima, você pode ver a função classificadora do Resource Governor colocar cada trabalho no AgentServiceAccountGroupgrupo; isso ocorre porque o SQL Server Agent inicialmente faz logon no SQL Server usando a conta de serviço usada quando o SQL Server Agent é inicializado.

    • 6

relate perguntas