How to Create Classifier Function in Resource Governor of SQL Server - SQL Server DBA Tutorial

In this video you will learn how to create Classifier Function in SQL Server Resource Governor, how to create resource pool, how to create workload, how to create schema binding functions and how to configure Workload and Resource pools with classifier function in order to take advantage of Resource Governor in SQL Server. It also shows step by step the functionality of Resource Governor once classifier function is created. It shows results set of this whole process.

Script to Create Classifier Function in Resource Governor of SQL Server

CREATE FUNCTION Resourcegclassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
  BEGIN
      DECLARE @WLGRP AS SYSNAME

      IF( Host_name() = 'TBSClient' )
        SET @WLGRP = 'ReportQueriesWG'
      ELSE IF ( Host_name() = 'TBSSQL' )
        SET @WLGRP = 'ExcelQueries'
      ELSE
        SET @WLGRP = 'default'

      RETURN @WLGRP
  END

GO 

-- Varify if Function Exist and enabled
SELECT *
FROM   sys.resource_governor_configuration

GO

SELECT Object_schema_name(classifier_function_id) AS [schema_name],
       Object_name(classifier_function_id)        AS [function_name]
FROM   sys.dm_resource_governor_configuration

-- Lets run queries from different servers 
SELECT s.group_id,
       Cast(g.name AS NVARCHAR(20)),
       s.session_id,
       s.login_time,
       Cast(s.host_name AS NVARCHAR(20)),
       Cast(s.program_name AS NVARCHAR(20))
FROM   sys.dm_exec_sessions s
       INNER JOIN sys.dm_resource_governor_workload_groups g
               ON g.group_id = s.group_id
ORDER  BY g.name

GO

 Create Classifier Function in Resource Governor of SQL Server - SQL Server DBA Tutorial