Sunday, November 15, 2009

SQL 2008: Modifying Resource Governor's workload classification

How to change Resource Governor Classifier function on the fly...
Here's the TSQL ...

-- 1) Disable Resource Governor
ALTER RESOURCE GOVERNOR  DISABLE 
GO

-- 2) Remove the classifier function from Resource Governor so that you can change it
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = NULL)
GO

-- 3) Change the function itself
ALTER FUNCTION ResourceGovClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
DECLARE @classification VARCHAR(32)

IF SUSER_SNAME() LIKE 'Dev_%'
SET @classification = 'Workload_Developer'

IF SUSER_SNAME() LIKE 'Adm_%'
SET @classification = 'Workload_DBA'

IF SUSER_SNAME() = 'applogin' 
SET @classification = 'Workload_App'


RETURN @classification 
END
GO

-- 4)  Set Resource Governor to use the revised function
ALTER RESOURCE GOVERNOR
WITH ( CLASSIFIER_FUNCTION = dbo.ResourceGovClassifier)
GO

-- 5) Re-enable Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

No comments: