Muokkaa

Jaa

Create and Test a Classifier User-Defined Function

  • Artikkeli

Applies to: SQL Server Azure SQL Managed Instance

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.

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.

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. For information about using Lookup Tables in a classifier function, see "Best practices for using Lookup Tables in a classifier function" in this topic.

    Note

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

    CREATE FUNCTION fnTimeClassifier()  
RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
/* We recommend running the classifier function code under 
snapshot isolation level OR using NOLOCK hint to avoid blocking on 
lookup table. In this example, we are using NOLOCK hint. */
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable WITH(NOLOCK)
          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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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

Best practices for using Lookup Tables in a classifier function

  1. Do not use a lookup table unless it is absolutely necessary. If you need to use a lookup table, it can be hard-coded into the function itself; however, this needs to be balanced with the complexity and dynamic changes of the classifier function.

  2. Limit the I/O performed for lookup tables.

    1. Use the TOP 1 to return only one row.

    2. Minimize the number of rows in the table.

    3. Make all rows of the table exist on a single page, or a small number of pages.

    4. Confirm that rows found using the Index Seek operations use as many seeking columns as possible.

    5. De-normalize to a single table if you are considering using multiple tables with joins.

  3. Prevent blocking on the lookup table.

    1. Use the NOLOCK hint to prevent blocking or use SET LOCK_TIMEOUT in the function with a maximum value of 1,000 milliseconds.

    2. Table(s) must exist in the master database. (The master database is the only database that is guaranteed to be recovered when the client computers attempt to connect).

    3. Always fully qualify the table name with the schema. The database name is not necessary since it has to be the master database.

    4. No triggers on the table.

    5. If you are updating the table contents, make sure to use a snapshot isolation level transaction in the classifier function to prevent Writer blocking Readers. Note that using the NOLOCK hint should also mitigate this.

    6. If possible, disable the classifier function when changing the table contents.

      Warning

      We highly recommend following these best practices. If there are issues that prevent you from following the best practices, we recommend that you contact Microsoft Support so that you can proactively prevent any future problems.

See Also

Resource Governor
Enable Resource Governor
Resource Governor Resource Pool
Resource Governor Workload Group
Configure Resource Governor Using a Template
View Resource Governor Properties
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)