ALTER QUEUE (Transact-SQL)

適用於：SQL ServerAzure SQL 受控執行個體

變更佇列屬性。

Transact-SQL 語法慣例

Syntax

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 )  
}  

注意

若要檢視 SQL Server 2014 （12.x） 和舊版的 Transact-SQL 語法，請參閱 舊版檔。

引數

database_name (object)
這是要變更之佇列所在的資料庫名稱。 若未提供 database_name，預設為目前的資料庫。

schema_name (object)
這是新佇列所屬的結構描述名稱。 若未提供 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 一律是離線作業。

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 (procedure)
這是包含預存程序的資料庫名稱。

schema_name (procedure)
這是擁有預存程序的結構描述名稱。

stored_procedure_name
這是預存程序的名稱。

MAX_QUEUE_READERS =max_reader
指定佇列同時啟動的啟用預存程序的最大執行個體數目。 max_readers 的值必須是在 0 和 32767 之間的數字。

EXECUTE AS
指定啟用預存程序執行所使用的 SQL Server 資料庫使用者帳戶。 當佇列啟用預存程序時，SQL Server 必須能夠檢查這名使用者的權限。 如果是 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 的佇列將不會在五個連續的交易復原後停用。 這可讓應用程式定義自訂有害訊息處理系統。

備註

當含有指定之啟用預存程序的佇列包含訊息時，將啟用狀態 OFF 改成 ON 會立即啟動這個啟用預存程序。 將啟用狀態 ON 改成 OFF 會使 Broker 停止啟用預存程序的執行個體，但並不會停止目前在執行中的預存程序之執行個體。

變更佇列來加入啟用預存程序，並不會變更佇列的啟用狀態。 變更佇列的啟用預存程序，並不會影響目前在執行中的啟用預存程序的執行個體。

在啟用過程中，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)