创建和测试分类器用户定义函数

项目
05/30/2023

适用于：SQL Server Azure SQL 托管实例

本主题说明如何创建和测试分类器用户定义函数 (UDF)。这些步骤涉及在 SQL Server Management Studio 查询编辑器中执行 Transact-SQL 语句。

下面的过程中显示的示例说明了创建非常复杂的分类器用户定义函数的可能性。

在我们的示例中：

创建了 pProductionProcessing 资源池和 gProductionProcessing 工作负荷组，用于在指定时间范围内进行生产处理。
创建了 pOffHoursProcessing 资源池和 gOffHoursProcessing 工作负荷组，用于处理不符合生产处理要求的连接。
在 master 中创建了 TblClassificationTimeTable 表，用于保存可根据登录时间计算的开始和结束时间。必须在 master 中创建该表，因为资源调控器对分类器函数使用了架构绑定。

注意

作为一种最佳做法，您不应在 master 中存储经常更新的大型表。

分类器函数可能延长登录时间。过于复杂的函数可能会导致登录超时或减慢快速连接速度。

创建分类器用户定义函数

创建并配置新的资源池和工作负荷组。将每个工作负荷组分配给相应的资源池。

--- 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

更新内存中的配置。

ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

创建一个表，并定义生产处理时间范围的开始和结束时间。

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

创建分类器函数，它使用时间函数以及可根据查找表中的时间计算的值。有关在分类器函数中使用查找表的信息，请参阅本主题中的“在分类器函数中使用查找表的最佳做法”。

注意

SQL Server 2008 (10.0.x) 引入了一组扩展的日期和时间数据类型和函数。有关详细信息，请参阅日期和时间数据类型和功能 (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

注册分类器函数并更新内存中的配置。

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

验证资源池、工作负荷组以及分类器用户定义函数

使用以下查询获取资源池和工作负荷组配置。

USE master;  
SELECT * FROM sys.resource_governor_resource_pools;  
SELECT * FROM sys.resource_governor_workload_groups;  
GO

使用以下查询验证分类器函数是否存在以及是否启用。

--- 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;

使用以下查询获取资源池和工作负荷组的当前运行时数据。

SELECT * FROM sys.dm_resource_governor_resource_pools;  
SELECT * FROM sys.dm_resource_governor_workload_groups;  
GO

使用以下查询确定每个组中包含的会话。

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

使用以下查询确定每个组中包含的请求。

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

使用以下查询确定分类器中运行的请求。

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

在分类器函数中使用查找表的最佳做法

除非绝对必要，否则不要使用查找表。如果需要使用查找表，可以将其硬编码到函数本身；但是，这样做时需要权衡考虑分类器函数的复杂程度和动态变更。
限制为查找表执行的 I/O。
1. 使用 TOP 1 仅返回一行。
2. 尽量减少表中的行数。
3. 让表中所有行都位于一页中或少数几页中。
4. 确认使用索引查找操作找到的行使用了尽量多的查找列。
5. 如果考虑使用相互联接的多个表，则可以对单个表取消规范化。
防止阻塞查找表。
1. 使用 NOLOCK 提示防止阻塞，或在函数中使用最大值设置为 1,000 毫秒的 SET LOCK_TIMEOUT。
2. 表必须存在于 master 数据库中。（master 数据库是在客户端计算机尝试连接时可确保恢复的唯一一个数据库）。
3. 始终使用架构对表名进行完全限定。数据库名称不是必需的，因为该名称只能是 master 数据库的名称。
4. 表上没有触发器。
5. 如果要更新表内容，请确保在分类器函数中使用快照隔离级别事务来防止编写器阻塞读取器。请注意，使用 NOLOCK 提示应该也能缓解此问题。
6. 如果可能，请在更改表内容时禁用分类器函数。
  
  警告
  
  我们强烈建议遵循如上最佳做法。如有任何问题妨碍您遵循这些最佳做法，我们建议您与 Microsoft 支持部门联系，以求主动防止未来出现任何问题。

另请参阅

资源调控器
 启用资源调控器
 资源调控器资源池
 资源调控器工作负荷组
 使用模板配置资源调控器
 查看资源调控器属性
 ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)

创建和测试分类器用户定义函数

创建分类器用户定义函数

验证资源池、工作负荷组以及分类器用户定义函数

在分类器函数中使用查找表的最佳做法

另请参阅

反馈

反馈

其他资源