如何：建立和測試分類使用者定義函數 (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
   

請參閱

概念

資源管理員概念

撰寫分類函數的考量

疑難排解資源管理員