SQLserver Resource Governor - How to set multiple users to the same work group

  • HI

    Just wondering how to set up multiple users to the same work group within the function the resource governor uses.

    My current function looks like this :

    CREATE FUNCTION [dbo].[RG_Classifier]() RETURNS sysname

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @grp_name sysname

    IF (SUSER_NAME() = 'DOMANINA\GMSA_999SQLAgt$')

    SET @grp_name = 'GroupA'

    RETURN @grp_name

    END;

    GO

    If I want to add more users to that same group how do I do it ?  do I need to put else if  ?

     

    CREATE FUNCTION [dbo].[RG_Classifier]() RETURNS sysname

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @grp_name sysname

    IF (SUSER_NAME() = 'DOMANINA\GMSA_999SQLAgt$')

    SET @grp_name = 'GroupA'

    IF (SUSER_NAME() = 'DOMANINA\B900')

    SET @grp_name = 'GroupA'

    IF (SUSER_NAME() = 'DOMANINA\Z9002')

    SET @grp_name = 'GroupA'

    RETURN @grp_name

    END;

    GO

     

     

    And any tips / scripts for checking things are working as expected ?

     

    I have the following :

     

    SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time,

    CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))

    FROM sys.dm_exec_sessions AS s

    INNER JOIN sys.dm_resource_governor_workload_groups AS g

    ON g.group_id = s.group_id

    where s.group_id not in('2','1')

    ORDER BY s.login_time desc;

     

    SELECT

    rpool.name as PoolName,

    COALESCE(SUM(rgroup.total_request_count), 0) as TotalRequest,

    COALESCE(SUM(rgroup.total_cpu_usage_ms), 0) as TotalCPUinMS,

    CASE

    WHEN SUM(rgroup.total_request_count) > 0 THEN

    SUM(rgroup.total_cpu_usage_ms) / SUM(rgroup.total_request_count)

    ELSE

    0

    END as AvgCPUinMS

    FROM

    sys.dm_resource_governor_resource_pools AS rpool

    LEFT OUTER JOIN

    sys.dm_resource_governor_workload_groups AS rgroup

    ON

    rpool.pool_id = rgroup.pool_id

    GROUP BY

    rpool.name;

     

     

     

     

     

     

  • IF (SUSER_NAME() in ( 'DOMANINA\GMSA_999SQLAgt$', 'user', 'user3', 'user4'))

    this is basic sql

  • Yeah thanks I tried that but it didnt seem to be sending the other user to the resource pool, this could be because the job in question runs under a proxy account. Is it possible to add proxies as well do you know ?

     

    And is there any way to monitor this historically ? .ie verify that the specified users are using the pool designated to them?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply