共用方式為


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