ユーザー定義の分類関数を作成およびテストする方法 (Transact-SQL)

このトピックでは、ユーザー定義 (UDF) の分類関数を作成してテストする方法について説明します。この手順では、SQL Server Management Studio クエリ エディタで Transact-SQL ステートメントを実行します。手順に進む前に、「分類関数の記述に関する注意点」を一読することをお勧めします。

次の手順で紹介する例のように、ユーザー定義の分類関数の作成はかなり複雑になる場合があります。

ここで使用する例の内容は次のとおりです。

• 指定された時間範囲内の実稼働プロセスに対し、リソース プール (pProductionProcessing) とワークロード グループ (gProductionProcessing) を作成します。

• 実稼働プロセスの要件を満たしていない接続を処理するために、リソース プール (pOffHoursProcessing) とワークロード グループ (gOffHoursProcessing) を作成します。

• ログイン時間に対して評価可能な開始時間と終了時間を保持するためのテーブル (TblClassificationTimeTable) を master に作成します。リソース ガバナが分類関数にスキーマ バインドを使用するため、このテーブルは 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
   
   --- 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
   
   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
   
   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
   
   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
   
   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
   
   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
   
   --- 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
   
   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
   
   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
   
   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
   
   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
   

関連項目

概念

リソース ガバナーの概念

分類関数の記述に関する注意点

リソース ガバナーのトラブルシューティング