ユーザー定義の分類子関数の作成とテスト

  • [アーティクル]

適用対象: SQL ServerAzure SQL Managed Instance

このトピックでは、ユーザー定義 (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

  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 (10.0.x) で、日付と時刻のデータ型および関数の拡張セットが導入されました。 詳細については、「日付と時刻のデータ型および関数 (Transact-SQL)」を参照してください。

    CREATE FUNCTION fnTimeClassifier()  
RETURNS sysname  
WITH SCHEMABINDING  
AS  
BEGIN  
/* We recommend running the classifier function code under 
snapshot isolation level OR using NOLOCK hint to avoid blocking on 
lookup table. In this example, we are using NOLOCK hint. */
     DECLARE @strGroup sysname  
     DECLARE @loginTime time  
     SET @loginTime = CONVERT(time,GETDATE())  
     SELECT TOP 1 @strGroup = strGroupName  
          FROM dbo.tblClassificationTimeTable WITH(NOLOCK)
          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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS s  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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 AS r  
INNER JOIN sys.dm_resource_governor_workload_groups AS 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

分類子関数に参照テーブルを使用する際のベスト プラクティス

  1. どうしても必要である場合を除き、参照テーブルは使用しないでください。 使用する必要がある場合は、関数そのものに参照テーブルをハードコーディングすることができます。ただし、そのためには、分類子関数に伴う複雑さや動的な変化とのバランスを考慮する必要があります。

  2. 参照テーブルに対して実行される I/O を制限します。

    1. TOP 1 を使用して、返される行数を 1 行に絞り込みます。

    2. テーブルの行数を最小限にします。

    3. テーブルのすべての行が単一のページまたは少数のページに収まるようにします。

    4. Index Seek 操作を使用して検出された行に、シーク列ができるだけ多く使用されていることを確認します。

    5. 複数のテーブルを結合によって使用することを検討している場合は、単一のテーブルに非正規化します。

  3. 参照テーブルでのブロックを回避します。

    1. NOLOCK ヒントを使用してブロックを回避するか、SET LOCK_TIMEOUT (最大値は 1,000 ミリ秒) を関数に使用します。

    2. テーブルは、master データベース内に存在している必要があります (クライアント コンピューターの接続試行時に復旧が保証されるデータベースは、master データベースだけです)。

    3. テーブル名は必ずスキーマ付きで完全修飾します。 master データベースであることがわかっているため、データベース名は不要です。

    4. テーブルにトリガーがないようにします。

    5. テーブルの内容を更新する場合は、ライターがリーダーをブロックすることのないように、分類子関数で必ずスナップショット分離レベルのトランザクションを使用します。 この点は NOLOCK ヒントを使用して回避することもできます。

    6. テーブルの内容を変更する場合、可能であれば、分類子関数を無効にします。

      警告

      以上のベスト プラクティスに従うことを強くお勧めします。 ベスト プラクティスに従うことのできない事情がある場合は、問題の発生を事前に防ぐためにも、Microsoft サポートにお問い合わせいただくことをお勧めします。

参照

リソース ガバナー
リソース ガバナーの有効化
リソース ガバナー リソース プール
リソース ガバナー ワークロード グループ
テンプレートを使用してリソース ガバナーを構成する
リソース ガバナー プロパティの表示
ALTER RESOURCE GOVERNOR (Transact-SQL)
CREATE RESOURCE POOL (Transact-SQL)
CREATE WORKLOAD GROUP (Transact-SQL)
CREATE FUNCTION (Transact-SQL)
ALTER RESOURCE GOVERNOR (Transact-SQL)