Opções de configuração de memória do servidor

Aplica-se a: SQL Server

A utilização de memória do Mecanismo de Banco de Dados do SQL Server é limitada por um par de configurações, memória mínima do servidor (MB) e memória máxima do servidor (MB). Ao longo do tempo e em circunstâncias normais, o SQL Server tentará reivindicar memória até o limite definido pela memória máxima do servidor (MB).

Observação

Os índices Columnstore: VIsão Geral e os objetos Visão geral do OLTP na memória e cenários de uso têm seus próprios administradores de memória, o que facilita o monitoramento do uso do pool de buffers. Para obter mais informações, confira sys.dm_os_memory_clerks.

Em versões mais antigas do SQL Server, a utilização de memória era virtualmente ilimitada, indicando ao SQL Server que toda a memória do sistema estava disponível para uso. É recomendável, em todas as versões do SQL Server, configurar um limite superior para a utilização de memória do SQL Server configurando a memória máxima do servidor (MB).

• Desde o SQL Server 2019 (15.x), a Instalação do SQL em servidores Windows fornece uma recomendação de memória máxima do servidor (MB) para uma instância autônoma do SQL Server com base em uma porcentagem da memória de sistema disponível no momento da instalação.
• A qualquer momento, você pode reconfigurar os limites de memória (em megabytes) para um processo do SQL Server usado por uma instância do SQL Server por meio das opções de configuração de memória mínima do servidor (MB) e memória máxima do servidor (MB).

Observação

Este guia se refere à instância do SQL Server no Windows. Para obter informações sobre a configuração de memória no Linux, confira Melhores práticas de desempenho e diretrizes de configuração para o SQL Server no Linux e Configuração memory.memorylimitmb.

Recomendações

As configurações padrão e os valores mínimos permitidos para essas opções são:

Opção	Padrão	Mínimo permitido	Recomendadas
memória mínima do servidor (MB)	0	0	0
memória máxima do servidor (MB)	2\.147.483.647 megabytes (MB)	128 MB	75% da memória do sistema disponível não consumida por outros processos, incluindo outras instâncias. Para obter recomendações mais detalhadas, confira memória máxima do servidor.

Com esses limites, o SQL Server pode alterar seus requisitos de memória dinamicamente com base nos recursos do sistema disponíveis. Para obter mais informações, consulte Gerenciamento de memória dinâmica.

• Definir um valor de memória máxima do servidor (MB) muito alto pode fazer com que uma instância do SQL Server tenha que competir por memória com outras instâncias do SQL Server hospedadas no mesmo host.
• No entanto, definir um valor de memória máxima do servidor (MB) muito baixo é uma oportunidade de desempenho perdida e pode causar problemas de desempenho e pressão de memória na instância do SQL Server.
• Definir a memória máxima do servidor com o valor mínimo pode até mesmo impedir que o SQL Server seja iniciado. Se você não conseguir iniciar o SQL Server após alterar essa opção, inicie-o usando a opção de inicialização -f e redefina a memória máxima do servidor (MB) com seu valor anterior. Para obter mais informações, consulte Opções de inicialização do serviço Mecanismo de Banco de Dados.
• Não é recomendável definir a memória máxima do servidor (MB) e a memória mínima do servidor (MB) com valores iguais ou quase iguais.

Observação

A opção de memória máxima do servidor limita apenas o tamanho do pool de buffers do SQL Server. A opção de memória máxima do servidor não limita uma área de memória não reservada restante que o SQL Server deixa para alocações de outros componentes, como procedimentos armazenados estendidos, objetos COM, DLLs não compartilhadas e EXEs.

O SQL Server pode usar a memória dinamicamente. No entanto, você pode definir as opções de memória manualmente e restringir a quantidade de memória que o SQL Server pode acessar. Antes de definir a quantidade de memória para o SQL Server, determine a configuração de memória apropriada subtraindo, da memória física total, a memória necessária para o SO (sistema operacional), as alocações de memória não controladas pela configuração de memória máxima do servidor e qualquer outra instância do SQL Server (e outros usos do sistema, caso o servidor hospede outros aplicativos que consomem memória, incluindo outras instâncias do SQL Server). Essa diferença é a quantidade máxima de memória que você pode atribuir à instância SQL Server atual.

A memória pode ser configurada até o limite de espaço do endereço virtual do processo em todas as edições do SQL Server. Para obter mais informações, consulte Limites de memória para as versões do Windows e do Windows Server.

Memória mínima do servidor

Use a memória mínima do servidor (MB) para garantir uma quantidade mínima de memória disponível para o Gerenciador de Memória do SQL Server.

• SQL Server não alocará imediatamente a quantidade de memória especificada em memória mínima do servidor (MB) na inicialização. No entanto, após o uso de memória atingir esse valor devido à carga do cliente, o SQL Server não poderá liberar memória livre a menos que o valor de memória mínima do servidor (MB) seja reduzido. Por exemplo, quando várias instâncias do SQL Server são instaladas simultaneamente no mesmo servidor, considere definir o parâmetro de memória mínima do servidor (MB) para reservar memória para uma instância.

• Definir um valor de memória mínima do servidor (MB) é essencial em um ambiente virtualizado a fim de garantir que a pressão de memória do host subjacente não tente desalocar memória do pool de buffers em uma VM (máquina virtual) convidada além do que for necessário para obter um desempenho aceitável. O ideal é que instâncias do SQL Server em uma máquina virtual não precisem competir com os processos de desalocação de memória proativos do host virtual.

• Não há nenhuma garantia de que o SQL Server aloque a quantidade de memória especificada em memória mínima do servidor (MB). Se a carga do servidor nunca exigir a alocação da quantidade de memória especificada em memória mínima do servidor (MB), o SQL Server usará menos memória.

Memória máxima do servidor

Use a memória máxima do servidor (MB) para garantir que o sistema operacional e outros aplicativos não sofram uma pressão de memória prejudicial proveniente do SQL Server.

• Antes de definir a configuração de memória máxima do servidor (MB), monitore o consumo geral de memória do servidor que hospeda a instância do SQL Server durante a operação normal para determinar a disponibilidade e os requisitos de memória. Para uma configuração inicial ou quando não há oportunidade de coletar o uso de memória do processo do SQL Server ao longo do tempo, use a seguinte abordagem de melhor prática generalizada para configurar a memória máxima do servidor para uma instância:
  • Da memória total do sistema operacional, subtraia o equivalente a possíveis alocações de memória de thread do SQL Server fora do controle de memória máxima do servidor (MB), que é composto pelo tamanho da pilha¹ multiplicado pelos threads de trabalho máximo calculados².
  • Em seguida, subtraia 25% para outras alocações de memória fora do controle máximo de memória do servidor (MB), como buffers de backup, DLLs de procedimento armazenado estendido, objetos criados usando procedimentos de Automação (chamadas sp_OA) e alocações de provedores de servidor vinculados. Essa é uma aproximação genérica e sua quilometragem pode variar.
  • O que sobrar deve ser a configuração de memória máxima do servidor (MB) para a instalação de uma instância.

¹ Consulte o Guia de arquitetura de gerenciamento de memória para obter informações sobre os tamanhos de pilha de thread por arquitetura.

² Consulte a página da documentação sobre como Configurar a opção max worker threads de configuração de servidor para obter informações sobre os threads de trabalho padrão calculados para um determinado número de CPUs de afinidade no host atual.

Definir opções manualmente

As opções de memória mínima do servidor (MB) e memória máxima do servidor (MB) podem ser definidas de modo a abranger um intervalo de valores de memória. Esse método é útil para os administradores de bancos de dados ou de sistemas configurarem uma instância do SQL Server com os requisitos de memória de outros aplicativos ou de outras instâncias do SQL Server executadas no mesmo host.

Usar o Transact-SQL

As opções memória mínima do servidor (MB) e memória máxima do servidor (MB) são opções avançadas. Ao usar o procedimento armazenado do sistema sp_configure para alterar essas configurações, você poderá alterá-las apenas quando mostrar opções avançadas estiver definido como 1. Essas configurações entram em vigor imediatamente sem a reinicialização do servidor. Para obter mais informações, confira sp_configure.

O exemplo a seguir define a opção de memória máxima do servidor (MB) como 12.288 MB ou 12 GB. Embora sp_configure especifique o nome da opção como max server memory (MB), você pode omitir o (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

A consulta a seguir retorna informações sobre os valores configurados atualmente e o valor em uso no momento. Essa consulta retorna resultados independentemente de a opção sp_configure “mostrar opções avançadas” estar habilitada.

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

Use SQL Server Management Studio.

Use memória mínima do servidor (MB) e memória máxima do servidor (MB) para reconfigurar a quantidade de memória (em megabytes) gerenciada pelo Gerenciador de Memória do SQL Server para uma instância do SQL Server.

1. No Pesquisador de Objetos, clique com o botão direito do mouse em um servidor e selecione Propriedades.

2. Selecione a página Memória da janela Propriedades do Servidor. Os valores atuais de Memória mínima do servidor e Memória máxima do servidor são exibidos.

3. Nas Opções de memória do servidor, insira os números desejados para Memória mínima do servidor e Memória máxima do servidor. Para obter recomendações, confira memória mínima do servidor (MB) e memória máxima do servidor (MB) neste artigo.

A seguinte captura de tela demonstra as três etapas:

LPIM (Bloquear páginas na memória)

Os aplicativos baseados no Windows podem usar as APIs do AWE (Address Windowing Extensions) do Windows a fim de alocar e mapear a memória física para o espaço de endereço do processo. A política LPIM do Windows determina as contas que podem acessar a API para manter os dados na memória física, impedindo o sistema de paginar os dados para a memória virtual em disco. A memória alocada por meio do AWE é bloqueada até que o aplicativo a libere ou seja encerrado explicitamente. O uso das APIs do AWE para o gerenciamento de memória no SQL Server de 64 bits também é frequentemente chamado de páginas bloqueadas. O bloqueio de páginas na memória pode manter a resposta do servidor quando ocorre paginação de memória no disco. A opção Bloquear páginas na memória ficará habilitada nas instâncias do SQL Server Standard Edition e superior quando a conta com privilégios para executar sqlservr.exe tiver recebido o direito de usuário Bloquear páginas na memória (LPIM) do Windows.

Para desabilitar a opção Bloquear páginas na memória do SQL Server, remova o direito de usuário Bloquear páginas na memória da conta com privilégios para executar a conta de inicialização sqlservr.exe (a conta de inicialização do SQL Server).

O uso do LPIM não afeta o gerenciamento de memória dinâmica do SQL Server, permitindo que ele seja expandido ou reduzido mediante a solicitação de outros administradores de memória. Ao usar o direito de usuário Bloquear páginas na memória, é recomendável definir um limite superior para a memória máxima do servidor. Para obter mais informações, confira memória máxima do servidor (MB).

LPIM deve ser usado quando houver sinais de que o processo sqlservr está sendo paginado. Nesse caso, o erro 17890 será relatado no Errorlog, semelhante ao exemplo abaixo:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

O uso do LPIM com uma configuração de memória máxima do servidor (MB) definida incorretamente que não leva em consideração outros consumidores de memória no sistema pode causar instabilidade dependendo da quantidade de memória exigida por outros processos ou dos requisitos de memória do SQL Server fora do escopo da memória máxima do servidor (MB). Para obter mais informações, confira memória máxima do servidor. Se o privilégio Bloquear páginas na memória (LPIM) for concedido (em sistemas de 32 bits ou de 64 bits), será altamente recomendável que você defina a memória máxima do servidor (MB) como um valor específico em vez de deixar o padrão de 2.147.483.647 megabytes (MB).

Observação

A partir do SQL Server 2012 (11.x), o Sinalizador de rastreamento 845 não é necessário para que a Standard Edition use páginas bloqueadas.

Habilitar Bloquear páginas na memória

Depois de considerar as informações anteriores, para habilitar a opção Bloquear páginas na memória concedendo o privilégio à conta de serviço para a instância do SQL Server, consulte Habilitar a opção Bloquear páginas na memória (Windows).

Para determinar a conta de serviço para a instância do SQL Server, consulte o SQL Server Configuration Manager ou consulte o service_account de sys.dm_server_services. Para obter mais informações, confira sys.dm_server_services.

Exibir o status de Bloquear páginas na memória

Para determinar se o privilégio de Bloquear páginas na memória foi concedido à conta de serviço da instância do SQL Server, use a consulta a seguir. Essa consulta tem suporte no SQL Server 2016 (13.x) SP1 e posterior.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Os seguintes valores de sql_memory_model_desc indicam o status do LPIM:

• CONVENTIONAL. O privilégio de Bloquear páginas na memória não foi concedido.
• LOCK_PAGES. O privilégio de Bloquear páginas na memória foi concedido.
• LARGE_PAGES. O privilégio Bloquear páginas na memória é concedido no modo Enterprise com o Sinalizador de Rastreamento 834 habilitado. Essa é uma configuração avançada e não recomendada para a maioria dos ambientes. Para obter mais informações e ressalvas importantes, consulte Sinalizador de Rastreamento 834.

Use os seguintes métodos para determinar se a instância do SQL Server está usando páginas bloqueadas:

• A saída da seguinte consulta Transact-SQL indica valores diferentes de zero para locked_page_allocations_kb:

  SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb
  FROM sys.dm_os_memory_nodes omn
  INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id)
  WHERE osn.node_state_desc <> 'ONLINE DAC';
  

• O log de erros atual do SQL Server relata a mensagem Using locked pages in the memory manager durante a inicialização do servidor.

• A seção Gerenciador de Memória da saída DBCC MEMORYSTATUS mostra um valor diferente de zero para o item AWE Allocated.

Várias instâncias do SQL Server

Quando você está executando várias instâncias do Mecanismo de Banco de Dados, há diferentes métodos que você pode usar para gerenciar a memória:

• Utilize a memória máxima do servidor (MB) em cada instância para controlar o uso de memória, conforme detalhado antes. Defina configurações máximas para cada instância, tomando cuidado para que a permissão total não seja maior que a memória física total de sua máquina. É recomendável que cada instância de memória seja proporcional à sua carga de trabalho ou tamanho de banco de dados esperado. Esse método tem a vantagem de que, quando novos processos ou instância forem iniciados, a memória livre estará disponível para eles imediatamente. A desvantagem é que se você não estiver executando todas as instâncias, nenhuma das instâncias executadas poderá utilizar a memória livre restante.

• Utilize a memória mínima do servidor (MB) em cada instância para controlar o uso de memória, conforme detalhado antes. Defina as configurações mínimas de cada instância, de forma que a soma desses mínimos seja entre 1 a 2 GB menor do que a memória física total de sua máquina. Novamente, você pode definir esses mínimos proporcionalmente à carga esperada para a instância. Esse método tem a vantagem de que, se nem todas as instâncias estiverem sendo executadas ao mesmo tempo, as que estiverem sendo executadas poderão usar a memória livre restante. Esse método também é útil quando há outro processo de uso intensivo da memória no computador, de forma que será assegurado que o SQL Server tenha pelo menos uma quantidade razoável de memória. A desvantagem é que quando uma nova instância (ou qualquer outro processo) for iniciada, pode levar algum tempo para que as instâncias liberem memória, principalmente se for necessário gravar páginas modificadas de volta nos respectivos bancos de dados para fazer isso.

• Use a memória máxima do servidor (MB) e a memória mínima do servidor (MB) em cada instância para controlar o uso da memória, observando e ajustando a utilização máxima de cada instância e a proteção mínima da memória dentro de uma ampla gama de potenciais níveis de utilização de memória.

• Não fazer nada (não recomendado). As primeiras instâncias apresentadas com uma carga de trabalho tendem a alocar toda a memória. Instâncias inativas ou instâncias iniciadas posteriormente poderão acabar com apenas uma quantidade mínima de memória disponível. SQL Server não tenta equilibrar o uso de memória em instâncias. No entanto, todas as instâncias responderão aos sinais de Notificação de Memória do Windows para ajustar o tamanho de sua superfície de memória. O Windows não balanceia a memória entre aplicativos com a API de Notificação de Memória. Ele simplesmente fornece um feedback global da disponibilidade da memória no sistema.

É possível alterar essas configurações sem reinicializar as instâncias, para que você possa testar facilmente para encontrar as melhores configurações para seu padrão de uso.

Exemplos

a. Definir a opção de memória máxima do servidor como 4 GB

O exemplo a seguir define a opção de memória máxima do servidor (MB) como 4096 MB ou 4 GB. Embora sp_configure especifique o nome da opção como max server memory (MB), você pode omitir o (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Isso gerará uma instrução semelhante a Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install.. O novo limite de memória entrará em vigor imediatamente após a execução de RECONFIGURE. Para obter mais informações, confira sp_configure.

B. Determinar a alocação de memória atual

A instrução a seguir retorna informações sobre a memória alocada atualmente.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

C. Exibir o valor de max server memory (MB)

A consulta a seguir retorna informações sobre o valor configurado atualmente e o valor em uso. Essa consulta retorna resultados independentemente de a opção sp_configure “mostrar opções avançadas” estar habilitada.

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

Conteúdo relacionado

• Guia de arquitetura de gerenciamento de memória
• Monitorar e ajustar o desempenho
• RECONFIGURE (Transact-SQL)
• Opções de configuração do servidor (SQL Server)
• sp_configure (Transact-SQL)
• Opções de inicialização do Serviço de Mecanismo de Banco de Dados
• Limites de memória para as versões do Windows e do Windows Server