-- Store the classifier function in master database.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION 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’ then 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 then 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 preceding groups,
-- put the connection in 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
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION 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’ then 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 then 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 preceding groups,
-- put the connection in 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