Microsoft® SQL Server® 2012 Bible

1137

Chapter 50: Resource Governor

50

map to the specifi ed user or application names, the function assigns them to the default

Workload group. The classifi er function then registers and the confi guration changes.

-- Store the classifier function in the master database.

USE master;

GO

SET ANSI_NULLS ON;

GO

SET QUOTED_IDENTIFIER ON;

GO

CREATE FUNCTION dbo.rgclassifier_v1() RETURNS sysname

WITH SCHEMABINDING

AS

BEGIN

-- Declare the variable to hold the value returned in sysname.

DECLARE @grp_name AS sysname

-- If the user login is 'sa', map the connection to the groupAdmin

-- workload group.

IF (SUSER_NAME() = 'sa')

SET @grp_name = 'groupAdmin'

-- Use application information to map the

--connection to the groupAdhoc

-- workload group.

ELSE IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%')

OR (APP_NAME() LIKE '%QUERY ANALYZER%')

SET @grp_name = 'groupAdhoc'

-- If the application is for reporting, map the connection to

-- the groupReports workload group.

ELSE IF (APP_NAME() LIKE '%REPORT SERVER%')

SET @grp_name = 'groupReports'

-- If the connection does not map to any of the previous groups,

-- put the connection into the default workload group.

ELSE

SET @grp_name = 'default'

RETURN @grp_name

END

GO

-- Register the classifier user-defined function and update the

-- the in-memory configuration.

ALTER RESOURCE GOVERNOR WITH

(CLASSIFIER_FUNCTION=dbo.rgclassifier_v1);

GO

ALTER RESOURCE GOVERNOR RECONFIGURE;

GO

The following example resets all workload group and pool statistics:

ALTER RESOURCE GOVERNOR RESET STATISTICS;

c50.indd 1137c50.indd 1137 7/31/2012 10:22:31 AM7/31/2012 10:22:31 AM

http://www.it-ebooks.info