Share via


How to: Create a Classifier User-Defined Function (Transact-SQL)

This topic shows how to create and test a classifier user-defined function (UDF). The steps involve executing Transact-SQL statements in the SQL Server Management Studio Query Editor. We recommend that you read Considerations for a Writing a Classifier Function before continuing.

The example shown in the following procedure illustrates the possibilities for creating a fairly complex classifier user-defined function.

In our example:

  • A resource pool (pProductionProcessing) and workload group (gProductionProcessing) are created for production processing during a specified time range.

  • A resource pool (pOffHoursProcessing) and workload group (gOffHoursProcessing) are created for handling connections that do not meet the requirements for production processing.

  • A table (TblClassificationTimeTable) is created in master to hold start and end times that can be evaluated against a login time. This must be created in master because Resource Governor uses schema binding for classifier functions.

    Note

    As a best practice, you should not store large, frequently updated tables in master.

As noted in Considerations for Writing a Classifier Function, the classifier function extends the login time. An overly complex function can cause logins to time out or slow down fast connections.

To create the classifier user-defined function

  1. Create and configure the new resource pools and workload groups. Assign each workload group to the appropriate resource pool.

    --- Create a resource pool for production processing
    --- and set limits.
    USE master
    GO
    CREATE RESOURCE POOL pProductionProcessing
    WITH
    (
         MAX_CPU_PERCENT = 100,
         MIN_CPU_PERCENT = 50
    )
    GO
    --- Create a workload group for production processing
    --- and configure the relative importance.
    CREATE WORKLOAD GROUP gProductionProcessing
    WITH
    (
         IMPORTANCE = MEDIUM
    )
    --- Assign the workload group to the production processing
    --- resource pool.
    USING pProductionProcessing
    GO
    --- Create a resource pool for off-hours processing
    --- and set limits.
    
    CREATE RESOURCE POOL pOffHoursProcessing
    WITH
    (
         MAX_CPU_PERCENT = 50,
         MIN_CPU_PERCENT = 0
    )
    GO
    --- Create a workload group for off-hours processing
    --- and configure the relative importance.
    CREATE WORKLOAD GROUP gOffHoursProcessing
    WITH
    (
         IMPORTANCE = LOW
    )
    --- Assign the workload group to the off-hours processing
    --- resource pool.
    USING pOffHoursProcessing
    GO
    
  2. Update the in-memory configuration.

    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    
  3. Create a table and define the start and end times for the production processing time range.

    USE master
    GO
    CREATE TABLE tblClassificationTimeTable
    (
         strGroupName     sysname          not null,
         tStartTime       time              not null,
         tEndTime         time              not null
    )
    GO
    --- Add time values that the classifier will use to
    --- determine the workload group for a session.
    INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM')
    go
    
  4. Create the classifier function that uses time functions and values that can be evaluated against the times in the lookup table.

    Note

    SQL Server 2008 introduces an expanded set of date and time data types and functions. For more information, see Date and Time Functions (Transact-SQL).

    CREATE FUNCTION fnTimeClassifier()
    RETURNS sysname
    WITH SCHEMABINDING
    AS
    BEGIN
         DECLARE @strGroup sysname
         DECLARE @loginTime time
         SET @loginTime = CONVERT(time,GETDATE())
         SELECT TOP 1 @strGroup = strGroupName
              FROM dbo.tblClassificationTimeTable
              WHERE tStartTime <= @loginTime and tEndTime >= @loginTime
         IF(@strGroup is not null)
         BEGIN
              RETURN @strGroup
         END
    --- Use the default workload group if there is no match
    --- on the lookup.
         RETURN N'gOffHoursProcessing'
    END
    GO
    
  5. Register the classifier function and update the in-memory configuration.

    ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier)
    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    

To verify the resource pools, workload groups, and the classifier user-defined function

  1. Obtain the resource pool and workload group configuration by using the following query.

    USE master
    SELECT * FROM sys.resource_governor_resource_pools
    SELECT * FROM sys.resource_governor_workload_groups
    GO
    
  2. Verify that the classifier function exists and is enabled by using the following queries.

    --- Get the classifier function Id and state (enabled).
    SELECT * FROM sys.resource_governor_configuration
    GO
    --- Get the classifer function name and the name of the schema
    --- that it is bound to.
    SELECT 
          object_schema_name(classifier_function_id) AS [schema_name],
          object_name(classifier_function_id) AS [function_name]
    FROM sys.dm_resource_governor_configuration
    
  3. Obtain the current runtime data for the resource pools and workload groups by using the following query.

    SELECT * FROM sys.dm_resource_governor_resource_pools
    SELECT * FROM sys.dm_resource_governor_workload_groups
    GO
    
  4. Find out what sessions are in each group by using the following query.

    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
    
  5. Find out which requests are in each group by using the following query.

    SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
               FROM sys.dm_exec_requests r
         INNER JOIN sys.dm_resource_governor_workload_groups g
                ON g.group_id = r.group_id
         CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    ORDER BY g.name
    GO
    
  6. Find out what requests are running in the classifier by using the following query.

    SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name 
               FROM sys.dm_exec_sessions s
         INNER JOIN sys.dm_resource_governor_workload_groups g
               ON g.group_id = s.group_id
                     AND 'preconnect' = s.status
    ORDER BY g.name
    GO
     
    SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text 
               FROM sys.dm_exec_requests r
         INNER JOIN sys.dm_resource_governor_workload_groups g
               ON g.group_id = r.group_id
                     AND 'preconnect' = r.status
         CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    ORDER BY g.name
    GO