Resource governor always uses default Work Load Group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Resource governor always uses default Work...

Resource governor always uses default Work Load Group

Rate Topic

Display Mode

Topic Options

Author

Message

Thinky Night

Posted Wednesday, January 23, 2013 7:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:31 AM
Points: 123, Visits: 218

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

MyDoggieJessie

Posted Wednesday, January 23, 2013 9:40 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,037, Visits: 3,761

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" Wink

Post #1410683

« Prev Topic | Next Topic »

Permissions