创建和测试分类器 User-Defined 函数

2017-06-13

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

以下过程中所示的示例说明了创建相当复杂的分类器用户定义的函数的可能性。

在我们的示例中：

资源池（pProductionProcessing）和工作负荷组（gProductionProcessing）是在指定时间范围内为生产处理创建的。
创建一个资源池（pOffHoursProcessing）和工作负荷组（gOffHoursProcessing），用于处理不符合生产处理要求的连接。
表（TblClassificationTimeTable）是在主节点中创建的，用于保存可针对登录时间计算的开始时间和结束时间。这必须在 master 中创建，因为 Resource Governor 对分类器函数使用架构绑定。

注释

最佳做法是，不应在 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 引入了一组扩展的日期和时间数据类型和函数。有关详细信息，请参阅日期和时间数据类型和函数（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

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

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 s  
     INNER JOIN sys.dm_resource_governor_workload_groups 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 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

使用以下查询了解分类器中正在运行的请求。

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

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

除非绝对必要，否则不要使用查阅表格。如果需要使用查阅表格，可以将它硬编码到函数本身中;但是，这需要与分类器函数的复杂性和动态更改进行平衡。
限制查找表的 I/O 操作。
1. 使用 TOP 1 只返回一行。
2. 尽量减少表中的行数。
3. 使表的所有行都存在于单个页面上，或少量的页。
4. 确认通过索引查找操作找到的行使用尽可能多的查找列。
5. 如果考虑将多个表与联接一起使用，应将表格去规范化为一个单表。
防止查找表的阻塞。
1. 使用NOLOCK提示来防止阻塞，或者在函数中使用SET LOCK_TIMEOUT，并将最大值设为1000毫秒。
2. Table（s）必须存在于 master 数据库中。客户端计算机尝试连接时，仅有 master 数据库确保能够恢复。
3. 始终使用模式完全限定表名称。不需要数据库名称，因为它必须是 master 数据库。
4. 表上没有触发器。
5. 如果要更新表内容，请确保使用快照隔离级别事务来阻止写入器阻止读取器。请注意，使用 NOLOCK 提示还应缓解此问题。
6. 如果可能，请在更改表内容时禁用分类器函数。
  
  警告
  
  强烈建议遵循这些最佳做法。如果存在阻止你遵循最佳做法的问题，我们建议你联系Microsoft支持部门，以便可以主动防止将来出现任何问题。

另请参阅

资源调控器
 启用资源调控器
 资源调控器资源池
 资源调控器工作负荷组
 使用模板配置资源调控器
 查看资源调控器属性
 ALTER RESOURCE GOVERNOR （Transact-SQL）
CREATE RESOURCE POOL （Transact-SQL）
创建工作负荷组（Transact-SQL）
CREATE FUNCTION （Transact-SQL）
ALTER RESOURCE GOVERNOR （Transact-SQL）

通过

创建和测试分类器 User-Defined 函数

创建分类器用户定义函数

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

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

另请参阅

其他资源