January 27, 2017 at 2:47 pm
I'm trying to create a classifier function that will evaluate the user connection and if it is any login in a sysadmin role it will be unconstrained, if it's a normal user then it will be constrained by resource/workload group. It works just fine when evaluating anyone coming in using a server level login (Windows User/Group or SQL Login), but it's not evaluating properly for contained users. The testing code is below and the section in red is where it's not working. If anyone has any ideas please let me know.
ALTER RESOURCE POOL sm_user_queries
WITH (MAX_CPU_PERCENT = 10,
MAX_MEMORY_PERCENT = 30,
MAX_IOPS_PER_VOLUME = 100)
GO
CREATE RESOURCE POOL med_user_queries
WITH (MAX_CPU_PERCENT = 50,
MAX_MEMORY_PERCENT = 50,
MAX_IOPS_PER_VOLUME = 500)
GO
ALTER WORKLOAD GROUP sm_user_queries
WITH (IMPORTANCE = MEDIUM,
MAX_DOP = 4)
USING
"sm_user_queries"
GO
CREATE WORKLOAD GROUP med_user_queries
WITH (IMPORTANCE = MEDIUM,
MAX_DOP = 4)
USING
"med_user_queries"
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
ALTER RESOURCE GOVERNOR DISABLE
ALTER RESOURCE GOVERNOR RECONFIGURE
DROP FUNCTION fn_RGClassifierUsers
GO
CREATE FUNCTION fn_RGClassifierUsers() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name AS sysname
IF IS_SRVROLEMEMBER('sysadmin') = 1
BEGIN
SET @grp_name = 'default'
END
IF USER_NAME() = 'containeduser'
BEGIN
SET @grp_name = 'sm_user_queries'
END
IF (IS_SRVROLEMEMBER('sysadmin') <> 1 AND USER_NAME() <> 'containeduser')
BEGIN
SET @grp_name = 'med_user_queries'
END
RETURN @grp_name;
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_RGClassifierUsers);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
February 7, 2017 at 12:27 pm
GoldenDBA
If you are trying to use Resource Governor on a server with contained and non-contained databases, I would recommend using SUSER_NAME() and not USER_NAME(). Keep in mind that in non-contained databases you can have a database user that has a different name then the server principal and that contained databases don't have the concept of "database users" they have SQL Logins with passwords.
Simply changing USER_NAME to SUSER_NAME will solve your issue.
Thomas LiddleSQL Server AdministratorVideo Blog - YouTubeWeb Blog - www.thomasliddledba.comTwitter - @thomasliddledbaFacebook - @thomasliddledba
February 7, 2017 at 12:56 pm
thomasliddledba - Tuesday, February 7, 2017 12:27 PMGoldenDBA
If you are trying to use Resource Governor on a server with contained and non-contained databases, I would recommend using SUSER_NAME() and not USER_NAME(). Keep in mind that in non-contained databases you can have a database user that has a different name then the server principal and that contained databases don't have the concept of "database users" they have SQL Logins with passwords.Simply changing USER_NAME to SUSER_NAME will solve your issue.
Thanks for the confirmation. I actually just came back to working on this yesterday and did determine that I needed to use SUSER_NAME() and that my logic was just off a bit in the classifier function. It's working like a champ with the contained users now and I've been able to build the reporting we needed against the resulting perfmon data.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply