ALTER QUEUE (Transact-SQL)

適用対象: SQL Server Azure SQL Managed Instance

キューのプロパティを変更します。

Transact-SQL 構文表記規則

構文

ALTER QUEUE <object>   
   queue_settings  
   | queue_action  
[ ; ]  
  
<object> : :=  
{ database_name.schema_name.queue_name | schema_name.queue_name | queue_name }
  
<queue_settings> : :=  
WITH  
   [ STATUS = { ON | OFF } [ , ] ]  
   [ RETENTION = { ON | OFF } [ , ] ]  
   [ ACTIVATION (  
       { [ STATUS = { ON | OFF } [ , ] ]   
         [ PROCEDURE_NAME = <procedure> [ , ] ]  
         [ MAX_QUEUE_READERS = max_readers [ , ] ]  
         [ EXECUTE AS { SELF | 'user_name'  | OWNER } ]  
       |  DROP }  
          ) [ , ]]  
         [ POISON_MESSAGE_HANDLING (  
          STATUS = { ON | OFF } )  
         ]   
  
<queue_action> : :=  
   REBUILD [ WITH <query_rebuild_options> ]  
   | REORGANIZE [ WITH (LOB_COMPACTION = { ON | OFF } ) ]  
   | MOVE TO { file_group | "default" }  
  
<procedure> : :=  
{ database_name.schema_name.stored_procedure_name | schema_name.stored_procedure_name | stored_procedure_name }
  
<queue_rebuild_options> : :=  
{  
   ( MAXDOP = max_degree_of_parallelism )  
}  

引数

database_name (オブジェクト)
変更するキューを含むデータベースの名前を指定します。 database_name が指定されていない場合、これは既定で現在のデータベースに設定されます。

schema_name (オブジェクト)
新しいキューが所属するスキーマの名前を指定します。 schema_name が指定されていない場合、これは既定で現在のユーザーに関する既定のスキーマに設定されます。

queue_name
変更するキューの名前を指定します。

STATUS (Queue)
キューが使用可能 (ON) か、使用不可能 (OFF) かを指定します。 キューが使用不可能な場合、キューにメッセージを追加したり、キューからメッセージを削除することはできません。

RETENTION
キューのメッセージ保有期間の設定を指定します。 RETENTION = ON の場合、このキューを使用するメッセージ交換で送信または受信されるすべてのメッセージは、メッセージ交換が終了するまでキュー内に保有されます。 これにより、監査目的でメッセージを保有したり、エラーが発生した場合に補正するトランザクションを実行することができます。

注意

RETENTION = ON に設定すると、パフォーマンスが低下する場合があります。 この設定は、アプリケーションのサービス レベル契約を満たす必要がある場合にのみ使用してください。

ACTIVATION
このキューに到着するメッセージを処理するため、アクティブになっているストアド プロシージャについての情報を指定します。

STATUS (Activation)
キューがストアド プロシージャをアクティブにするかどうかを指定します。 STATUS = ON の場合は、現在実行中のプロシージャの数が MAX_QUEUE_READERS より少なく、ストアド プロシージャによるメッセージの受信よりも早くメッセージがキューに到着する場合に、PROCEDURE_NAME で指定されるストアド プロシージャがキューによって開始されます。 STATUS = OFF の場合は、キューによってストアド プロシージャはアクティブになりません。

REBUILD [ WITH <queue_rebuild_options> ]
適用対象: SQL Server 2016 (13.x) 以降。

キューの内部テーブルのすべてのインデックスを再構築します。 負荷が高いために断片化の問題が発生した場合は、この機能を使用します。 MAXDOP は、唯一サポートされているキューの rebuild オプションです。 REBUILD は常にオフライン操作です。

REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
適用対象: SQL Server 2016 (13.x) 以降。

キューの内部テーブルのすべてのインデックスを再構成します。
ユーザー テーブルの REORGANIZE とは異なり、キューの REORGANIZE は常にオフライン操作として実行されます。これは、キューではページ レベルのロックが明示的に無効になっているためです。

ヒント

インデックスの断片化に関する一般的なガイダンスでは、断片化が 5% から 30% の場合、インデックスを再構成します。 断片化が 30% を超える場合は、インデックスを再構築します。 ただし、これらの数値は環境の開始点として一般的なガイダンスでのみ使用できます。 インデックスの断片化の量を確認するには、sys.dm_db_index_physical_stats (Transact-SQL) を使用します。例については、この記事の例 G を参照してください。

MOVE TO { file_group | "default" }
適用対象: SQL Server 2016 (13.x) 以降。

キューの内部テーブルを (そのインデックスと共に) ユーザー指定のファイル グループに移動します。 新しいファイル グループを読み取り専用にすることはできません。

PROCEDURE_NAME = <procedure>
処理するメッセージがキューに含まれているときにアクティブにするストアド プロシージャの名前を指定します。 この値は SQL Server の識別子にする必要があります。

database_name (プロシージャ)
ストアド プロシージャを含むデータベースの名前を指定します。

schema_name (プロシージャ)
ストアド プロシージャを所有するスキーマの名前です。

stored_procedure_name
ストアド プロシージャの名前です。

MAX_QUEUE_READERS =max_reader
キューで同時に開始する、アクティブ化ストアド プロシージャの最大インスタンス数を指定します。 max_readers の値は、0 から 32767 の数値にする必要があります。

EXECUTE AS
アクティブ化ストアド プロシージャを実行する SQL Server データベース ユーザー アカウントを指定します キューによってストアド プロシージャがアクティブになったとき、SQL Server ではこのユーザーの権限を確認できる必要があります。 Microsoft Windows ドメイン ユーザーの場合は、SQL Server がドメインに接続している必要があり、プロシージャがアクティブ化されたとき、またはアクティブ化が失敗したときに、指定したユーザーの権限を検証できる必要があります。 SQL Server ユーザーの場合は、サーバーで常に権限を確認できます。

SELF
現在のユーザーとしてストアド プロシージャを実行することを指定します。 (対象の ALTER QUEUE ステートメントを実行しているデータベース プリンシパル)。

'user_name'
ストアド プロシージャを実行するユーザーの名前です。user_name には、SQL Server 識別子として指定されている、有効な SQL Server 識別子を指定する必要があります。 現在のユーザーは、指定した user_name に対して IMPERSONATE 権限を保持している必要があります。

OWNER
キューの所有者としてストアド プロシージャを実行します。

DROP
キューに関連付けられているすべてのアクティブ化情報を削除します。

POISON_MESSAGE_HANDLING
有害なメッセージの処理が有効かどうかを指定します。 既定値は ON です。

有害なメッセージの処理が OFF に設定されているキューは、トランザクションのロールバックが連続して 5 回実行されても無効になりません。 これにより、カスタムの有害なメッセージの処理システムをアプリケーションで定義できます。

解説

指定されたアクティブ化ストアド プロシージャが存在するキューにメッセージが含まれている場合は、アクティブ化状態を OFF から ON に変更すると、すぐにアクティブ化ストアド プロシージャがアクティブになります。 アクティブ化状態を ON から OFF に変更すると、ストアド プロシージャのインスタンスのアクティブ化処理は停止しますが、現在実行中のストアド プロシージャのインスタンスは停止しません。

キューにアクティブ化ストアド プロシージャを追加しても、キューのアクティブ化の状態は変わりません。 キューのアクティブ化ストアド プロシージャを変更しても、現在実行中のアクティブ化ストアド プロシージャのインスタンスには影響しません。

Service Broker では、アクティブ化処理の一部として、キューに設定されているキュー リーダーの最大数が確認されます。 したがって、キューに対してキュー リーダーの最大数を増やすと、Service Broker ではすぐにアクティブ化ストアド プロシージャのインスタンスを追加起動できるようになります。 キューに対してキュー リーダーの最大数を減らしても、現在実行中のアクティブ化ストアド プロシージャのインスタンスには影響しません。 ただし、アクティブ化ストアド プロシージャのインスタンスの数が、設定されている最大数より少なくなるまで、Service Broker ではストアド プロシージャの新しいインスタンスは起動されません。

キューが使用できない場合、Service Broker では、データベースの転送キュー内にあるキューを使用するサービスのメッセージが保持されます。 sys.transmission_queue カタログ ビューでは、転送キューのビューが提供されます。

RECEIVE ステートメントまたは GET CONVERSATION GROUP ステートメントで使用できないキューを指定すると、そのステートメントは失敗し、Transact-SQL エラーが返されます。

アクセス許可

既定では、キューを変更する権限は、キューの所有者、db_ddladmin 固定データベース ロールまたは db_owner 固定データベース ロールのメンバー、および sysadmin 固定サーバー ロールのメンバーに与えられています。

例

A. キューを利用できないようにする

次の例では、ExpenseQueue キューをメッセージの受信に利用できないようにします。

ALTER QUEUE ExpenseQueue WITH STATUS = OFF ;  

B. アクティブ化ストアド プロシージャを変更する

次の例では、キューによって開始されるストアド プロシージャを変更します。 このストアド プロシージャは、ALTER QUEUE ステートメントを実行したユーザーとして実行されます。

ALTER QUEUE ExpenseQueue  
    WITH ACTIVATION (  
        PROCEDURE_NAME = new_stored_proc,  
        EXECUTE AS SELF) ;  

C. キュー リーダーの数を変更する

次の例では、このキュー用に Service Broker で起動されるストアド プロシージャ インスタンスの最大数を 7 に設定します。

ALTER QUEUE ExpenseQueue WITH ACTIVATION (MAX_QUEUE_READERS = 7) ;  

D. アクティブ化ストアド プロシージャと EXECUTE AS アカウントを変更する

次の例では、Service Broker によって開始されるストアド プロシージャを変更します。 このストアド プロシージャは、ユーザー SecurityAccount として実行されます。

ALTER QUEUE ExpenseQueue  
    WITH ACTIVATION (  
        PROCEDURE_NAME = AdventureWorks2022.dbo.new_stored_proc ,  
        EXECUTE AS 'SecurityAccount') ;  

E. メッセージを保持するようキューを設定する

次の例では、メッセージを保持するようキューを設定します。 このキューでは、キューを使用するサービス間で送信または受信されるすべてのメッセージが、そのメッセージ交換が終わるまで保持されます。

ALTER QUEUE ExpenseQueue WITH RETENTION = ON ;  

F. キューからアクティブ化情報を削除する

次の例では、キューからすべてのアクティブ化情報を削除します。

ALTER QUEUE ExpenseQueue WITH ACTIVATION (DROP) ;  

G. キュー インデックスを再構築する

適用対象: SQL Server 2016 (13.x) 以降。

次の例では、キュー インデックスを再構築します。

ALTER QUEUE ExpenseQueue REBUILD WITH (MAXDOP = 2)   

H. キュー インデックスを再構成する

適用対象: SQL Server 2016 (13.x) 以降。

次の例では、キュー インデックスを再構成します。

ALTER QUEUE ExpenseQueue REORGANIZE   

I:キューの内部テーブルを別のファイル グループに移動する

適用対象: SQL Server 2016 (13.x) 以降。

ALTER QUEUE ExpenseQueue MOVE TO [NewFilegroup]   

参照

CREATE QUEUE (Transact-SQL)
DROP QUEUE (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL)