ALTER QUEUE (Transact-SQL)

  • Article

S’applique à :SQL ServerAzure SQL Managed Instance

Modifie les propriétés d'une file d'attente.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

Remarque

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.

Arguments

database_name (objet)
Nom de la base de données contenant la file d'attente à modifier. Quand aucun argument database_name n’est fourni, la base de données active est utilisée par défaut.

schema_name (objet)
Nom du schéma auquel la nouvelle file d'attente appartient. Quand aucun argument schema_name n’est fourni, le schéma par défaut de l’utilisateur actif est utilisé par défaut.

queue_name
Nom de la file d'attente à modifier.

STATUS (File d'attente)
Indique si la file d'attente est disponible (ON) ou indisponible (OFF). Lorsque la file d'attente est indisponible, aucun message ne peut y être ajouté ou supprimé.

RETENTION
Spécifie le paramètre de rétention pour la file d'attente. Si le paramètre RETENTION = ON (activé), tous les messages des conversations qui ont été envoyés ou reçus sont conservés dans la file d'attente jusqu'à ce que les conversations s'achèvent. Vous pouvez ainsi conserver les messages pour effectuer des audits ou procéder à des transactions de compensation si une erreur se produit.

Notes

Les performances peuvent diminuer si RETENTION = ON. Ce paramètre doit être utilisé seulement s'il est nécessaire pour satisfaire au contrat de niveau de service pour l'application.

ACTIVATION
Spécifie des informations sur la procédure stockée qui est activée pour traiter les messages arrivant dans cette file d'attente.

STATUS (Activation)
Spécifie si la file d'attente active ou non la procédure stockée. Lorsque STATUS = ON, la file d'attente lance la procédure stockée spécifiée avec PROCEDURE_NAME, quand le nombre de procédures actuellement en cours d'exécution est inférieur à la valeur de MAX_QUEUE_READERS et que la réception des messages dans la file d'attente est plus rapide que la réception des messages par les procédures stockées. Lorsque STATUS = OFF, la file d'attente n'active pas la procédure stockée.

REBUILD [ WITH <queue_rebuild_options> ]
S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

Reconstruit tous les index sur la table interne de file d’attente. Utilisez cette fonction quand vous rencontrez des problèmes de fragmentation en raison d’une charge élevée. MAXDOP est la seule option de reconstruction de file d’attente prise en charge. REBUILD est toujours une opération hors connexion.

REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

Réorganiser tous les index sur la table interne de file d’attente.
Contrairement à REORGANIZE sur les tables utilisateur, REORGANIZE sur une file d’attente est toujours exécutée comme opération hors connexion, car les verrous de page sont explicitement désactivés sur les files d’attente.

Conseil

En guise de recommandation générale concernant la fragmentation des index, quand la fragmentation est comprise entre 5 et 30 %, réorganisez l’index. Quand la fragmentation est supérieure à 30 %, reconstruisez l’index. Ces nombres ne constituent que des recommandations d’ordre général, à utiliser comme point de départ pour votre environnement. Pour déterminer la quantité de fragmentation d’index, utilisez sys.dm_db_index_physical_stats (Transact-SQL) (voir l’exemple G dans cet article pour obtenir des exemples).

MOVE TO { file_group | "default" }
S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

Déplace la table interne de file d’attente (avec ses index) vers un groupe de fichiers spécifié par l’utilisateur. Le nouveau groupe de fichiers ne doit pas être en lecture seule.

PROCEDURE_NAME = <procedure>
Spécifie le nom de la procédure stockée à activer lorsque la file d'attente contient des messages à traiter. Cette valeur doit être un identificateur SQL Server.

database_name (procédure)
Nom de la base de données contenant la procédure stockée.

schema_name (procédure)
Nom du schéma propriétaire de la procédure stockée.

stored_procedure_name
Nom de la procédure stockée.

MAX_QUEUE_READERS =max_reader
Précise le nombre maximal d'instances de la procédure stockée d'activation lancées simultanément par la file d'attente. La valeur de max_readers doit être comprise entre 0 et 32 767.

EXECUTE AS
Spécifie le compte d'utilisateur de la base de données SQL Server sous lequel la procédure stockée d'activation s'exécute. SQL Server doit être en mesure de contrôler les autorisations de cet utilisateur au moment où la file d'attente active la procédure stockée. Pour un utilisateur de domaine Windows, le SQL Server doit être connecté au domaine et pouvoir valider les autorisations de l'utilisateur spécifié lorsque la procédure est activée ou que l'activation échoue. Pour un utilisateur SQL Server, le serveur peut toujours vérifier les autorisations.

SELF
Spécifie que la procédure stockée s'exécute en tant qu'utilisateur actuel. (Principal de la base de données exécutant cette instruction ALTER QUEUE.)

'user_name'
Nom de l'utilisateur sous lequel la procédure stockée s'exécute. L'argument user_name doit être un utilisateur SQL Server valide spécifié comme identificateur SQL Server. L’utilisateur actuel doit disposer de l’autorisation IMPERSONATE pour la valeur user_name spécifiée.

OWNER
Spécifie que la procédure stockée s'exécute en tant que propriétaire de la file d'attente.

DROP
Supprime toutes les informations d'activation associées à la file d'attente.

POISON_MESSAGE_HANDLING
Spécifie si la gestion des messages incohérents est activée. La valeur par défaut est ON.

Une file d'attente dont la gestion des messages incohérents a la valeur OFF ne sera pas désactivée après cinq restaurations de transactions consécutives. L'application peut ainsi définir un système personnalisé de gestion des messages incohérents.

Notes

Si une file d'attente dotée d'une procédure stockée d'activation spécifiée contient des messages, le fait de basculer l'état d'activation de OFF (désactivé) en ON (activé) déclenche immédiatement la procédure stockée d'activation. Le fait de repasser l'état d'activation de ON à OFF arrête l'activation d'instances de la procédure stockée par le broker, mais n'arrête pas les instances de la procédure stockée en cours d'exécution à ce moment-là.

La modification d'une file d'attente pour ajouter une procédure stockée d'activation ne change pas l'état d'activation de la file d'attente. La modification de la procédure stockée d'activation pour la file d'attente n'a pas d'incidence sur les instances de la procédure stockée en cours d'exécution à ce moment-là.

Service Broker vérifie le nombre maximal d'agents de lecture pour une file d'attente dans le cadre du processus d'activation. Ainsi, la modification d'une file d'attente en vue d'augmenter le nombre maximal d'agents de lecture la file d'attente permet à Service Broker de lancer immédiatement davantage d'instances de la procédure stockée d'activation. La modification d'une file d'attente pour diminuer le nombre maximal de lecteurs de file d'attente n'a pas d'incidence sur les instances de la procédure stockée d'activation en cours d'exécution à ce moment-là. Service Broker ne lance cependant pas de nouvelles instances de la procédure stockée tant que le nombre d'instances de la procédure stockée d'activation ne retombe pas en-dessous de la limite maximale configurée.

Lorsqu'une file d'attente est indisponible, Service Broker conserve les messages destinés aux services qui utilisent cette file dans la file d'attente de transmission de la base de données. La vue de catalogue sys.transmission_queue donne une vue de la file d’attente de transmission.

Si une instruction RECEIVE ou GET CONVERSATION GROUP spécifie une file d'attente indisponible, cette instruction échoue et une erreur Transact-SQL se produit.

Autorisations

L'autorisation de modification d'une file d'attente est accordée par défaut au propriétaire de la file d'attente, aux membres du rôle de base de données fixe db_ddladmin ou db_owner et aux membres du rôle serveur fixe sysadmin.

Exemples

R. Mise en indisponibilité d'une file d'attente

L'exemple suivant indique comment rendre la file d'attente ExpenseQueue indisponible pour la réception des messages.

ALTER QUEUE ExpenseQueue WITH STATUS = OFF ;

B. Modification de la procédure stockée d'activation

L'exemple suivant modifie la procédure stockée lancée par la file d'attente. La procédure stockée s'exécute sous l'utilisateur qui a lancé l'instruction ALTER QUEUE.

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

C. Modification du nombre de lecteurs de file d'attente

L'exemple suivant définit à 7 le nombre maximal d'instances de la procédure stockée que Service Broker lance pour cette file d'attente.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (MAX_QUEUE_READERS = 7) ;

D. Modification de la procédure stockée d'activation et du compte EXECUTE AS

L'exemple suivant modifie la procédure stockée lancée par Service Broker. Cette procédure stockée s'exécute en tant qu'utilisateur SecurityAccount.

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

E. Configuration de la file d'attente pour la conservation des messages

L'exemple suivant configure la file d'attente pour qu'elle conserve les messages. La file d'attente conserve tous les messages envoyés ou reçus des services utilisant cette file d'attente, jusqu'à ce que la conversation contenant le message s'achève.

ALTER QUEUE ExpenseQueue WITH RETENTION = ON ;

F. Suppression de l'activation d'une file d'attente

L'exemple suivant supprime toutes les informations d'activation de la file d'attente.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (DROP) ;

G. Reconstruction d’index de file d’attente

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

L’exemple suivant reconstruit des index de file d’attente.

ALTER QUEUE ExpenseQueue REBUILD WITH (MAXDOP = 2)

H. Réorganisation d’index de file d’attente

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

L’exemple suivant réorganise des index de file d’attente.

ALTER QUEUE ExpenseQueue REORGANIZE

I. Déplacement d’une table interne de file d’attente vers un autre groupe de fichiers

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

ALTER QUEUE ExpenseQueue MOVE TO [NewFilegroup]

Voir aussi

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