ALTER QUEUE (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerInstância Gerenciada de SQL do Azure

Altera as propriedades de uma fila.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

database_name (object)
É o nome do banco de dados que contém a fila a ser alterada. Quando nenhum database_name for fornecido, o padrão será o banco de dados atual.

schema_name (object)
É o nome do esquema ao qual a nova fila pertence. Quando nenhum schema_name for fornecido, o padrão será o esquema padrão do usuário atual.

queue_name
É o nome da fila a ser alterada.

STATUS (Fila)
Especifica se a fila está disponível (ON) ou indisponível (OFF). Quando a fila estiver indisponível, nenhuma mensagem pode ser adicionada à fila ou removida dela.

RETENTION
Especifica a configuração de retenção para a fila. Se RETENTION = ON, todas as mensagens enviadas ou recebidas em conversas usando essa fila serão mantidas na fila até que as conversas tenham terminado. Isso permite manter as mensagens para fins de auditoria ou executar transações de compensação se ocorrer um erro.

Observação

Definir RETENÇÃO = ON pode reduzir o desempenho. Essa configuração deve ser usada somente se for necessária para atender ao contrato de nível de serviço para o aplicativo.

ACTIVATION
Especifica as informações sobre o procedimento armazenado que é ativado ao processar as mensagens que chegam nessa fila.

STATUS (Ativação)
Especifica se a fila ativa ou não o procedimento armazenado. Quando o STATUS = ON, a fila começa o procedimento armazenado especificado com PROCEDURE_NAME quando o número de procedimentos atualmente sendo executados for menos que MAX_QUEUE_READERS e quando as mensagens chegarem à fila mais rápido do que os procedimentos armazenados recebam as mensagens. Quando o STATUS = OFF, a fila não ativa o procedimento armazenado.

REBUILD [ WITH <queue_rebuild_options> ]
Aplica-se a: SQL Server 2016 (13.x) e posterior.

Recompila todos os índices na tabela interna da fila. Use esse recurso quando estiver com problemas de fragmentação devido à alta carga. MAXDOP é a única opção de recompilação de fila com suporte. REBUILD sempre é uma operação offline.

REORGANIZE [ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
Aplica-se a: SQL Server 2016 (13.x) e posterior.

Reorganize todos os índices na tabela interna da fila.
Ao contrário de REORGANIZE em tabelas de usuário, REORGANIZE em uma fila sempre é executada como uma operação offline porque os bloqueios no nível da página são desabilitados explicitamente nas filas.

Dica

Para obter as diretrizes gerais sobre a fragmentação de índice, quando a fragmentação estiver entre 5% e 30%, reorganize o índice. Quando a fragmentação estiver acima de 30%, recompile o índice. No entanto, esses números são apenas para diretrizes gerais como um ponto inicial para o seu ambiente. Para determinar a quantidade de fragmentação de índice, use sys.dm_db_index_physical_stats (Transact-SQL) – confira o exemplo G neste artigo para obter exemplos.

MOVE TO { file_group | "default" }
Aplica-se a: SQL Server 2016 (13.x) e posterior.

Move a tabela interna da fila (com seus índices) para um grupo de arquivos especificado pelo usuário. O novo grupo de arquivos não pode ser somente leitura.

PROCEDURE_NAME = <procedure>
Especifica o nome do procedimento armazenado a ser ativado quando a fila contiver mensagens a serem processadas. Esse valor deve ser um identificador do SQL Server.

database_name (procedimento)
É o nome do banco de dados que contém o procedimento armazenado.

schema_name (procedimento)
É o nome do esquema que é proprietário do procedimento armazenado.

stored_procedure_name
É o nome do procedimento armazenado.

MAX_QUEUE_READERS =max_reader
Especifica o número máximo de instâncias do procedimento armazenado de ativação que a fila inicia simultaneamente. O valor de max_readers precisa ser um número entre 0 e 32767.

EXECUTE AS
Especifica a conta do usuário do banco de dados do SQL Server sob a qual é executado o procedimento armazenado de ativação. O SQL Server deve poder verificar as permissões deste usuário no momento em que a fila ativa o procedimento armazenado. Para um usuário de domínio do Windows, o SQL Server deve estar conectado ao domínio e ser capaz de validar as permissões do usuário especificado quando o procedimento for iniciado, caso contrário, a ativação falhará. Para um usuário do SQL Server, o servidor sempre pode verificar as permissões.

SELF
Especifica que o procedimento armazenado é executado como o usuário atual. (O principal do banco de dados que executa essa instrução ALTER QUEUE.)

'user_name'
É o nome do usuário com o qual o procedimento armazenado é executado. user_name deve ser um usuário válido do SQL Server especificado como um identificador do SQL Server. O usuário atual precisa ter a permissão IMPERSONATE para o user_name especificado.

OWNER
Especifica que o procedimento armazenado é executado como o proprietário da fila.

DROP
Exclui todas as informações de ativação associadas à fila.

POISON_MESSAGE_HANDLING
Especifica se a manipulação de mensagens suspeitas está habilitada. O padrão é ON.

A fila que tiver a manipulação de mensagens suspeitas definida como OFF, não será desabilitada após cinco reversões consecutivas de transações. Isso permite que um sistema personalizado de manipulação de mensagens suspeitas seja definido pelo aplicativo.

Comentários

Quando uma fila com um procedimento armazenado de ativação especificado tiver mensagens, a alteração do status de ativação de OFF para ON ativa imediatamente o procedimento armazenado de ativação. A alteração do status de ativação de ON para OFF faz o Broker parar de ativar instâncias do procedimento armazenado, mas não interrompe as instâncias do procedimento armazenado em execução atualmente.

A alteração de uma fila para adicionar um procedimento armazenado de ativação não altera o status de ativação da fila. A alteração do procedimento armazenado de ativação para a fila não afeta as instâncias do procedimento armazenado de ativação em execução atualmente.

O Agente de Serviço verifica o número máximo de leitores de fila para uma fila como parte do processo de ativação. Portanto, alterar uma fila para aumentar o número de máximo de leitores de fila permite que o Agente de Serviço inicie imediatamente mais instâncias do procedimento armazenado de ativação. A alteração de uma fila para diminuir o número de máximo de leitores de fila não afeta as instâncias do procedimento armazenado de ativação em execução atualmente. Entretanto, o Agente de Serviço não inicia uma nova instância do procedimento armazenado até que o número de instâncias para o procedimento armazenado de ativação seja menor que o número máximo configurado.

Quando uma fila não está disponível, o Agente de Serviço mantém as mensagens para os serviços que usam a fila na transmissão para o banco de dados. A exibição de catálogo sys.transmission_queue fornece uma exibição da fila de transmissão.

Se uma instrução RECEIVE ou GET CONVERSATION GROUP especificar uma fila não disponível, ela falhará com um erro Transact-SQL.

Permissões

A permissão para alterar uma fila assume como padrão o proprietário da fila, os membros das funções de banco de dados fixas db_ddladmin ou db_owner e os membros da função de servidor fixa sysadmin.

Exemplos

a. Tornando uma fila não disponível

O exemplo a seguir torna a fila ExpenseQueue não disponível para receber mensagens.

ALTER QUEUE ExpenseQueue WITH STATUS = OFF ;

B. Alterando o procedimento armazenado de ativação

O exemplo a seguir altera o procedimento armazenado que a fila inicia. O procedimento armazenado executa como o usuário que executou a instrução ALTER QUEUE.

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

C. Alterando o número de leitores da fila

O exemplo a seguir define como 7 o número máximo de instâncias de procedimento armazenado que o Agente de Serviço inicia para essa fila.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (MAX_QUEUE_READERS = 7) ;

D. Alterando o procedimento armazenado de ativação e a conta EXECUTE AS

O exemplo a seguir altera o procedimento armazenado que o Agente de Serviço inicia. O procedimento armazenado executa como o usuário SecurityAccount.

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

E. Definindo a fila para reter mensagens

O exemplo a seguir define a fila para reter mensagens. A fila retém todas as mensagens enviadas para ou dos serviços que a utilizam, até terminar a conversa que contém a mensagem.

ALTER QUEUE ExpenseQueue WITH RETENTION = ON ;

F. Removendo a ativação de uma fila

O exemplo a seguir remove todas as informações de ativação da fila.

ALTER QUEUE ExpenseQueue WITH ACTIVATION (DROP) ;

G. Recompilando índices de fila

Aplica-se a: SQL Server 2016 (13.x) e posterior.

O exemplo a seguir recompila índices de fila

ALTER QUEUE ExpenseQueue REBUILD WITH (MAXDOP = 2)

H. Reorganizando índices de fila

Aplica-se a: SQL Server 2016 (13.x) e posterior.

O exemplo a seguir reorganiza índices de fila

ALTER QUEUE ExpenseQueue REORGANIZE

I: movendo a tabela interna da fila para outro grupo de arquivos

Aplica-se a: SQL Server 2016 (13.x) e posterior.

ALTER QUEUE ExpenseQueue MOVE TO [NewFilegroup]

Consulte Também

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