创建和测试分类器用户定义函数Create and Test a Classifier User-Defined Function

适用对象:是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何创建和测试分类器用户定义函数 (UDF)。This topic shows how to create and test a classifier user-defined function (UDF). 这些步骤涉及在 Transact-SQLTransact-SQL 查询编辑器中执行 SQL Server Management StudioSQL Server Management Studio 语句。The steps involve executing Transact-SQLTransact-SQL statements in the SQL Server Management StudioSQL 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:

  • 创建了 pProductionProcessing 资源池和 gProductionProcessing 工作负荷组,用于在指定时间范围内进行生产处理。A resource pool (pProductionProcessing) and workload group (gProductionProcessing) are created for production processing during a specified time range.

  • 创建了 pOffHoursProcessing 资源池和 gOffHoursProcessing 工作负荷组,用于处理不符合生产处理要求的连接。A resource pool (pOffHoursProcessing) and workload group (gOffHoursProcessing) are created for handling connections that do not meet the requirements for production processing.

  • 在 master 中创建了 TblClassificationTimeTable 表,用于保存可根据登录时间计算的开始和结束时间。A table (TblClassificationTimeTable) is created in master to hold start and end times that can be evaluated against a login time. 必须在 master 中创建该表,因为资源调控器对分类器函数使用了架构绑定。This must be created in master because Resource Governor uses schema binding for classifier functions.

    备注

    作为一种最佳做法,您不应在 master 中存储经常更新的大型表。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.

    备注

    SQL Server 2008SQL Server 2008 引入了一组扩展的日期和时间数据类型和函数。introduced an expanded set of date and time data types and functions. 有关详细信息,请参阅日期和时间数据类型和功能 (Transact-SQL)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. 限制为查找表执行的 I/O。Limit the I/O performed for lookup tables.

    1. 使用 TOP 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. 使用 NOLOCK 提示防止阻塞,或在函数中使用最大值设置为 1000 毫秒的 SET LOCK_TIMEOUTUse the NOLOCK hint to prevent blocking or use SET LOCK_TIMEOUT in the function with a maximum value of 1000 milliseconds.

    2. 表必须存在于 master 数据库中。Table(s) must exist in the master database. (master 数据库是在客户端计算机尝试连接时可确保恢复的唯一一个数据库)。(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. 数据库名称不是必需的,因为该名称只能是 master 数据库的名称。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. 请注意,使用 NOLOCK 提示应该也能缓解此问题。Note that using the NOLOCK hint should also mitigate this.

    6. 如果可能,请在更改表内容时禁用分类器函数。If possible, disable the classifier function when changing the table contents.

      警告

      我们强烈建议遵循如上最佳做法。We highly recommend following these best practices. 如有任何问题妨碍您遵循这些最佳做法,我们建议您与 Microsoft 支持部门联系,以求主动防止未来出现任何问题。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) ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL) CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL) CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL) CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL) ALTER RESOURCE GOVERNOR (Transact-SQL)