Opções de configuração do Server Memory

Use as duas opções de memória do servidor, memória mínima do servidor e memória máxima do servidor, para reconfigurar a quantidade de memória (em megabytes) gerenciada pelo Gerenciador de Memória SQL Server para um processo de SQL Server usado por uma instância de SQL Server.

A configuração padrão para memória mínima do servidor é 0 e a configuração padrão para memória máxima do servidor é 2147483647 MB. Por padrão, o SQL Server pode alterar os requisitos de memória de maneira dinâmica com base nos recursos disponíveis do sistema.

Quando o SQL Server está usando memória dinamicamente, ele consulta o sistema periodicamente para determinar a quantidade de memória livre. Manter essa memória livre impede a paginação do SO (sistema operacional). Se menos memória estiver livre, o SQL Server liberará memória para o SO. Se houver mais memória livre, o SQL Server poderá alocar mais memória. SQL Server adiciona memória apenas quando sua carga de trabalho exige mais. Um servidor em repouso não aumenta o tamanho de seu espaço de endereço virtual.

Veja o exemplo B para uma consulta que retorna a memória usada atualmente. max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essencialmente any memory clerk found in sys.dm_os_memory_clerks). Memória para pilhas de threads, heaps de memória, provedores de servidor vinculados diferentes de SQL Server e qualquer memória alocada por uma DLL não SQL Server não são controladas pela memória máxima do servidor.

O SQL Server usa a API de notificação de memória QueryMemoryResourceNotification para determinar quando o Gerenciador de Memória do SQL Server pode alocar e liberar memória.

É recomendável permitir que SQL Server usem a memória dinamicamente; no entanto, você pode definir as opções de memória manualmente e restringir a quantidade de memória que SQL Server pode acessar. Antes de definir a quantidade de memória para SQL Server, determine a configuração de memória apropriada subtraindo, da memória física total, a memória necessária para o sistema operacional e quaisquer outras instâncias de SQL Server (e outros usos do sistema, se o computador não estiver totalmente dedicado a SQL Server). Essa diferença é a quantidade máxima de memória que você pode atribuir a SQL Server.

Configurando as opções de memória manualmente

As opções min server memory e max server memory do servidor podem ser definidas para abrangerem um intervalo de valores de memória. Esse método é útil para os administradores de banco de dados ou de sistemas configurarem uma instância do SQL Server em conjunto com os requisitos de memória de outros aplicativos ou de outras instâncias do SQL Server executadas no mesmo host.

Use min_server_memory para garantir uma quantidade mínima de memória disponível para o Gerenciador de Memória do SQL Server em uma instância do SQL Server. SQL Server não alocará imediatamente a quantidade de memória especificada em memória mínima do servidor na inicialização. Porém, depois que 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 seja reduzido. Por exemplo, quando várias instâncias do SQL Server puderem existir simultaneamente no mesmo host, defina o parâmetro min_server_memory em vez do max_server_memory com a finalidade de reservar memória para uma instância. Além disso, a configuração de um valor de min_server_memory é essencial em um ambiente virtualizado para garantir que a pressão de memória do host subjacente não tente desalocar a memória do pool de buffers em uma VM (máquina virtual) do SQL Server convidada além do que for necessário para se obter um desempenho aceitável.

Utilize max_server_memory para garantir que o sistema operacional não experimente uma pressão de memória prejudicial. Para definir a configuração max server memory, monitore o consumo geral do processo do SQL Server para determinar os requisitos de memória. Para ser mais preciso com esses cálculos para uma única instância:

• Da memória total do SO, reserve de 1 GB a 4 GB para o sistema operacional em si.
• Em seguida, subtraia o equivalente a possíveis alocações de memória SQL Server fora do controle máximo de memória do servidor, que é composto pelo tamanho da pilha ¹ * threads de trabalho máximo calculados ² + -g parâmetro de inicialização ³ (ou 256 MB por padrão, se -g não estiver definido). O que sobrar deve ser a configuração max_server_memory para a instalação de uma instância única.

¹ 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.

³ Consulte a página da documentação em Opções de inicialização do serviço Mecanismo de Banco de Dados para obter informações sobre o parâmetro de inicialização -g. Aplicavel apenas a SQL Server de 32 bits (SQL Server 2005 a SQL Server 2014).

Como configurar opções de memória no SQL Server Management Studio

Use as duas opções de memória de servidor, min server memory e max server memory, 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. Por padrão, o SQL Server pode alterar seus requisitos de memória dinamicamente com base nos recursos do sistema disponíveis.

Procedimento para configurar uma quantidade fixa de memória

Para definir uma quantidade fixa de memória:

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

2. Clique no nó Memória .

3. Em Opções de Memória do Servidor, insira a quantidade desejada para Memória mínima do servidor e Memória máxima do servidor.

   Use as configurações padrão para permitir que o SQL Server altere seus requisitos de memória de forma dinâmica com base nos recursos disponíveis do sistema. A configuração padrão de memória mínima do servidor é 0 e a configuração padrão de memória máxima do servidor é 2147483647 megabytes (MB).

Maximizar a taxa de transferência de dados para aplicativos de rede

Para otimizar o uso de memória do sistema para SQL Server, você deve limitar a quantidade de memória usada pelo sistema para cache de arquivos. Para limitar o cache do sistema de arquivos, verifique se a opção Maximizar taxa de transferência de dados para compartilhamento de arquivos não está selecionada. É possível especificar o menor cache do sistema de arquivos com a seleção de Minimizar a memória usada ou Equilíbrio.

Para verificar a configuração atual de seu sistema operacional

1. Clique em Iniciar, clique em Painel de Controle, clique duas vezes em Conexões de Redee duas vezes em Conexão de Área Local.

2. Na guia Geral clique em Propriedades, selecione Redes Microsoft de Compartilhamento de Arquivos e Impressorase clique em Propriedades.

3. Se Maximizar transferência de dados para aplicativos de rede estiver selecionada, escolha qualquer outra opção, clique em OKe feche o restante das caixas de diálogo.

Bloquear páginas na memória

Essa política do Windows determina quais contas podem usar um processo para manter dados na memória física, impedindo o sistema de paginar os dados para a memória virtual em disco. 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 SQL Server Bloquear Páginas na Memória é definida como ON em instâncias de 32 bits e 64 bits do SQL Server edição Standard 2014 e superior quando a conta com privilégios para executar sqlservr.exe tiver recebido o direito de usuário "Páginas Bloqueadas na Memória" (LPIM) do Windows. Em versões anteriores do SQL Server, a definição da opção Bloquear Páginas para uma instância de 32 bits do SQL Server requer que a conta com privilégios para executar o sqlservr.exe tenha o direito de usuário LPIM e a opção de configuração 'awe_enabled' seja definida como ON.

Para desabilitar a opção Bloquear Páginas na Memória para SQL Server, remova o direito de usuário "Páginas Bloqueadas na Memória" da conta de inicialização do SQL Server.

Para desabilitar Bloquear Páginas na Memória

Para desabilitar a opção bloquear páginas na memória:

1. No menu Iniciar , clique em Executar. Na caixa Abrir , digite gpedit.msc.

   A caixa de diálogo Política de Grupo é aberta.

2. No console Política de Grupo , expanda Configuração do Computadore então expanda Configurações do Windows.

3. Expanda Configurações de Segurançae então expanda Políticas Locais.

4. Selecione a pasta Atribuição de direitos de usuários .

   As políticas serão exibidas no painel de detalhes.

5. No painel, clique duas vezes em Bloquear páginas na memória.

6. Na caixa de diálogo Configuração da Política de Segurança Local , selecione a conta com privilégios para executar o sqlservr.exe e clique em Remover.

Gerenciador de Memória Virtual

Os sistemas operacionais de 32 bits fornecem acesso a 4 GB de espaço de endereço virtual. Os 2 GB de memória virtual são privativos por processo e estão disponíveis para uso do aplicativo. 2 GB são reservados para uso do sistema operacional. Todas as edições do sistema operacional incluem um comutador que pode fornecer aos aplicativos acesso a até 3 GB de espaço de endereço virtual, com o limite de 1 GB para o sistema operacional. Para obter mais informações sobre como usar a configuração de comutador de memória, consulte a documentação do Windows sobre como ajustar 4 gigabytes (4 GT). Quando o SQL Server de 32 bits está em execução no sistema operacional de 64 bits, o espaço de endereço virtual disponível pelo usuário é de 4 GB completo.

As regiões confirmadas de espaço de endereço são mapeadas para a memória física disponível pelo VMM (Gerenciador de Memória Virtual) do Windows.

Para obter mais informações sobre a quantidade de memória física com suporte de diferentes sistemas operacionais, consulte a documentação do Windows "Limites de memória para versões do Windows".

Os sistemas de memória virtual permitem exceder o uso da memória física, de modo que a taxa entre memória física e virtual pode exceder 1:1. Como resultado, programas maiores podem ser executados em computadores com várias configurações de memória física. No entanto, usar significativamente mais memória virtual do que a média combinada de conjuntos de trabalho de todos os processos pode provocar desempenho inadequado.

As opções memória mínima do servidor e memória máxima do servidor são opções avançadas. Se você estiver usando o procedimento armazenado no sistema sp_configure para alterar essas configurações, será possível alterá-las apenas quando show advanced options estiver definida como 1. Essas configurações entram em vigor imediatamente sem a reinicialização do servidor.

Executando várias instâncias do SQL Server

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

• Use max server memory para controlar o uso de memória. 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 sendo executadas poderá utilizar a memória livre restante.

• Use min server memory para controlar o uso de memória. 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 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.

• Não fazer nada (não recomendado). As primeiras instâncias apresentadas com uma carga de trabalho tenderão 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.

Fornecendo a quantidade máxima de memória para o SQL Server

*/3gb é um parâmetro de inicialização do sistema operacional. Para obter mais informações, visite o Biblioteca MSDN.

**WOW64 (Windows no Windows 64) é um modo no qual SQL Server de 32 bits é executado em um sistema operacional de 64 bits. Para obter mais informações, visite o Biblioteca MSDN.

Exemplos

Exemplo A

O exemplo a seguir define a opção max server memory como 4 GB:

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

Exemplo B. Determinando 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 Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;  

Consulte Também

Monitorar e ajustar o desempenho
RECONFIGURE (Transact-SQL)
Opções de configuração do servidor (SQL Server)
sp_configure (Transact-SQL)