ALTER QUEUE (Transact-SQL)

Articolo
05/30/2023

Si applica a: SQL Server Istanza gestita di SQL di Azure

Modifica le proprietà di una coda.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

database_name (oggetto)
Nome del database contenente la coda da modificare. Se non si specifica database_name, per impostazione predefinita viene usato il database corrente.

schema_name (object)
Nome dello schema cui appartiene la nuova coda. Se non si specifica schema_name, per impostazione predefinita viene usato lo schema predefinito dell'utente corrente.

queue_name
Nome della coda da modificare.

STATUS (coda)
Specifica se la coda è disponibile (ON) o non disponibile (OFF). Quando la coda non è disponibile, non è possibile aggiungere o rimuovere messaggi nella coda.

RETENTION
Specifica l'impostazione di memorizzazione per la coda. Se RETENTION = ON, tutti i messaggi inviati o ricevuti per conversazioni che utilizzano la coda vengono memorizzati nella coda fino al termine delle conversazioni. Ciò consente di memorizzare i messaggi a scopi di controllo oppure di eseguire transazioni di compensazione se si verificano errori.

Nota

L'impostazione di RETENTION = ON può ridurre le prestazioni. È consigliabile utilizzare questa impostazione solo se necessario per soddisfare il contratto di servizio per l'applicazione.

ACTIVATION
Specifica informazioni sulla stored procedure attivata per l'elaborazione dei messaggi che arrivano nella coda.

STATUS (attivazione)
Specifica se la coda attiva la stored procedure. Se STATUS = ON, la coda avvia la stored procedure specificata con PROCEDURE_NAME quando il numero di procedure in esecuzione è minore di MAX_QUEUE_READERS e quando i messaggi arrivano nella coda più velocemente di quanto non vengano ricevuti dalle stored procedure. Se STATUS = OFF, la coda non attiva la stored procedure.

REBUILD [ WITH <queue_rebuild_options> ]
Si applica a: SQL Server 2016 (13.x) e versioni successive.

Ricompila tutti gli indici della tabella interna della coda. Usare questa funzionalità quando si verificano problemi di frammentazione a causa di un carico elevato. MAXDOP è l'unica opzione di ricompilazione della coda supportata. L'operazione REBUILD viene sempre eseguita in modalità offline.

REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
Si applica a: SQL Server 2016 (13.x) e versioni successive.

Riorganizza tutti gli indici della tabella interna della coda.
A differenza di REORGANIZE nelle tabelle utente, l'operazione REORGANIZE in una coda viene sempre eseguita in modalità offline perché i blocchi a livello di pagina sono disabilitati in modo esplicito nelle code.

Suggerimento

In linea generale, quando la frammentazione dell'indice è compresa tra il 5 e il 30%, è consigliabile riorganizzare l'indice. Se la frammentazione è superiore al 30%, ricompilare l'indice. Si tratta tuttavia di indicazioni di carattere generale, da considerare come punto di partenza per l'ambiente. Per determinare la percentuale di frammentazione dell'indice, usare sys.dm_db_index_physical_stats (Transact-SQL). Fare riferimento all'esempio G dell'articolo.

MOVE TO { file_group | "default" }
Si applica a: SQL Server 2016 (13.x) e versioni successive.

Sposta la tabella interna della coda (con i relativi indici) in un filegroup specificato dall'utente. Il nuovo filegroup non può essere di sola lettura.

PROCEDURE_NAME = <procedure>
Specifica il nome della stored procedure da attivare quando la coda contiene messaggi da elaborare. Questo valore deve essere un identificatore di SQL Server.

database_name (procedura)
Nome del database contenente la stored procedure.

schema_name (procedura)
Nome dello schema a cui appartiene la stored procedure.

stored_procedure_name
Nome della stored procedure.

MAX_QUEUE_READERS =max_reader
Specifica il numero massimo di istanze della stored procedure di attivazione che possono essere avviate simultaneamente dalla coda. Il valore di max_readers deve essere un numero compreso tra 0 e 32767.

EXECUTE AS
Specifica l'account utente del database SQL Server in cui viene eseguita la stored procedure di attivazione. SQL Server deve essere in grado di controllare le autorizzazioni per questo utente quando la stored procedure viene attivata dalla coda. Per un utente di dominio di Windows, SQL Server deve essere connesso al dominio e deve essere in grado di convalidare le autorizzazioni dell'utente specificato quando la procedura viene attivata o l'attivazione ha esito negativo. Per un utente di SQL Server, il server è sempre in grado di eseguire il controllo delle autorizzazioni.

SELF
Specifica che la stored procedure viene eseguita con l'account dell'utente corrente, (l'entità di database che esegue l'istruzione ALTER QUEUE).

'user_name'
Si tratta del nome dell'utente usato per l'esecuzione della stored procedure. Il valore user_name deve corrispondere a un utente di SQL Server valido specificato come identificatore di SQL Server. L'utente corrente deve avere l'autorizzazione IMPERSONATE per il nome user_name specificato.

OWNER
Specifica che la stored procedure viene eseguita con l'account del proprietario della coda.

DROP
Elimina tutte le informazioni di attivazione associate alla coda.

POISON_MESSAGE_HANDLING
Specifica se la gestione del messaggio non elaborabile è abilitata. Il valore predefinito è ON.

Una coda con la gestione di messaggi non elaborabili impostata su OFF non verrà disabilitata dopo cinque rollback di transazioni consecutivi. In questo modo è possibile che un sistema di gestione di messaggi non elaborabili venga definito dall'applicazione.

Osservazioni:

Se una coda per cui viene specificata una stored procedure di attivazione contiene messaggi, la modifica dello stato di attivazione da OFF a ON comporta l'attivazione immediata della stored procedure di attivazione. La modifica dello stato di attivazione da ON a OFF impedisce a Service Broker di attivare altre istanze della stored procedure, ma non arresta le istanze della stored procedure già in esecuzione.

La modifica di una coda per l'aggiunta di una stored procedure di attivazione non modifica lo stato di attivazione della coda. La modifica della stored procedure di attivazione per la coda non influisce sulle istanze della stored procedure di attivazione già in esecuzione.

Service Broker controlla il numero massimo di lettori di una coda nell'ambito del processo di attivazione. Se si modifica una coda per aumentare il numero massimo di lettori, pertanto, Service Broker potrà avviare immediatamente più istanze della stored procedure di attivazione. La modifica di una coda per ridurre il numero massimo di lettori non influisce sulle istanze della stored procedure di attivazione già in esecuzione. Tuttavia, Service Broker non attiverà alcuna nuova istanza della stored procedure fino a quando il numero di istanze per la stored procedure di attivazione non risulterà inferiore al numero massimo configurato.

In caso una coda non sia disponibile, Service Broker manterrà i messaggi riguardanti i servizi che usano tale coda nella coda di trasmissione relativa al database. La vista del catalogo sys.transmission_queue consente di visualizzare il contenuto della coda di trasmissione.

Se in un'istruzione RECEIVE o GET CONVERSATION GROUP viene specificata una coda non disponibile, l'istruzione avrà esito negativo e verrà generato un errore di Transact-SQL.

Autorizzazioni

Le autorizzazioni per la modifica di una coda vengono assegnate per impostazione predefinita al proprietario della coda, ai membri del ruolo predefinito del database db_ddladmin o db_owner e ai membri del ruolo predefinito del server sysadmin.

Esempi

R. Impostazione di una coda come non disponibile

Nell'esempio seguente la coda ExpenseQueue viene resa non disponibile per la ricezione di messaggi.

ALTER QUEUE ExpenseQueue WITH STATUS = OFF ;

B. Modifica della stored procedure di attivazione

Nell'esempio seguente viene modificata la stored procedure avviata dalla coda. La stored procedure viene eseguita con l'account dell'utente che ha eseguito l'istruzione ALTER QUEUE.

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

C. Modifica del numero di lettori di coda

Nell'esempio seguente il numero massimo di istanze della stored procedure avviate da Service Broker per questa coda viene impostato su 7.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (MAX_QUEUE_READERS = 7) ;

D. Modifica della stored procedure di attivazione e dell'account EXECUTE AS

Nell'esempio seguente viene modificata la stored procedure avviata da Service Broker. La stored procedure viene eseguita con l'account SecurityAccount.

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

E. Impostazione della coda per la memorizzazione dei messaggi

Nell'esempio seguente viene impostata la coda per la memorizzazione dei messaggi. Nella coda vengono memorizzati tutti i messaggi inviati ai servizi o provenienti dai servizi che utilizzano la coda fino alla fine della conversazione contenente il messaggio.

ALTER QUEUE ExpenseQueue WITH RETENTION = ON ;

F. Rimozione delle informazioni di attivazione da una coda

Nell'esempio seguente vengono rimosse tutte le informazioni di attivazione dalla coda.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (DROP) ;

G. Ricompilazione degli indici della coda

Si applica a: SQL Server 2016 (13.x) e versioni successive.

Nell'esempio seguente viene illustrata la ricompilazione degli indici della coda

ALTER QUEUE ExpenseQueue REBUILD WITH (MAXDOP = 2)

H. Riorganizzazione degli indici della coda

Si applica a: SQL Server 2016 (13.x) e versioni successive.

Nell'esempio seguente viene illustrata la riorganizzazione degli indici della coda

ALTER QUEUE ExpenseQueue REORGANIZE

I. Spostamento della tabella interna della coda in un altro filegroup

Si applica a: SQL Server 2016 (13.x) e versioni successive.

ALTER QUEUE ExpenseQueue MOVE TO [NewFilegroup]

Vedi anche

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

Condividi tramite

ALTER QUEUE (Transact-SQL)

Sintassi

Argomenti

Osservazioni:

Autorizzazioni

Esempi

R. Impostazione di una coda come non disponibile

B. Modifica della stored procedure di attivazione

C. Modifica del numero di lettori di coda

D. Modifica della stored procedure di attivazione e dell'account EXECUTE AS

E. Impostazione della coda per la memorizzazione dei messaggi

F. Rimozione delle informazioni di attivazione da una coda

G. Ricompilazione degli indici della coda

H. Riorganizzazione degli indici della coda

I. Spostamento della tabella interna della coda in un altro filegroup

Vedi anche

Commenti e suggerimenti

Risorse aggiuntive