May 26, 2022 at 1:09 pm
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;
May 26, 2022 at 4:41 pm
IF (SUSER_NAME() in ( 'DOMANINA\GMSA_999SQLAgt$', 'user', 'user3', 'user4'))
this is basic sql
May 26, 2022 at 5:03 pm
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