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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy