August 12, 2009 at 2:32 pm
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.
After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
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 s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
(MAX_CPU_PERCENT = 20
MAX_MEMORY_PERCENT = 20);
GO
CREATE RESOURCE POOL pool_DBNAME
WITH
(MAX_CPU_PERCENT = 30
MAX_MEMORY_PERCENT = 30);
GO
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
GO
Create FUNCTION [dbo].[rgclassifier_v1]() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname
IF (SUSER_NAME() = 'user_login')
SET @grp_name = 'GroupLogin'
ELSE IF (DB_NAME() = 'DBNAME')
SET @grp_name = 'GroupDBNAME'
RETURN @grp_name
END;
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
August 12, 2009 at 3:46 pm
You can't use DB_NAME() in the classifier function, since classification happens after login but NOT before assigning database (or determining default), user options, etc. As a workaround you could look this up yourself, if you are using default database mappings and properly naming groups according to the default database name. If you are relying on the database in the connection string, I am afraid you are out of luck. Here is an example that demonstrates using the default database, it requires you maintain your own copy of sys.server_principals because you can not schemabind to a system object (or a view to it, or a synonym).
USE [master];
GO
CREATE DATABASE DBNAME;
GO
CREATE LOGIN DBNAMEA WITH PASSWORD = 'foo', CHECK_POLICY = OFF, DEFAULT_DATABASE = DBNAME;
CREATE LOGIN DBNAMEB WITH PASSWORD = 'foo', CHECK_POLICY = OFF, DEFAULT_DATABASE = [master];
GO
USE DBNAME;
GO
CREATE USER DBNAMEA FOR LOGIN DBNAMEA;
CREATE USER DBNAMEB FOR LOGIN DBNAMEB;
GO
USE [master];
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- do this once:
IF OBJECT_ID('dbo.principals') IS NULL
SELECT *
INTO dbo.principals
FROM sys.server_principals;
-- end one time task
-- set this up in a scheduled job that runs frequently enough
-- depending on your rate of creating / dropping logins:
BEGIN TRANSACTION;
TRUNCATE TABLE dbo.principals;
INSERT dbo.principals SELECT * FROM sys.server_principals;
COMMIT TRANSACTION;
-- end job
GO
ALTER FUNCTION [dbo].[rgclassifier_v1]()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grp_name sysname;
IF (SUSER_NAME() = 'user_login')
SET @grp_name = 'GroupLogin'
ELSE
SELECT @grp_name = 'Group' + default_database_name
FROM dbo.principals
WHERE [name] = SUSER_NAME();
RETURN @grp_name;
END;
GO
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- connect as DBNameA and DBNameB, then compare:
SELECT session_id, login_name, group_id
FROM sys.dm_exec_sessions
WHERE login_name LIKE 'DBNAME[A-B]';
August 14, 2009 at 7:07 am
Thanks Aaron.
In our environment, we use an application to register database logins automatically from Active Directory.
And assign databases' roles to each logins.
It is hard to assign default database to each login at this point.
Any other way can approch ?
September 2, 2009 at 5:53 am
use the ORIGINAL_DB_NAME () feature in your classifier function
MVDBA
October 8, 2009 at 7:18 am
Hello scui,
Not sure if you found or developed a solution to this but here is what we are doing to accomplish this:
Taken from this doc: http://download.microsoft.com/download/d/1/f/d1f05c2e-0741-442d-859e-9417b377fdfa/wp-Using%20Resource%20Governor%20in%20a%20Shared%20Hosting%20Environment.zip
Creating the Classifier Function
The classifier function’s job is to map incoming connections to a workload group. In order to not have to continually update the classifier function as new databases are brought online, the following classifier function and control table are suggested. The control table, located in the Master database, holds the current list of database to workload group pairings:
CREATE TABLE [dbo].[WorkloadGroups](
[DatabaseName] [varchar](15) NOT NULL,
[WorkloadGroup] [varchar](15) NOT NULL,
CONSTRAINT [PK_WorkloadGroups] PRIMARY KEY CLUSTERED
(
[DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The classifier function first checks to see if the current user is in one of the built-in server roles then uses the control table to determine the workload group:
CREATE FUNCTION [dbo].[fnDatabaseClassifier]()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @strGroup sysname
DECLARE @db sysname
IF IS_SRVROLEMEMBER ('sysadmin') = 1 or IS_SRVROLEMEMBER('serveradmin') = 1
OR IS_SRVROLEMEMBER('securityadmin') = 1 or IS_SRVROLEMEMBER('processadmin') = 1
OR IS_SRVROLEMEMBER('diskadmin') = 1
BEGIN
SELECT @strGroup = 'Admin_Pool_WG';
END
ELSE
BEGIN
SELECT @db = ORIGINAL_DB_NAME();
IF (@db = '')
BEGIN
SELECT @db = CONVERT(nvarchar, LOGINPROPERTY(SUSER_NAME(), 'DefaultDatabase'));
END
SELECT @strGroup = WorkloadGroup
FROM dbo.WorkloadGroups
WHERE DatabaseName = @db
END
RETURN @strGroup
END
The model is to have a workload group for each database and use either the database specified on the connection string or falling back to the user’s default database, if no database was specified.
Populating the Control Table
As databases are created, add them to the control table, assigning them to a workload group.
Enabling Resource Governor
Use the following command to enable the resource governor using the classifier function as described previously:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fnDatabaseClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
This worked great for us, Good luck!
Paul Cortes
Technical Support Representative
http://www.ServerIntellect.com
Managed Servers, 24x7 U.S. Support, Web Hosting Solutions.
November 4, 2012 at 12:09 pm
For those who happen to search for a solution and read this post, here it is (the example applies for two databases):
CREATE function [dbo].[CLASSIFIER]()
returns sysname with schemabinding
begin
declare @val sysname
set @val='default'
if ORIGINAL_DB_NAME() in ('your_databse_name1','your_database_name2')
set @val = your_workgroup_name
return @val
end
GO
June 30, 2015 at 8:20 am
Hi
I got the same situation where in I am creating workload groups based on database name. I will be using table to store the loginnames, database names and workload group names and classify to a WL group based on the default database or original database name. What if the login name is active directory group? My table has active directory group name listed as login name and when I query suser_name() or suser_sname(), it returns the user name who is member of the active directory group but doesn't return active directory group name. Any trick to find out that user belongs to a particular group name?
Thanks
Vicky
April 28, 2016 at 10:05 am
You can use the is_member('AD GROUP NAME') function to determine AD group membership.
October 24, 2017 at 11:35 am
ServerIntellect_PC - Thursday, October 8, 2009 7:18 AMHello scui,Not sure if you found or developed a solution to this but here is what we are doing to accomplish this:Taken from this doc: http://download.microsoft.com/download/d/1/f/d1f05c2e-0741-442d-859e-9417b377fdfa/wp-Using%20Resource%20Governor%20in%20a%20Shared%20Hosting%20Environment.zipCreating the Classifier FunctionThe classifier function’s job is to map incoming connections to a workload group. In order to not have to continually update the classifier function as new databases are brought online, the following classifier function and control table are suggested. The control table, located in the Master database, holds the current list of database to workload group pairings:CREATE TABLE [dbo].[WorkloadGroups]([DatabaseName] [varchar](15) NOT NULL,[WorkloadGroup] [varchar](15) NOT NULL, CONSTRAINT [PK_WorkloadGroups] PRIMARY KEY CLUSTERED ([DatabaseName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]The classifier function first checks to see if the current user is in one of the built-in server roles then uses the control table to determine the workload group:CREATE FUNCTION [dbo].[fnDatabaseClassifier]()RETURNS sysnameWITH SCHEMABINDINGASBEGINDECLARE @strGroup sysnameDECLARE @db sysnameIF IS_SRVROLEMEMBER ('sysadmin') = 1 or IS_SRVROLEMEMBER('serveradmin') = 1 OR IS_SRVROLEMEMBER('securityadmin') = 1 or IS_SRVROLEMEMBER('processadmin') = 1OR IS_SRVROLEMEMBER('diskadmin') = 1BEGINSELECT @strGroup = 'Admin_Pool_WG';ENDELSEBEGINSELECT @db = ORIGINAL_DB_NAME();IF (@db = '') BEGINSELECT @db = CONVERT(nvarchar, LOGINPROPERTY(SUSER_NAME(), 'DefaultDatabase'));ENDSELECT @strGroup = WorkloadGroupFROM dbo.WorkloadGroupsWHERE DatabaseName = @db ENDRETURN @strGroupENDThe model is to have a workload group for each database and use either the database specified on the connection string or falling back to the user’s default database, if no database was specified.Populating the Control TableAs databases are created, add them to the control table, assigning them to a workload group.Enabling Resource GovernorUse the following command to enable the resource governor using the classifier function as described previously:ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fnDatabaseClassifier);GOALTER RESOURCE GOVERNOR RECONFIGURE;GOThis worked great for us, Good luck!
Hi,
How to implement on AlwaysOn Availability groups
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply