Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Resource governor always uses default Work Load Group Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 7:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 4:00 AM
Points: 127, Visits: 265
When I setup resource governor with the below script, Both two user uses default Workload Group?

Where do i do some mistake?


CREATE RESOURCE POOL PoolBO
WITH(
MAX_CPU_PERCENT=100,
MAX_MEMORY_PERCENT=100,
MIN_CPU_PERCENT=80,
MIN_MEMORY_PERCENT=80
);
GO
CREATE RESOURCE POOL PoolVIP
WITH(
MAX_CPU_PERCENT=20,
MAX_MEMORY_PERCENT=20,
MIN_CPU_PERCENT=0,
MIN_MEMORY_PERCENT=0
);
GO

CREATE WORKLOAD GROUP WorkloadGroupBO
USING PoolBO

CREATE WORKLOAD GROUP WorkloadGroupVIP
USING PoolVIP
GO

USE master
GO

-- create logins to separate users into different groups
-- note that we disabled strong password checking for demo purposes
-- but this is against any best practice
CREATE LOGIN UserMarketing WITH PASSWORD = 'UserMarketingPwd', CHECK_POLICY = OFF
CREATE LOGIN UserVIP WITH PASSWORD = 'UserVIPPwd', CHECK_POLICY = OFF

CREATE FUNCTION dbo.CLASSIFIER_V1()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'UserVIP')
SET @WorkloadGroup = 'WorkloadGroupVIP'
ELSE IF (SUSER_NAME() = 'UserMarketing')
SET @WorkloadGroup = 'WorkloadGroupBO'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Post #1410602
Posted Wednesday, January 23, 2013 9:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, October 16, 2014 6:18 PM
Points: 3,961, Visits: 7,170
I know this sounds weird, but try setting the classifier function = NULL beforehand, then assign it
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Then run:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION =dbo.CLASSIFIER_V1)
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

I've had a similar issue before, not sure if there's a bug in there or not


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1410683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse