Resource Governor Starter Setup

A working Resource Governor setup: three workload groups (admin, SSMS/Query Analyzer, report server) and a classifier function that routes sessions into them based on the login name and application name. It finishes by registering the classifier, reconfiguring the governor, and querying the workload group DMV to verify. A good starting template for taming a shared server.

USE master
GO

CREATE WORKLOAD GROUP GroupSSMS;
CREATE WORKLOAD GROUP GroupReports;
CREATE WORKLOAD GROUP GroupAdmin;
GO

CREATE FUNCTION dbo.ResourceClassification_v1() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @grp_name sysname
      IF (UPPER(SUSER_NAME()) = 'SA')
          SET @grp_name = 'GroupAdmin'
      ELSE IF (UPPER(APP_NAME()) LIKE '%MANAGEMENT STUDIO%')
          OR (APP_NAME() LIKE '%QUERY ANALYZER%')
          SET @grp_name = 'GroupSSMS'
      ELSE IF (UPPER(APP_NAME()) LIKE '%REPORT SERVER%')
          SET @grp_name = 'GroupReports'
    RETURN @grp_name
END;
GO
-- Register the classifier function with Resource Governor
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.ResourceClassification_v1);
GO

-- Start Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

SELECT * FROM sys.dm_resource_governor_workload_groups
GO

--Reset statistics with:
--ALTER RESOURCE GOVERNOR RESET STATISTICS