ALTER SERVER CONFIGURATION (Transact-SQL)

  • Artigo

Aplica-se: SQL Server

Modifica as definições da configuração global do servidor atual no SQL Server.

Convenções de sintaxe de Transact-SQL

Sintaxe

ALTER SERVER CONFIGURATION  
SET <optionspec>   
[;]  
  
<optionspec> ::=  
{  
     <process_affinity>  
   | <diagnostic_log>  
   | <failover_cluster_property>  
   | <hadr_cluster_context>  
   | <buffer_pool_extension>  
   | <soft_numa>  
   | <memory_optimized>
   | <hardware_offload>
   | <suspend_for_snapshot_backup>
}  
  
<process_affinity> ::=   
   PROCESS AFFINITY   
   {  
     CPU = { AUTO | <CPU_range_spec> }   
   | NUMANODE = <NUMA_node_range_spec>   
   }  
   <CPU_range_spec> ::=   
      { CPU_ID | CPU_ID  TO CPU_ID } [ ,...n ]   
  
   <NUMA_node_range_spec> ::=   
      { NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,...n ]  
  
<diagnostic_log> ::=   
   DIAGNOSTICS LOG   
   {   
     ON    
   | OFF    
   | PATH = { 'os_file_path' | DEFAULT }    
   | MAX_SIZE = { 'log_max_size' MB | DEFAULT }    
   | MAX_FILES = { 'max_file_count' | DEFAULT }    
   }  
  
<failover_cluster_property> ::=   
   FAILOVER CLUSTER PROPERTY <resource_property>  
   <resource_property> ::=  
      {  
        VerboseLogging = { 'logging_detail' | DEFAULT }    
      | SqlDumperDumpFlags = { 'dump_file_type' | DEFAULT }  
      | SqlDumperDumpPath = { 'os_file_path'| DEFAULT }  
      | SqlDumperDumpTimeOut = { 'dump_time-out' | DEFAULT }  
      | FailureConditionLevel = { 'failure_condition_level' | DEFAULT }  
      | HealthCheckTimeout = { 'health_check_time-out' | DEFAULT }  
      }  
  
<hadr_cluster_context> ::=  
   HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }  
  
<buffer_pool_extension>::=  
    BUFFER POOL EXTENSION   
    { ON ( FILENAME = 'os_file_path_and_name' , SIZE = <size_spec> )   
    | OFF }  
  
    <size_spec> ::=  
        { size [ KB | MB | GB ] }  
  
<soft_numa> ::=  
    SOFTNUMA  
    { ON | OFF }  

<memory-optimized> ::=   
   MEMORY_OPTIMIZED   
   {   
     ON 
   | OFF
   | [ TEMPDB_METADATA = { ON [(RESOURCE_POOL='resource_pool_name')] | OFF }
   | [ HYBRID_BUFFER_POOL = { ON | OFF }
   }  

<hardware_offload> ::=
   HARDWARE_OFFLOAD
   {   
     ON 
   | OFF
   }

<suspend_for_snapshot_backup> ::=
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF } [ ( GROUP = ( <database>,...n) [ , MODE = COPY_ONLY ] ) ]

Argumentos

<process_affinity> ::=

PROCESS AFFINITY
Permite que threads de hardware sejam associados a CPUs.

CPU = { AUTO | <CPU_range_spec> }
Distribui threads de trabalho do SQL Server para cada CPU dentro do intervalo especificado. CPUs fora do intervalo especificado não terão threads atribuídos.

AUTO
Especifica que nenhum thread está atribuído a uma CPU. O sistema operacional pode mover threads livremente entre CPUs com base na carga de trabalho do servidor. Essa configuração é a padrão e é recomendada.

<CPU_range_spec> ::=
Especifica a CPU ou o intervalo de CPUs ao qual atribuir threads.

{ CPU_ID | CPU_ID TO CPU_ID } [ ,...n ]
É a lista de uma ou mais CPUs. IDs de CPU começam com 0 e são valores integer.

NUMANODE = <NUMA_node_range_spec>
Atribui threads a todas as CPUs que pertencem ao nó NUMA especificado ou ao intervalo de nós.

<NUMA_node_range_spec> ::=
Especifica o nó NUMA ou o intervalo de nós NUMA.

{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,...n ]
É a lista de um ou mais nós NUMA. IDs de nó NUMA começam com 0 e são valores integer.

<diagnostic_log> ::=

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

DIAGNOSTICS LOG
Inicia ou interrompe o log de dados de diagnóstico que o procedimento sp_server_diagnostics captura. Este argumento também define parâmetros de configuração de log SQLDIAG, como a contagem de substituições de arquivo de log, o tamanho do arquivo de log e o local do arquivo. Para obter mais informações, consulte Exibir e ler o log de diagnóstico da instância do cluster de failover.

ATIVADO
Inicia o log de dados de diagnóstico do SQL Server no local especificado na opção de arquivo PATH. Este argumento é o padrão.

OFF
Interrompe o log de dados diagnóstico.

PATH = { 'os_file_path' | DEFAULT }
Caminho que indica o local dos logs de diagnóstico. A localização padrão é <\MSSQL\Log> dentro da pasta de instalação da instância de cluster de failover do SQL Server.

MAX_SIZE = { 'log_max_size' MB | DEFAULT }
Tamanho máximo em megabytes que o log de diagnóstico pode atingir. O padrão é 100 MB.

MAX_FILES = { 'max_file_count' | DEFAULT }
Número máximo de arquivos de log de diagnóstico que podem ser armazenados no computador antes de serem reciclados para novos logs de diagnóstico.

<failover_cluster_property> ::=

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

FAILOVER CLUSTER PROPERTY
Modifica as propriedades do cluster de failover privado de recursos do SQL Server.

VERBOSE LOGGING = { 'logging_detail' | DEFAULT }
Define o nível do log do clustering de failover do SQL Server. Pode ser ativado para fornecer detalhes adicionais nos logs de erros para solução de problemas.

  • 0 – O log está desativado (padrão)

  • 1 – Apenas erros

  • 2 – Erros e avisos

Em cenários de failover de recurso, a DLL de recurso do SQL Server pode obter um arquivo de despejo antes que o failover ocorra. Isso se aplica às tecnologias de FCI e de Grupo de Disponibilidade. Quando determina que um recurso do SQL Server falhou, a DLL de recurso do SQL Server usa o utilitário Sqldumpr.exe para obter um arquivo de despejo do processo do SQL Server. Para garantir que o utilitário Sqldumpr.exe gere com êxito o arquivo de despejo no momento do failover do recurso, você precisa definir as três seguintes propriedades como pré-requisitos: SqlDumperDumpTimeOut, SqlDumperDumpPath, SqlDumperDumpFlags.

SQLDUMPEREDUMPFLAGS
Determina o tipo de arquivos de despejo gerados pelo utilitário SQLDumper do SQL Server. A configuração padrão é 0. Valores decimais, em vez de hexadecimais, são usados para essa configuração. Para o minidespejo, use 288, para o minidespejo com uso indireto de memória, use 296 e, para o despejo filtrado, use 33024. Para obter mais informações, veja o artigo da Base de Dados de Conhecimento sobre o utilitário do SQL Server Dumper.

SQLDUMPERDUMPPATH = { 'os_file_path' | DEFAULT }
O local onde o utilitário SQLDumper armazena os arquivos de despejo. Para obter mais informações, veja o artigo da Base de Dados de Conhecimento sobre o utilitário do SQL Server Dumper.

SQLDUMPERDUMPTIMEOUT = { 'dump_time-out' | DEFAULT }
O valor do tempo limite em milissegundos para o utilitário SQLDumper gerar um despejo se ocorrer uma falha do SQL Server. O valor padrão é 0, o que indica que não há nenhum tempo limite para a conclusão do despejo. Para obter mais informações, veja o artigo da Base de Dados de Conhecimento sobre o utilitário do SQL Server Dumper.

FAILURECONDITIONLEVEL = { 'failure_condition_level' | DEFAULT }
As condições sob as quais a instância de cluster de failover do SQL Server deve realizar um failover ou reiniciar. O valor padrão é 3, o que indica que o recurso do SQL Server apresentará failover ou reiniciará em erros críticos do servidor. Para obter mais informações sobre este e outros níveis de condição de falha, veja Configure FailureConditionLevel Property Settings.

HEALTHCHECKTIMEOUT = { 'health_check_time-out' | DEFAULT }
O valor do tempo limite de quanto tempo a DLL de recursos do Mecanismo de Banco de Dados do SQL Server deve aguardar por informações de integridade do servidor antes de considerar a instância do SQL Server como sem resposta. O valor de tempo limite é expresso em milissegundos. O padrão é 60.000 milissegundos (60 segundos).

<hadr_cluster_context> ::=

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }
Alterna o contexto do cluster HADR da instância de servidor para o cluster especificado do Windows Server Failover Cluster (WSFC). O contexto do cluster HADR determina qual cluster WSFC gerencia os metadados das réplicas de disponibilidade hospedadas pela instância de servidor. Use a opção SET HADR CLUSTER CONTEXT somente durante uma migração entre clusters de Grupos de disponibilidade AlwaysOn para uma instância do SQL Server 2012 SP1 (11.0.3x) ou versão posterior em um novo WSFC.

Só é possível alternar o contexto do cluster HADR do WSFC local para um WSFC remoto. Então, você pode optar por voltar do WSFC remoto para o WSFC local. O contexto do cluster HADR pode ser alternado para um cluster remoto somente quando a instância do SQL Server não está hospedando réplicas de disponibilidade.

Um contexto do cluster HADR remoto pode ser alternado novamente para o cluster local a qualquer momento. Entretanto, o contexto não poderá ser alternado novamente enquanto a instância de servidor estiver hospedando réplicas de disponibilidade.

Para identificar o cluster de destino, especifique um dos seguintes valores:

cluster_windows
O nome de rede de um WSFC. Você pode especificar o nome curto ou o nome de domínio completo. Para localizar o endereço IP de destino de um nome curto, ALTER SERVER CONFIGURATION usa a resolução DNS. Em algumas circunstâncias, um nome curto pode causar confusão, e o DNS pode retornar o endereço IP errado. É recomendável especificar o nome de domínio completo.

Observação

Não há suporte para uma migração entre clusters usando essa configuração. Para executar uma migração entre clusters, use um grupo de disponibilidade distribuído ou algum outro método, como o envio de logs.

LOCAL
O WSFC local.

Para obter mais informações, confira Alterar contexto do cluster HADR da instância do servidor (SQL Server).

<buffer_pool_extension>::=

Aplica-se a: SQL Server (começando com SQL Server 2014 (12.x)).

ATIVADO
Habilita a opção de extensão do pool de buffers. Essa opção estende o tamanho do pool de buffers usando o armazenamento não volátil. O armazenamento não volátil, como unidades de estado sólido (SSD), mantém páginas de dados limpas no pool. Para saber mais sobre esse recurso, confira Extensão do pool de buffers. A extensão do pool de buffers não está disponível em todas as edições do SQL Server. Para obter mais informações, confira Edições e recursos com suporte do SQL Server 2022.

FILENAME = 'os_file_path_and_name'
Define o caminho do diretório e o nome de arquivo do cache de extensão do pool de buffers. A extensão do arquivo deve ser especificada como .BPE. Desative a opção BUFFER POOL EXTENSION antes que você possa alterar o FILENAME.

SIZE = size [ KB | MB | GB ]
Define o tamanho do cache. A especificação do tamanho padrão é KB. O tamanho mínimo é o tamanho da Memória Máxima do Servidor. O limite máximo é 32 vezes o tamanho de Memória Máxima do Servidor. Para obter mais informações sobre a Memória Máxima do Servidor, confira sp_configure (Transact-SQL).

Desative a opção BUFFER POOL EXTENSION antes que você possa alterar o tamanho do arquivo. Para especificar um tamanho que seja menor do que o tamanho atual, a instância do SQL Server deve ser reiniciada para recuperar a memória. Caso contrário, o tamanho especificado deve ser igual a ou maior que o tamanho atual.

OFF
Desabilita a opção de extensão do pool de buffers. Desabilite a opção de extensão do pool de buffers antes de modificar quaisquer parâmetros associados, como o tamanho ou o nome do arquivo. Quando esta opção estiver desabilitada, todas as informações de configuração relacionadas serão removidas do Registro.

Aviso

A desabilitação da extensão do pool de buffers pode ter um impacto negativo no desempenho do servidor porque o tamanho do pool de buffers é significativamente reduzido.

<soft_numa>

Aplica-se a: SQL Server (começando com SQL Server 2016 (13.x)).

ATIVADO
Permite que o particionamento automático divida nós de hardware NUMA grandes em nós NUMA menores. Alterar o valor de execução requer a reinicialização do mecanismo de banco de dados.

OFF
Desabilita o particionamento automático de software de nós de hardware NUMA grandes em nós NUMA menores. Alterar o valor de execução requer a reinicialização do mecanismo de banco de dados.

Aviso

Há problemas conhecidos com o comportamento da instrução ALTER SERVER CONFIGURATION com a opção SOFT NUMA temporário e o SQL Server Agent. Esta é a sequência recomendada de operações:

  1. Pare a instância do SQL Server Agent.
  2. Execute sua opção ALTER SERVER CONFIGURATION SOFT NUMA.
  3. Reinicie a instância do SQL Server.
  4. Inicie a instância do SQL Server Agent.

Mais Informações: se você executar o comando ALTER SERVER CONFIGURATION com SET SOFTNUMA antes de reiniciar o serviço do SQL Server, quando o serviço SQL Server Agent for interrompido, ele executará um comando T-SQL RECONFIGURE que reverterá as configurações do SOFTNUMA para o que era antes do ALTER SERVER CONFIGURATION.

<memory_optimized> ::=

Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)).

ON
Habilita todos os recursos no nível da instância que fazem parte da família de recursos do Banco de Dados em Memória. Atualmente, isso inclui os metadados do tempdb com otimização de memória e o pool de buffers híbrido. Exige uma reinicialização para entrar em vigor.

OFF
Desabilita todos os recursos no nível da instância que fazem parte da família de recursos do Banco de Dados em Memória. Exige uma reinicialização para entrar em vigor.

TEMPDB_METADATA = ON | OFF
Habilita ou desabilita somente os metadados do tempdb com otimização de memória. Exige uma reinicialização para entrar em vigor.

RESOURCE_POOL='resource_pool_name'
Quando combinado com TEMPDB_METADATA = ON, especifica o pool de recursos definido pelo usuário que deve ser usado para o tempdb. Se ele não for especificado, o tempdb usará o pool padrão. O pool já precisa existir. Se o pool não estiver disponível quando o serviço for reiniciado, o tempdb usará o pool padrão.

HYBRID_BUFFER_POOL = ON | OFF
Habilita ou desabilita o pool de buffers híbrido no nível da instância. Exige uma reinicialização para entrar em vigor.

<hardware_offload> ::=

Aplica-se a: SQL Server (começando com SQL Server 2022 (16.x)).

ON
Habilita o uso de descarregamento integrado e aceleração integrados para a instância. Exige reinicialização.

OFF
Desabilita todo o uso de descarregamento e aceleração integrados no nível da instância. Exige uma reinicialização para entrar em vigor.

Para obter mais informações, confira Descarregamento e aceleração integrados.

<suspend_for_snapshot_backup> ::=

Aplica-se a: SQL Server, a partir do SQL Server 2022 (16.x)

Suspende os bancos de dados do backup de instantâneo. Pode definir um grupo de um ou mais bancos de dados. Pode designar o modo somente cópia.

SET SUSPEND_FOR_SNAPSHOT_BACKUP = { ON | OFF }

Suspende ou cancela a suspensão de bancos de dados. O padrão é “Desativado”.

GROUP = ( <database>,...n)

Opcional. Define um grupo de um ou mais bancos de dados a serem suspensos. Se não houver uma especificação, a configuração se aplicará a todos os bancos de dados.

MODE = COPY_ONLY

Opcional. Usa o modo COPY_ONLY para todos os backups de banco de dados.

Comentários gerais

Esta instrução não requer uma reinicialização do SQL Server, a menos que explicitamente indicado de outra forma. No caso de uma instância de cluster de failover do SQL Server, uma reinicialização do recurso de cluster do SQL Server não é necessária.

Limitações e Restrições

Essa instrução não oferece suporte a gatilhos DDL.

Permissões

Exige:

  • As permissões ALTER SETTINGS para a opção de afinidade de processo.
  • As permissões ALTER SETTINGS e VIEW SERVER STATE para as opções de propriedade do log de diagnóstico e do cluster de failover.
  • A permissão CONTROL SERVER para a opção de contexto do cluster HADR.
  • A permissão ALTER SERVER STATE para a opção de extensão do pool de buffers.

A DLL de recurso do Mecanismo de Banco de Dados do SQL Server é executada na conta do Sistema Local. Portanto, a conta Sistema Local deve ter acesso de leitura e gravação ao caminho especificado na opção Log de Diagnóstico.

Exemplos

Categoria Elementos de sintaxe em destaque
Configurando a afinidade do processo CPU • NUMANODE • AUTO
Configurando as opções do log de diagnóstico ON • OFF • PATH • MAX_SIZE
Configurando as propriedades do cluster de failover HealthCheckTimeout
Como alterar o contexto do cluster de uma réplica de disponibilidade ' windows_cluster '
Configurando a extensão do pool de buffers BUFFER POOL EXTENSION
Como definir opções do Banco de Dados em Memória MEMORY_OPTIMIZED

Configurando a afinidade do processo

Os exemplos desta seção mostram como definir a afinidade do processo para CPUs e nós NUMA. Os exemplos presumem que o servidor contém 256 CPUs que são organizadas em quatro grupos de 16 nós NUMA cada um. Não são atribuídos threads a nenhum nó NUMA ou CPU.

  • Grupo 0: Nós NUMA 0 a 3, CPUs 0 a 63
  • Grupo 1: Nós NUMA 4 a 7, CPUs 64 a 127
  • Grupo 2: Nós NUMA 8 a 12, CPUs 128 a 191
  • Grupo 3: Nós NUMA 13 a 16, CPUs 192 a 255

a. Configurando a afinidade para todas as CPUs nos grupos 0 e 2

O exemplo a seguir define afinidade para todas as CPUs nos grupos 0 e 2.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=0 TO 63, 128 TO 191;

B. Configurando a afinidade para todas as CPUs nos nós NUMA 0 e 7

O exemplo a seguir define a afinidade de CPU para nós 0 e 7.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY NUMANODE=0, 7;

C. Definindo a afinidade para as CPUs 60 a 200

O exemplo a seguir define a afinidade para as CPUs 60 a 200.

ALTER SERVER CONFIGURATION   
SET PROCESS AFFINITY CPU=60 TO 200;

D. Definindo a afinidade para a CPU 0 em um sistema que tem duas CPUs

O exemplo a seguir define a afinidade para CPU=0 em um computador que tem duas CPUs. Antes de a instrução seguinte ser executada, a máscara de bits de afinidade interna é 00.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0;

E. Definindo a afinidade como AUTO

O exemplo a seguir define a afinidade como AUTO.

ALTER SERVER CONFIGURATION  
SET PROCESS AFFINITY CPU=AUTO;

Setting diagnostic log options

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

Os exemplos desta seção mostram como definir os valores para a opção de log de diagnóstico.

a. Iniciando o log de diagnóstico

O exemplo a seguir inicia o log de dados de diagnóstico.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;

B. Interrompendo o log de diagnóstico

O exemplo a seguir interrompe o log de dados de diagnóstico.

ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;

C. Especificando o local dos logs de diagnóstico

O exemplo a seguir define o local dos logs de diagnóstico como o caminho do arquivo especificado.

ALTER SERVER CONFIGURATION  
SET DIAGNOSTICS LOG PATH = 'C:\logs';

D. Especificando o tamanho máximo de cada log de diagnóstico

O exemplo a seguir define o tamanho máximo de cada log de diagnóstico como 10 megabytes.

ALTER SERVER CONFIGURATION   
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;

Configurando as propriedades do cluster de failover

Aplica-se a: SQL Server (começando com SQL Server 2012 (11.x)).

O exemplo a seguir ilustra a configuração dos valores das propriedades do recurso de cluster de failover do SQL Server.

a. Especificando o valor da propriedade HealthCheckTimeout

O exemplo a seguir define a opção HealthCheckTimeout como 15.000 milissegundos (15 segundos).

ALTER SERVER CONFIGURATION   
SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000;

B. Como alterar o contexto do cluster de uma réplica de disponibilidade

O exemplo a seguir altera o contexto do cluster HADR da instância do SQL Server. Para especificar o cluster WSFC de destino, clus01, o exemplo especifica o nome de objeto completo do cluster, clus01.xyz.com.

ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT = 'clus01.xyz.com';

Definindo opções de extensão do pool de buffers

a. Definindo a opção de extensão do pool de buffers

Aplica-se a: SQL Server (começando com SQL Server 2014 (12.x)).

O exemplo a seguir habilita a opção de extensão do pool de buffers, e especifica um nome e um tamanho de arquivo.

ALTER SERVER CONFIGURATION   
SET BUFFER POOL EXTENSION ON  
    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 50 GB);

B. Modificando parâmetros da extensão do pool de buffers

O exemplo a seguir modifica o tamanho de um arquivo de extensão do pool de buffers. A opção de extensão do pool de buffers deve ser desabilitada antes que qualquer parâmetro seja modificado.

ALTER SERVER CONFIGURATION   
SET BUFFER POOL EXTENSION OFF;  
GO  
EXEC sp_configure 'max server memory (MB)', 12000;  
GO  
RECONFIGURE;  
GO  
ALTER SERVER CONFIGURATION  
SET BUFFER POOL EXTENSION ON  
    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 60 GB);  
GO

Como definir opções do Banco de Dados em Memória

Aplica-se a: SQL Server (começando com SQL Server 2019 (15.x)).

a. Habilitar todos os recursos do Banco de Dados em Memória com as opções padrão

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED ON;
GO

B. Habilitar os metadados do tempdb com otimização de memória usando o pool de recursos padrão

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
GO

C. Habilitar os metadados do tempdb com otimização de memória com um pool de recursos definido pelo usuário

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = 'pool_name');
GO

D. Habilitar o pool de buffers híbrido

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;
GO

Consulte Também

Soft-NUMA (SQL Server)
Alterar o contexto do cluster HADR da instância de servidor (SQL Server)
sys.dm_os_schedulers (Transact-SQL)
sys.dm_os_memory_nodes (Transact-SQL)
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)
Extensão do pool de buffers