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

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

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 MB. Por padrão, o SQL Server pode alterar seus requisitos de memória dinamicamente com base nos recursos do sistema disponíveis.

Observação

Configurar a memória máxima do servidor com o valor mínimo pode reduzir drasticamente o desempenho do SQL Server e até mesmo impedir sua inicialização. Se você não puder iniciar o SQL Server após alterar essa opção, inicie-o usando a opção de inicialização –f e redefina memória máxima do servidor com seu valor anterior. Para obter mais informações, consulte Opções de inicialização do serviço Mecanismo de Banco de Dados.

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. O SQL Server adiciona memória apenas quando sua carga de trabalho exige mais memória. Um servidor em repouso não aumenta o tamanho de seu espaço de endereço virtual.

Veja o exemplo B para uma consulta retornar a memória usada atualmente. A max server memory controla a alocação de memória de SQL Server, incluindo o conjunto de buffer, compilar memória, todos os caches, concessões de memória qe, memória de gerenciador de bloqueio e memória clr (basicamente qualquer administrador de memória encontrado em sys.dm_os_memory_clerks). Memória para pilhas de thread, pilhas de memória, fornecedores de servidor vinculados diferentes de SQL Server e qualquer memória alocada por um não SQL Server DLL não são controladas pelo max server memory.

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.

E recomendável permitir que o SQL Server use memória dinamicamente, porém, 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 e quaisquer outras instâncias do SQL Server (e outros usos do sistema, caso o computador não esteja totalmente dedicado ao SQL Server). Essa diferença é a quantidade máxima de memória que você pode atribuir ao SQL Server.

Configurando as opções de memória manualmente

Defina memória mínima do servidor e memória máxima do servidor para abranger um intervalo de valores de memória. Esse método é útil para os administradores de sistema ou de banco de dados para configurar uma instância do SQL Server com os requisitos de memória de outros aplicativos executados no mesmo computador.

Use memória mínima do servidor 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. O 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.

Observação
Não há garantia de que o SQL Server aloque a quantidade de memória especificada em memória mínima do servidor. Se a carga do servidor nunca exigir a alocação da quantidade de memória especificada em memória mínima do servidor, o SQL Server será executado com menos memória.

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

Use as duas opções de memória de 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 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

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

Para otimizar o uso de memória do sistema para o SQL Server, limite a quantidade de memória usada pelo sistema para cache de arquivo. 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 em seu sistema operacional

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

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

3. Se Maximizar transferência de dados para aplicativos de rede estiver selecionada, escolha qualquer outra opção, clique em OK e 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 Bloquear Páginas na Memória do SQL Server são definidas como ON em instâncias de 32 e 64 bits do SQL Server 2012 Standard Edition e com um valor mais alto quando a conta com privilégios para executar o sqlservr.exe recebe o direito de usuário "LPIM" (Páginas Bloqueadas em Memória) 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 do SQL Server, remova o direito de usuário “Páginas Bloqueadas em Memória” para a conta de inicialização do SQL Server.

Para desabilitar Bloquear Páginas na Memória

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 em um sistema operacional de 64 bits seu espaço de endereço virtual de usuário disponível é de 4 GB completos.

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, só será possível alterá-las 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. O 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

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

2 WOW64 (Windows on Windows 64) é um modo em que o SQL Server de 32 bits é executado em um sistema operacional de 64 bits. Para obter mais informações, visite a 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 alocação de memória atual

A consulta a seguir retorna informações sobre a memória atual alocada.

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

Referência

RECONFIGURE (Transact-SQL)

sp_configure (Transact-SQL)

Conceitos

Monitorar e ajustar o desempenho

Opções de configuração de servidor