Resource Governor Classification
It is the job of the Resource Governor classification to allocates incoming sessions to a workload group based on session properties. The Resource Governor Classification is one of the most important parts of Resource Governor, is a user-defined function (UDF) which returns the name of the pool where the incoming request will be directed to.
The classifier function resides in the master database, and although you can have multiple classifier functions residing in the master database, they cannot be used concurrently. Because of this the classifier function is the biggest bottleneck in Resource Governor as all queries and tasks that are not internal must be classified by this UDF.
The below diagram displays how the classification function sits within the Resource Governor’s Basic Flow.
Note in the diagram how the Classifier function has no effect on the internal pool.
The process step by step
- When a client attempts to log on to SQL Server, a user session is established with the SQL instance
- Once the authentication phase of the log-in finishes the logon trigger fires establishing the user session
- The classification is then attempted. You can use one of the following system functions to get the details of the query or task so that it can be directed to the right pool:
HOST_NAME(), APP_NAME(), SUSER_NAME(), SUSER_SNAME(), IS_SRVROLEMEMBER(), or IS_MEMBER().
- The UDF can also use lookup tables to configure the request to the proper pool.
Below is an example of creating classifier functions by customising the classification logic such as creating two resource pools, poolA and poolB and creating similarly named workload groups associated with these resource pools.
After these resource pools and workload groups are created, we then create a table by which to define the times to be used, allocate sessions that run during defined periods.
Below is Transact-SQL code to create the table
USE master GO <code>CREATE TABLE poolA_Or_poolB</code> ( GroupName <code>sysname</code> not null, <code>StartTime time not null,</code> <code>EndTime time not null,</code> )
Once completed we insert the values that the classifier will use when assigning a workload group to a particular session. using a simple insert statement
<code>INSERT into</code> <code>poolA_Or_poolB</code> <code>VALUES('poolA', '6:00 AM', '6:00 PM')</code> GO
Now in our table we will have in our table columns the following values:
sysname = 'poolA' StartTime = '6:00 AM' EndTime = '6:00 PM'
Creating the classifier function that will use the information in the table to determine the appropriate workload group. The code for an example function is as follows:
<code>CREATE FUNCTION DayNightClassifier()</code> <code>RETURNS sysname</code> <code>WITH SCHEMABINDING</code> <code>AS</code> <code>BEGIN</code> <code>DECLARE</code> <code>@nameGroup</code> <code>sysname</code> DECLARE <code>@sessionTime</code> time SET <code>@sessionTime</code> = <code>CONVERT</code>(time,<code>GETDATE()</code>) SELECT TOP 1 <code>@nameGroup = GroupName</code> FROM <code>dbo.poolA_Or_poolB</code> <code> WHERE StartTime = @sessionTime </code> IF (@nameGroup is not null) <code>BEGIN</code> RETURN <code>@nameGroup</code> <code>END</code> <code>--- Use default workgroup if no match is found</code> <code>RETURN N'Default'</code> <code>END</code> GO
After you have created the classifier function, register it and then update the in-memory configuration. You can do this for the preceding example by using the following Transact-SQL code:
<code>ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.poolA_Or_poolB Classifier)</code> <code>ALTER RESOURCE GOVERNOR RECONFIGURE</code> GO
MSDN has more information on the Resource Governor Classification click here