Resource Governor problem

  • Hey,

    I'm having a strange issue when using Resource Governor to assign users to a workload group based on their membership to a customer server role. If I add them individually everything works as expected, but if I add an AD group to the server role the classifier function doesn't assign the users to the desired workload group. Instead it just assigns them to the default one.

    Has anyone come across this?

    I'm just trying to avoid having to add multiple users (it could be a lot) to my custom server role.

  • Can you post the TSQL for your pools, groups and classifier?

  • Agree, need to see code. Not easy to guess here.

  • Sorry I should have included that from the outset. Below is the code I'm using:

    /****** Object: ResourcePool [pReadIntentAccess] Script Date: 3/07/2015 8:33:46 AM ******/

    CREATE RESOURCE POOL [pReadIntentAccess] WITH(min_cpu_percent=0,

    max_cpu_percent=10,

    min_memory_percent=0,

    max_memory_percent=10,

    cap_cpu_percent=100,

    AFFINITY SCHEDULER = AUTO

    )

    GO

    /****** Object: WorkloadGroup [gReadIntentAccess] Script Date: 3/07/2015 8:32:50 AM ******/

    CREATE WORKLOAD GROUP [gReadIntentAccess] 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 [pReadIntentAccess]

    GO

    CREATE FUNCTION [dbo].[fnReadIntentAccessClassifier]()

    RETURNS SYSNAME

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @WorkloadGroup SYSNAME

    DECLARE @WhoAmI SYSNAME = ORIGINAL_LOGIN()

    IF IS_SRVROLEMEMBER('ReadIntentAccess', @WhoAmi) = 1

    SET @WorkloadGroup = 'gReadIntentAccess'

    ELSE

    SET @WorkloadGroup = 'default'

    RETURN @WorkloadGroup

    END

    GO

    So the issue I'm having is if I add an AD group to the 'ReadIntentAccess' server role the individual users within that group aren't assigned to my custom workload group.

  • Ok I had some time to try replicate the issue: I created a server role called dataadmin. I then created a Windows group and put a login in there... called LOGINX which has the dataadmin permission role.

    CREATE FUNCTION [dbo].[fnClassifier1]()

    RETURNS SYSNAME

    WITH SCHEMABINDING

    AS

    BEGIN

    DECLARE @WorkloadGroup SYSNAME

    --DECLARE @WhoAmI SYSNAME = ORIGINAL_LOGIN()

    IF IS_SRVROLEMEMBER('dataadmin') = 1

    SET @WorkloadGroup = 'gAccess'

    ELSE

    SET @WorkloadGroup = 'default'

    RETURN @WorkloadGroup

    END

    GO

    I then start a new session and

    -- Run the below as LOGIN X

    SELECT * FROM dbo.blanktable

    see my attachment... it does go to the gAccess group when I login as LOGINX... Is that what you need?

  • Yeah that's what I need.

    I'll do some more testing so my function is similar to the one you tested.

  • That seems to work perfectly!

    Thanks for the help with that.

  • Chris-475469 (7/5/2015)


    That seems to work perfectly!

    Thanks for the help with that.

    you are very welcome!

  • Just to explain what was at issue with the original script, check out this from the documentation of IS_SRVROLEMEMBER:

    If a Windows login, such as Contoso\Mary5, is specified for login, IS_SRVROLEMEMBER returns NULL, unless the login has been granted or denied direct access to SQL Server.

    So, since those logins had not been granted direct access, IS_SRVROLEMEMBER was returning NULL when you passed them in as the login parameter. In the revised script, you are not passing a login parameter, and as the above link goes on to explain, then it is able to resolve indirect memberships.

    Cheers!

Viewing 9 posts - 1 through 8 (of 8 total)

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