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.
