ユーザー定義の分類関数を作成およびテストする方法 (Transact-SQL)
このトピックでは、ユーザー定義 (UDF) の分類関数を作成してテストする方法について説明します。 この手順では、SQL Server Management Studio クエリ エディターで Transact-SQL ステートメントを実行します。 手順に進む前に、「分類関数の記述に関する注意点」を一読することをお勧めします。
次の手順で紹介する例のように、ユーザー定義の分類関数の作成はかなり複雑になる場合があります。
ここで使用する例の内容は次のとおりです。
指定された時間範囲内の実稼働プロセスに対し、リソース プール (pProductionProcessing) とワークロード グループ (gProductionProcessing) を作成します。
実稼働プロセスの要件を満たしていない接続を処理するために、リソース プール (pOffHoursProcessing) とワークロード グループ (gOffHoursProcessing) を作成します。
ログイン時間に対して評価可能な開始時刻と終了時刻を保持するためのテーブル (TblClassificationTimeTable) を master に作成します。 リソース ガバナーが分類関数にスキーマ バインドを使用するため、このテーブルは master に作成する必要があります。
注意 サイズが大きく、頻繁に更新されるテーブルは、master に格納しないようにしてください。
「分類関数の記述に関する注意点」に記載されているように、分類関数を使用するとログイン時間が長くなります。 関数が複雑すぎると、ログインがタイムアウトになったり、接続速度が低下したりすることがあります。
ユーザー定義の分類関数を作成するには
新しいリソース プールとワークロード グループを作成して構成します。 各ワークロード グループを適切なリソース プールに割り当てます。
--- 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
メモリ内の構成を更新します。
ALTER RESOURCE GOVERNOR RECONFIGURE 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
時刻関数および参照テーブル内の時間に対して評価可能な値を使用する分類関数を作成します。
注意 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
分類関数を登録し、メモリ内の構成を更新します。
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier) ALTER RESOURCE GOVERNOR RECONFIGURE GO
リソース プール、ワークロード グループ、およびユーザー定義の分類関数を確認するには
次のクエリを使用して、リソース プールとワークロード グループの構成を取得します。
USE master SELECT * FROM sys.resource_governor_resource_pools SELECT * FROM sys.resource_governor_workload_groups GO
次のクエリを使用して、分類関数が存在し、有効になっていることを確認します。
--- 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
次のクエリを使用して、リソース プールとワークロード グループの現在のランタイム データを取得します。
SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_workload_groups 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
次のクエリを使用して、各グループにある要求を確認します。
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 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