January 23, 2013 at 7:58 am
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
January 23, 2013 at 9:40 am
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; Theyll drag you down to their level and beat you with experience
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply