如何创建和测试分类器用户定义函数 (Transact-SQL)

本主题说明如何创建和测试分类器用户定义函数 (UDF)。 这些步骤涉及在 SQL Server Management Studio 查询编辑器中执行 Transact-SQL 语句。 建议您先阅读编写分类器函数的注意事项,然后再继续阅读下面的内容。

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

在本示例中:

  • 创建了 pProductionProcessing 资源池和 gProductionProcessing 工作负荷组,用于在指定时间范围内进行生产处理。

  • 创建了 pOffHoursProcessing 资源池和 gOffHoursProcessing 工作负荷组,用于处理不符合生产处理要求的连接。

  • 在 master 中创建了 TblClassificationTimeTable 表,用于保存可根据登录时间计算的开始和结束时间。 必须在 master 中创建该表,因为资源调控器对分类器函数使用了架构绑定。

    注意注意

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

正如编写分类器函数的注意事项中所提到的一样,分类器函数可能延长登录时间。 过于复杂的函数可能会导致登录超时或减慢快速连接速度。

创建分类器用户定义函数

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

    --- 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. 更新内存中的配置。

    ALTER RESOURCE GOVERNOR RECONFIGURE
    GO
    
  3. 创建一个表,并定义生产处理时间范围的开始和结束时间。

    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. 创建分类器函数,它使用时间函数以及可根据查找表中的时间计算的值。

    注意注意

    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
    
  5. 注册分类器函数并更新内存中的配置。

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

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

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

    USE master
    SELECT * FROM sys.resource_governor_resource_pools
    SELECT * FROM sys.resource_governor_workload_groups
    GO
    
  2. 使用以下查询验证分类器函数是否存在以及是否启用。

    --- 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. 使用以下查询获取资源池和工作负荷组的当前运行时数据。

    SELECT * FROM sys.dm_resource_governor_resource_pools
    SELECT * FROM sys.dm_resource_governor_workload_groups
    GO
    
  4. 使用以下查询确定每个组中包含的会话。

    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. 使用以下查询确定每个组中包含的请求。

    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. 使用以下查询确定分类器中运行的请求。

    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