Resource Governor for all users in a group

It was a hard part initially to make use of balancing the resources for all users with different groups. We can make use of IS_MEMBER() function, but the Function Classifier need to be derived accordingly. Also this is not an 100% workout to get the ideal result. I have recently come out with good work around to sort out this issue.

To give a scenario, I have 3 sets of SQL users. Moreover I also have replication stuff going on.
1. Business Team with developers knowledge
2. Business team with basic knowledge + pivot table manipulators in excel
3. IT team

I need to balance the users login on one side & the replication on the other.

I have used 3 Workload groups & Resouce pools on top of the server's default & internal pools.

--**CREATE RESOURCE POOL **--
CREATE RESOURCE POOL SSUsers
WITH (MAX_CPU_PERCENT = 2, MAX_MEMORY_PERCENT = 2);
go

CREATE RESOURCE POOL DevUsers
WITH (MAX_CPU_PERCENT = 4, MAX_MEMORY_PERCENT = 4);
go

CREATE RESOURCE POOL Distribution
WITH (MIN_CPU_PERCENT = 15, MAX_CPU_PERCENT = 40, MIN_MEMORY_PERCENT = 40, MAX_MEMORY_PERCENT = 60);
go

--to reconfigure the created resource pools, i used the following
ALTER RESOURCE GOVERNOR RECONFIGURE;
go

--the below query displays the newly created resource pools with its ID.
SELECT * FROM sys.dm_resource_governor_resource_pools;
go

--**CREATE WORKLOAD GROUP **--
--create workload group to assign to the resource pools

CREATE WORKLOAD GROUP WG_SSUsers
USING SSUsers;
go

CREATE WORKLOAD GROUP WG_DevUsers
USING DevUsers;
go

CREATE WORKLOAD GROUP WG_Distribution
USING distribution;
GO

--to reconfigure the created resource pools, i used the following
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

--the below query displays the newly created workload groups with its ID.
SELECT * FROM sys.dm_resource_governor_workload_groups


I am going to write a classifier function that will distinguish the users and the replication database and allocated the workload groups accordingly.

Before creating the classifier function I need to have all the list of users underneath their group level. In our case, I had issue in using IS_MEMBER() function where it was able to allocate resource for the first set of rules defined in the classifier function. Then I came out with adding the list of user to a physical table with their permissions level in the master database. You can execute the following against your master database.

Use master
go

CREATE TABLE RG_Users_Group
(
RG_Name NVARCHAR(15) NULL,
Username NVARCHAR(25) NULL

)

INSERT INTO rg_users_group VALUES ('SS_Users', 'AB01\user1')
INSERT INTO rg_users_group VALUES ('SS_Users',  'AB01\user2')
INSERT INTO rg_users_group VALUES ('SS_Users',  'AB01\user3')
INSERT INTO rg_users_group VALUES ('SS_Users',  'AB01\user4')
INSERT INTO rg_users_group VALUES ('Dev_Users',  'AB01\user5')
INSERT INTO rg_users_group VALUES ('Dev_Users',  'AB01\user6')


This creates a list of all users with proper Resource Group name. The output of the table looks like this.

RG_Name                       Username
------------------------------
SS_Users                         AB01\user1
SS_Users                        AB01\user2
SS_Users                        AB01\user3
SS_Users                        AB01\user4
Dev_Users                    AB01\user5
Dev_Users                    AB01\user6

Once the table is created then I write my classifier function here.
--create the classifier function to assign the workload groups
IF OBJECT_ID ( 'dbo.RGov_Classifier') IS NOT NULL
 DROP FUNCTION dbo.RGov_Classifier;
go
CREATE FUNCTION dbo.RGov_Classifier ()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
 RETURN
 (
  SELECT CASE WHEN SUSER_NAME() IN (SELECT username FROM dbo.rg_users_group WHERE RG_name = 'SS_Users')
      THEN N'WG_SSUsers'
     WHEN SUSER_NAME() IN (SELECT username FROM dbo.rg_users_group WHERE RG_name = 'Dev_Users')
      THEN N'WG_DevUsers'
     WHEN ORIGINAL_DB_NAME () = 'distribution'
      THEN N'WG_Distribution'
    ELSE N'default'
    END
 );
END;
go

--register the classifier function to the RG.
ALTER RESOURCE GOVERNOR
 WITH (CLASSIFIER_FUNCTION = dbo.RGov_Classifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now the creation of all resource pools, workload groups, table & classifier function is done. It is time to check how the resource group works. So I have the following query to check each resource pools & their average response in milliseconds.

USE MASTER;
GO
SELECT
             RP.name as PoolName,
             COALESCE(SUM(RG.total_request_count), 0)as Total_Request,
             COALESCE(SUM(RG.total_cpu_usage_ms), 0)as Total_CPU_in_MS,
             CASE
                   WHEN SUM(RG.total_request_count) > 0 THEN
                         SUM(RG.total_cpu_usage_ms) /SUM(RG.total_request_count)
                         ELSE
                         0
                  END as Avg_CPU_in_MS
       FROM
       sys.dm_resource_governor_resource_pools AS RP
       LEFT OUTER JOIN
       sys.dm_resource_governor_workload_groups AS RG
       ON
           RP.pool_id = RG.pool_id
       GROUP BY
           RP.name;

In the above query, I have used the DMV (Dynamic Management Views) to help in identifying the resources used for each pool.

PoolName         Total_Request                Total_CPU_in_MS      Avg_CPU_in_MS
DevUsers             3644                                       13412519                              3680
SSUsers                50                                           243846                                  4876
distribution        1340523                                16428325                             12
default                  621057                                  822676774                           1324
internal                0                                             3182271                                0

In the above scenario, I made sure all the resource pool now works fine.

This is not an 100% solution, and still have some work around to better up the results. But still a good work around compared to the other scenario.

Let me know if you need any clarifications on this.