Guia de arquitetura de gerenciamento de memória

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Analytics Platform System (PDW)

Gerenciador de Memória Virtual do Windows

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 compatível com sistemas operacionais diferentes, consulte a documentação do Windows sobre os 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.

Arquitetura de memória do SQL Server

O SQL Server adquire e libera memória dinamicamente conforme necessário. Normalmente, um administrador não precisa especificar a quantidade de memória que deve ser alocada ao SQL Server, embora essa opção exista e seja necessária em alguns ambientes.

Uma das principais metas de design de todo o software de banco de dados é minimizar a E/S de disco devido às leituras e gravações de disco estarem entre as operações que consomem muitos recursos. O SQL Server cria um pool de buffers na memória para manter a leitura de páginas do banco de dados. Grande parte do código do SQL Server é dedicada a minimizar o número de leituras e gravações físicas entre o disco e o pool de buffers. O SQL Server tenta alcançar um equilíbrio entre as duas metas:

• Evitar que o pool de buffers fique tão grande que o sistema inteiro fique com pouca memória.
• Minimizar a E/S física aos arquivos de banco de dados maximizando o tamanho do pool de buffers.

Em um sistema amplamente carregado, algumas consultas grandes que exigem muita memória para serem executadas não conseguem obter a quantidade mínima de memória solicitada e recebem um erro de tempo limite enquanto esperam recursos de memória. Para resolver isso, aumente a Opção query wait. Para uma consulta paralela, considere a redução da Opção de grau máximo de paralelismo.

Em um sistema amplamente carregado sob pressão de memória, as consultas com junção de mesclagem, classificação e bitmap no plano de consulta podem cancelar o bitmap quando as consultas não adquirem a memória mínima necessária para o bitmap. Isso pode afetar o desempenho da consulta e, se o processo de classificação não conseguir se ajustar na memória, poderá aumentar o uso de tabelas de trabalho no banco de dados tempdb, aumentando o tempdb. Para resolver esse problema, adicione memória física ou ajuste as consultas para usar um plano de consulta diferente e mais rápido.

Memória (virtual) convencional

Todas as edições do SQL Server dão suporte à memória convencional em uma plataforma de 64 bits. O processo do SQL Server pode acessar o espaço de endereço virtual até o máximo do sistema operacional na arquitetura x64 (o SQL Server Standard Edition dá suporte a até 128 GB). Com a arquitetura IA64, o limite era de 7 TB (IA64 sem suporte no SQL Server 2012 (11.x) e versões posteriores). Para obter mais informações, confira Limites de memória para o Windows.

Memória AWE (Address Windows Extensions)

Usando o AWE (Address Windowing Extensions) e o privilégio LPIM (bloquear páginas na memória) exigido pelo AWE, você pode manter a maior parte da memória de processo do SQL Server bloqueada na RAM física sob condições de memória virtual baixa. Isso acontece em alocações AWE de 32 bits e 64 bits. O bloqueio da memória ocorre porque a memória AWE não passa pelo Gerenciador de memória virtual no Windows, que controla a paginação da memória. A API de alocação de memória AWE requer o privilégio Bloquear páginas na memória (SeLockMemoryPrivilege). Confira Notas de AllocateUserPhysicalPages. Portanto, o principal benefício de usar a API AWE é manter a maior parte da memória residente na RAM se houver demanda de memória sobre o sistema. Para obter informações sobre como permitir que o SQL Server use o AWE, confira Habilitar a opção Bloquear páginas na memória (Windows).

Se o LPIM for concedido, 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). Para obter mais informações, confira Configuração de servidor de memória do servidor: definir opções manualmente e Bloquear páginas na memória (LPIM).

Se o LPIM não estiver habilitado, o SQL Server alterna para o uso de memória convencional e em casos de esgotamento de memória do sistema operacional, e o erro [MSSQLSERVER_17890] (errors-events/mssqlserver-17890-database-engine-error.md) poderá ser relatado no log de erros. O erro é semelhante ao seguinte exemplo:

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: ##%.

Alterações no gerenciamento de memória por meio do SQL Server 2012

Em versões anteriores do SQL Server, a alocação de memória era feita usando cinco mecanismos diferentes:

• SPA (Alocador de Página Única), incluindo somente as alocações de memória que eram menores ou iguais a 8 KB no processo do SQL Server. As opções de configuração max server memory (MB) e min server memory (MB) determinavam os limites de memória física que o SPA consumia. O Pool de Buffers era simultaneamente o mecanismo do SPA e o maior consumidor de alocações de uma página.
• O Alocador de Várias Páginas (MPA), para as alocações de memória que solicitam mais de 8 KB.
• O Alocador de CLR, incluindo os heaps SQL CLR e suas alocações globais que são criadas durante a inicialização do CLR.
• As alocações de memória para as pilhas de thread no processo do SQL Server.
• As DWA (Alocações Diretas do Windows), para as solicitações de alocação de memória feitas diretamente no Windows. Elas incluem o uso de heap do Windows e as alocações virtuais diretas feitas pelos módulos que são carregados no processo do SQL Server. Exemplos de tais solicitações de alocação de memória incluem as alocações de DLLs de procedimento armazenado estendido, os objetos que são criados usando procedimentos de automação (chamadas sp_OA) e as alocações de provedores de servidor vinculado.

Do SQL Server 2012 (11.x) em diante, alocações de uma página, alocações de várias páginas e alocações de CLR são consolidadas em um Alocador de Páginas de "Qualquer Tamanho" é incluídas nos limites de memória controlados pelas opções de configuração max server memory (MB) e min server memory (MB). Essa alteração forneceu uma capacidade de dimensionamento mais precisa para todos os requisitos de memória que passam pelo gerenciador de memória do SQL Server.

A tabela a seguir indica se um tipo específico de alocação de memória é controlado pelas opções de configuração max server memory (MB) e min server memory (MB):

O SQL Server pode confirmar a memória sobre a configuração de memória máxima do servidor

Do SQL Server 2012 (11.x), o SQL Server pode alocar mais memória do que o valor especificado na configuração máximo de memória do servidor (MB). Esse comportamento pode ocorrer quando o valor de Memória Total do Servidor (KB) já tiver atingido o valor da Memória do Servidor de Destino (KB), conforme especificado pela memória máxima do servidor (MB). Se houver memória contígua livre insuficiente para atender à demanda de solicitações de memória de várias páginas (mais de 8 KB) devido à fragmentação da memória, o SQL Server poderá exceder a alocação em vez de rejeitar a solicitação de memória.

Assim que essa alocação for executada, a tarefa em segundo plano Monitor de Recursos começará a indicar para todos os consumidores de memória liberarem a memória alocada e tentará colocar o valor de Memória Total do Servidor (KB) abaixo da especificação da Memória do Servidor de Destino (KB). Portanto, o uso de memória do SQL Server pode rapidamente exceder a configuração de memória máxima do servidor (MB). Nessa situação, o leitor do contador de desempenho da Memória Total do Servidor (KB) excede as configurações de memória máxima do servidor (MB) e Memória do Servidor de Destino (KB).

Esse comportamento geralmente é observado durante as operações a seguir:

• Consultas grandes do índice columnstore
• Consultas grandes do Modo de lote em rowstore
• Recompilações do índice columnstore, que usam grandes volumes de memória para executar operações de hash e de classificação
• Operações de backup que exigem buffers de memória grandes
• Rastreamento de operações que precisam armazenar grandes parâmetros de entrada
• Grandes solicitações de concessão de memória

Se você observar esse comportamento com frequência, considere usar o Sinalizador de Rastreamento 8121 no sSQL Server 2019 (15.x) para permitir que o Monitor de Recursos limpe mais rapidamente. Desde o SQL Server 2022 (16.x), essa funcionalidade é habilitada por padrão e o sinalizador de rastreamento não tem efeito.

Alterações em memory_to_reserve começando com o SQL Server 2012

Nas versões anteriores do SQL Server (, e ), o gerenciador de memória do deixava de lado uma parte do VAS (espaço de endereço virtual) do processo usado pelo MPA (Alocador de Várias Páginas), pelo Alocador de CLR, pelas alocações de memória das pilhas de threads no processo do SQL Server e pelas DWA (Alocações Diretas do Windows). Esta parte do espaço de endereço virtual também é conhecida como região “Mem-To-Leave” ou como “Pool de buffers sem memória”.

O espaço de endereço virtual reservado para essas alocações é determinado pela opção de configuração memory_to_reserve. O valor padrão que o SQL Server usa é 256 MB.

Uma vez que o alocador de página de “qualquer tamanho” também controla as alocações maiores que 8 KB, o valor memory_to_reserve não inclui as alocações de várias páginas. Exceto por essa alteração, tudo permanece igual com esta opção de configuração.

A tabela a seguir indica se um tipo específico de alocação de memória se encaixa na região memory_to_reserve do espaço de endereço virtual para o processo do SQL Server:

Gerenciamento de memória dinâmica

O comportamento de gerenciamento de memória padrão do Mecanismo de Banco de Dados do SQL Server é adquirir a quantidade de memória necessária sem provocar escassez de memória no sistema. O Mecanismo de Banco de Dados do SQL Server faz isto usando as APIs de Notificação de memória no Microsoft Windows.

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. Um servidor em repouso não aumenta o tamanho de seu espaço de endereço virtual. Se você notar que o Gerenciador de Tarefas e o Monitor de Desempenho mostram uma redução constante na memória disponível quando o SQL Server está usando o gerenciamento de memória dinâmica, esse é o comportamento padrão e não deve ser percebido como uma perda de memória.

Opções de configuração de memória do servidor controla a alocação de memória do SQL Server, memória de compilação, todos os caches (incluindo o pool de buffer), concessões de memória para execução de consulta, memória do gerenciador de bloqueio e memória CLR¹ (basicamente, qualquer administrador de memória encontrado em sys.dm_os_memory_clerks).

¹ A memória do CLR é gerenciada em alocações de max_server_memory do SQL Server 2012 (11.x) em diante.

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;

Tamanhos da pilha

A memória para as pilhas de thread ¹, o CLR ², os arquivos .dll de procedimento estendido, os provedores OLE DB referenciados por consultas distribuídas, os objetos de automação referenciados nas instruções do Transact_SQL e qualquer memória alocada por uma DLL não SQL Server não são controlados pela opção max server memory.

¹ Confira o artigo sobre como Configurar a opção max worker threads (opção de configuração do 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. Estes são os tamanhos de pilha do SQL Server:

² A memória do CLR é gerenciada em alocações de max_server_memory do SQL Server 2012 (11.x) em diante.

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.

Quando o SQL Server é iniciado, ele computa o tamanho do espaço de endereço virtual do pool de buffers baseado em diversos parâmetros, como quantidade de memória física no sistema, número de threads de servidor e vários parâmetros de inicialização. O SQL Server reserva a quantidade computada do seu espaço de endereço virtual de processo do pool de buffers, mas adquire (confirma) somente a quantidade exigida da memória física para a carga atual.

A instância continua adquirindo memória conforme necessário para atender a carga de trabalho. À medida que mais usuários se conectam e executam consultas, o SQL Server adquire mais memória física sob demanda. Uma instância do SQL Server continua adquirindo memória física até alcançar o destino de alocação max server memory (MB) ou o SO indicar que não há mais um excesso de memória livre; ela libera memória quando tem mais do que a configuração min server memory e o SO indica que há uma escassez de memória livre.

Conforme são iniciados outros aplicativos em um computador que está executando uma instância do SQL Server, eles consomem memória e a quantidade de memória física livre reduz o destino do SQL Server. A instância do SQL Server ajusta seu consumo de memória. Se outro aplicativo for interrompido e, com isso, houver mais memória disponível, a instância do SQL Server aumentará o tamanho de sua alocação de memória. O SQL Server pode liberar e adquirir vários megabytes de memória por segundo, permitindo o ajuste rápido às mudanças na alocação de memória.

Efeitos de memória mínima e máxima do servidor

As opções de configuração min server memory e max server memory estabelecem limites superiores e inferiores à quantidade de memória usada pelo pool de buffers e por outros caches do Mecanismo de Banco de Dados do. O pool de buffers não adquire imediatamente a quantidade de memória especificada na min server memory. O pool de buffers é iniciado apenas com a memória exigida para inicialização. Conforme a carga de trabalho do Mecanismo de Banco de Dados do SQL Server aumenta, ele continua adquirindo a memória exigida para oferecer suporte à carga de trabalho. O pool de buffers não libera a memória adquirida até atingir a quantidade especificada na min server memory. Quando a min server memory é atingida, o pool de buffers usa o algoritmo padrão para adquirir e liberar memória, conforme necessário. A única diferença é que o pool de buffers nunca cancela sua alocação de memória abaixo do nível especificado na min server memory, e nunca adquire mais memória que o nível especificado na max server memory (MB).

A quantidade de memória adquirida pelo Mecanismo de Banco de Dados do SQL Server é completamente dependente da carga de trabalho colocada na instância. Uma instância do SQL Server que não está processando muitas solicitações pode nunca alcançar o valor especificado por min server memory.

Se o mesmo valor for especificado para a min server memory e a max server memory (MB), quando a memória alocada ao Mecanismo de Banco de Dados do SQL Server atingir o valor, o Mecanismo de Banco de Dados do SQL Server interromperá a liberação e a aquisição dinamicamente para o pool de buffers.

Se uma instância do SQL Server estiver sendo executada em um computador em que outras aplicações são interrompidas ou iniciadas com frequência, a alocação e a desalocação de memória pela instância do SQL Server poderão atrasar os tempos de inicialização de outras aplicações. Além disso, se o SQL Server for uma das várias aplicações de servidor em execução em um único computador, os administradores do sistema poderão precisar controlar a quantidade de memória alocada ao SQL Server. Nesses casos, você pode usar as opções min server memory e max server memory (MB) para controlar a quantidade de memória que o SQL Server pode usar. As opções min server memory e max server memory são especificadas em megabytes. Para saber mais e obter recomendações sobre como definir essas configurações de memória, confira Opções de configuração de memória do servidor.

Memória usada por especificações de objetos do SQL Server

A lista a seguir descreve a quantidade aproximada de memória usada por diferentes objetos no SQL Server. Os valores listados são estimativas e podem variar dependendo do ambiente e de como os objetos são criados:

• Bloquear (conforme mantido pelo Gerenciador de Bloqueios): 64 bytes + 32 bytes por proprietário
• Conexão do usuário: aproximadamente (3 * network_packet_size + 94 KB)

O tamanho do pacote de rede é o tamanho dos pacotes de TDS (esquema de dados de tabela) que são usados para comunicação entre aplicativos e o Mecanismo de Banco de Dados. O tamanho de pacote padrão é 4 KB e é controlado pela opção de configuração tamanho do pacote de rede.

Quando vários conjuntos de resultados ativos estiverem habilitados, a conexão do usuário será de aproximadamente (3 + 3 * num_logical_connections) * network_packet_size + 94 KB.

Efeitos de min memory per query

A opção de configuração min memory per query estabelece a quantidade mínima de memória (em quilobytes) que será alocada para a execução de uma consulta. Isso também é conhecido como a concessão de memória mínima. O início da execução de todas as consultas deve aguardar até que a memória mínima solicitada possa ser protegida ou então até que o valor especificado na opção de configuração de servidor query wait seja excedido. O tipo de espera é acumulado neste cenário é RESOURCE_SEMAPHORE.

Considerações de concessão de memória

Para execução em modo de linha, a concessão de memória inicial não pode ser excedida sob nenhuma condição. Se mais memória do que a concessão inicial for necessária para executar as operações de hash ou classificação, as operações serão despejadas para o disco. Uma operação hash que é despejada tem o suporte de um arquivo de trabalho em tempdb, enquanto uma operação de classificação que é despejada tem o suporte de uma Tabela de trabalho.

Um despejo que ocorre durante uma operação de classificação é conhecido como uma Classe de evento de aviso de classificação. Avisos de classificação indicam que operações de classificação não cabem na memória. Isso não inclui operações de classificação envolvendo a criação de índices, somente operações de classificação em uma consulta (como uma cláusula ORDER BY usada em uma instrução SELECT).

Um despejo que ocorre durante uma operação de hash é conhecido como uma Classe de evento de aviso de hash. Esses avisos ocorrem após uma recursão ou cessação de hash (esgotamento de hash ) ocorrer durante uma operação de hash.

• A recursão de hash ocorre quando a entrada de criação não cabe na memória disponível, ocasionando a divisão da entrada em várias partições que são processadas separadamente. Se qualquer uma dessas partições ainda não couber na memória disponível, será dividida em subpartições, que também são processadas separadamente. Esse processo de divisão continuará até que cada partição caiba na memória disponível ou até que o nível máximo de recursão seja atingido.
• O esgotamento hash ocorre quando uma operação de hashing atinge o nível máximo de recursão e é deslocada para um plano alternativo, de forma a processar os dados particionados restantes. Esses eventos podem causar a redução do desempenho de seu servidor.

Para execução em modo de lote, a concessão de memória inicial pode aumentar dinamicamente até um certo limite interno por padrão. Esse mecanismo de concessão de memória dinâmica é projetado para permitir a execução de operações de hash ou de classificação residentes na memória executadas em modo de lote. Se essas operações ainda não couberem na memória, elas serão despejadas para o disco.

Para obter mais informações sobre modos de execução, consulte Guia da Arquitetura de Processamento de Consultas.

Gerenciamento de buffer

A principal finalidade de um banco de dados do SQL Server é armazenar e recuperar dados, de modo que a intensa E/S de disco é uma característica importante do Mecanismo de Banco de Dados. Como as operações de E/S de disco podem consumir muitos recursos e levar um tempo relativamente longo para terminar, o SQL Server se concentra em tornar a E/S altamente eficiente. O gerenciamento de buffer é um componente fundamental para alcançar essa eficiência. O componente de gerenciamento de buffer consiste em dois mecanismos: o gerenciador de buffer para acessar e atualizar páginas de banco de dados e o cache do buffer (também chamado de pool de buffers) para reduzir a E/S do arquivo de banco de dados.

Para obter uma explicação detalhada da E/S de disco no SQL Server, consulte Fundamentos de E/S do SQL Server.

Como funciona o gerenciamento de buffer

Um buffer é uma página de 8 KB da memória, mesmo tamanho de uma página de dados ou de índice. Portanto, o cache do buffer é dividido em páginas de 8 KB. O gerenciador de buffer gerencia as funções lendo páginas de dados ou de índice dos arquivos em disco do banco de dados para o cache do buffer e gravando páginas modificadas de volta ao disco. Uma página permanece no cache do buffer até que o gerenciador de buffer precise da área de buffer para ler mais dados. Os dados serão gravados no disco somente se forem modificados. Os dados podem ser modificados no cache do buffer várias vezes antes de serem gravados no disco. Para saber mais, veja Lendo Páginas e Gravando Páginas.

Quando o SQL Server é iniciado, ele computa o tamanho do espaço de endereço virtual do cache do buffer com base em vários parâmetros, como quantidade de memória física no sistema, número máximo de threads de servidor configurado e vários parâmetros de inicialização. O SQL Server reserva essa quantidade computada de seu espaço de endereço virtual de processo (chamado de memória cache) do cache de buffers, mas adquire (confirma) somente a quantidade exigida da memória física para a carga atual. Você pode consultar as colunas committed_target_kb e committed_kb na exibição do catálogo sys.dm_os_sys_info para retornar o número de páginas reservado como destino de memória e o número de páginas atualmente confirmado no cache do buffer, respectivamente.

O intervalo entre a inicialização do SQL Server e o momento em que o cache do buffer obtém seu destino de memória é chamado de ramp-up. Nesse momento, as solicitações de leitura preenchem os buffers conforme necessário. Por exemplo, uma solicitação de leitura de página única de 8 KB preenche apenas uma página de buffer. Isso significa que o ramp-up depende do número e do tipo de solicitações do cliente. O ramp-up é expedido ao transformar as solicitações de leitura de página única em solicitações de oito páginas alinhadas (compondo uma extensão). Isso permite ao ramp-up terminar de forma muito mais rápida, especialmente em máquinas com muita memória. Para obter mais informações sobre páginas e extensões, confira o Guia de arquitetura de páginas e extensões.

Como o gerenciador de buffer usa a maior parte da memória no processo do SQL Server, ele coopera com o gerenciador de memória para permitir que outros componentes usem seus buffers. O gerenciador de buffer interage principalmente com os seguintes componentes:

• Resource Manager para controlar o uso de memória global e, em plataformas de 32 bits, controlar o uso de espaço de endereço.
• Gerenciador de banco de dados e SQLOS (SQL Server Operating System) para operações de E/S de arquivo de nível baixo.
• Gerenciador de log para o registro em log write-ahead.

Recursos com suporte

O gerenciador de buffer oferece suporte aos seguintes recursos:

• O gerenciador de buffer reconhece o NUMA (acesso não uniforme à memória por software). São distribuídas páginas de cache do buffer em nós NUMA de hardware, que permitem a um thread acessar uma página de buffer alocada no nó NUMA local em vez de memória externa.

• O gerenciador de buffer é compatível com a Adição de Memória a Quente, que permite aos usuários adicionar memória física sem reiniciar o servidor.

• O gerenciador de buffer é compatível com páginas grandes em plataformas de 64 bits. O tamanho da página é específico para a versão do Windows.

  Observação

  Antes do SQL Server 2012 (11.x), habilitar páginas grandes no SQL Server requer o sinalizador de rastreamento 834.

• O gerenciador de buffer fornece diagnósticos extras que são expostos por meio de exibições de gerenciamento dinâmico. Você pode usar essas exibições para monitorar vários recursos de sistema operacional específicos do SQL Server. Por exemplo, você pode usar a exibição sys.dm_os_buffer_descriptors para monitorar as páginas no cache do buffer.

Detecção de demanda de memória

A demanda de memória é uma condição decorrente da falta de memória e pode resultar em:

• E/S extra (por exemplo, um thread em segundo plano de gravador lento muito ativo)
• Maior taxa de recompilação
• Consultas de execução mais longa (se existirem esperas por concessão de memória)
• Ciclos de CPU adicionais

Essa situação pode ser acionada por causas externas ou internas. As causas externas incluem:

• A RAM (memória física disponível) é insuficiente. Isso faz com que o sistema arrume conjuntos de trabalho de processos atualmente em execução, o que pode resultar em um atraso geral. O SQL Server pode reduzir o destino de confirmação do pool de buffers e passar a arrumar os caches internos com mais frequência.
• A memória de sistema geral disponível (que inclui o arquivo de paginação do sistema) está baixa. Isso pode causar falha em alocações de memória pelo sistema, já que ele não é capaz de realizar a paginação para fora da memória atualmente alocada.

As causas internas incluem:

• Resposta à demanda de memória externa, quando o Mecanismo de Banco de Dados do SQL Server define limites de uso de memória mais baixos.
• As configurações de memória foram reduzidas manualmente reduzindo-se a configuração max server memory.
• Alterações na distribuição de memória de componentes internos entre os vários caches.

O Mecanismo de Banco de Dados do SQL Server implementa uma estrutura dedicada a detectar e manipular a demanda de memória, como parte do seu gerenciamento de memória dinâmica. Essa estrutura inclui a tarefa de fundo chamada Monitor de Recursos. A tarefa de Monitor de Recursos monitora o estado de indicadores de memória interna e externa. Depois que o status de um desses indicadores se altera, ele calcula a notificação correspondente e a difunde. Essas notificações são mensagens internas de cada um dos componentes do mecanismo e armazenadas em buffers de anéis.

Dois buffers de anéis mantêm informações relevantes para o gerenciamento de memória dinâmica:

• O buffer de anéis de Monitor de Recursos, que acompanha a atividade do Monitor de Recursos, por exemplo, se a demanda de memória foi sinalizada ou não. Esse buffer de anel tem informações de status dependendo da condição atual de RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY ou RESOURCE_MEMVIRTUAL_LOW.
• O buffer de anéis do agente de memória, que contém registros de notificações de memória para cada pool de recursos do Resource Governor. Conforme a demanda de memória interna é detectada, a notificação de memória baixa é ativada para os componentes que alocam memória, de modo a disparar ações destinadas a equilibrar a memória entre caches.

Agentes de memória monitoram o consumo de demanda de memória por cada componente e então, com base nas informações coletadas, calculam o valor ideal de memória para cada um desses componentes. Há um conjunto de agentes para cada pool de recursos do Resource Governor. Essas informações são então difundidas para cada um dos componentes que aumentam ou diminuem seu uso conforme necessário.

Para obter mais informações sobre agentes de memória, consulte sys.dm_os_memory_brokers.

Detecção de erro

As páginas de banco de dados podem usar um dentre dois mecanismos opcionais que ajudam a garantir a integridade da página do momento em que é gravada no disco até ser lida novamente: proteção de página interrompida e proteção de soma de verificação. Esses mecanismos permitem um método independente para verificar a exatidão não apenas do armazenamento de dados, mas de componentes de hardware, como controladores, drivers, cabos e até mesmo o sistema operacional. A proteção é adicionada à página um pouco antes da gravação no disco e verificada depois da leitura do disco.

O SQL Server repetirá mais quatro vezes qualquer leitura que falhe com uma soma de verificação, página interrompida ou outro erro de E/S. Se a leitura tiver êxito em qualquer uma das novas tentativas, uma mensagem será gravada no log de erros e o comando que disparou a leitura continuará. Se as tentativas de repetição falharem, o comando falhará com o erro MSSQLSERVER_824.

O tipo de proteção de página usado é um atributo do banco de dados que contém a página. A proteção de soma de verificação é a proteção padrão para bancos de dados criados no SQL Server 2005 (9.x) e versões posteriores. O mecanismo de proteção de página é especificado no momento de criação do banco de dados e pode ser alterado usando ALTER DATABASE SET. Você pode determinar a configuração de proteção da página atual consultando a coluna page_verify_option na exibição do catálogo sys.databases ou na propriedade IsTornPageDetectionEnabled da função DATABASEPROPERTYEX.

Proteção de página interrompida

A proteção de página interrompida, apresentada no SQL Server 2000 (8.x), é principalmente um modo de detectar páginas corrompidas devido a falhas de energia. Por exemplo, uma falta de energia inesperada pode deixar apenas parte de uma página gravada no disco. Quando a proteção de página interrompida é usada, um padrão de assinatura de 2 bits específico para cada setor de 512 bytes na página de banco de dados de 8 KB (quilobytes) é salvo e armazenado no cabeçalho da página do banco de dados, quando a página é gravada em disco.

Quando a página for lida pelo disco, os bits desativados armazenados no cabeçalho da página serão comparados às informações do setor da página real. O padrão de assinatura alterna entre os binários 01 e 10 a cada gravação, de modo que seja sempre possível informar quando apenas uma parte dos setores chegou ao disco: se um bit estiver no estado errado quando a página for lida posteriormente, a página terá sido gravada incorretamente e uma página interrompida será detectada. A detecção de página interrompida usa recursos mínimos; porém, não detecta todos os erros provocados por falhas de hardware de disco. Para obter informações sobre como configurar a detecção de página interrompida, confira Opções ALTER DATABASE SET (Transact-SQL).

Proteção de soma de verificação

A proteção de soma de verificação, apresentada no SQL Server 2005 (9.x), fornece verificação de integridade de dados mais resistente. Uma soma de verificação é calculada para os dados de cada página gravada e armazenada no cabeçalho da página. Sempre que uma página com uma soma de verificação armazenada é lida no disco, o Mecanismo de Banco de Dados recalcula a soma de verificação dos dados na página e gera o erro 824 se a nova soma de verificação for diferente da soma de verificação armazenada. A proteção de soma de verificação pode capturar mais erros que a proteção de página interrompida, porque é afetada por todo byte da página, porém, faz um uso moderadamente intensivo de recursos.

Quando a soma de verificação for habilitada, os erros causados por falta de energia e falha de hardware ou firmware poderão ser detectados sempre que o gerenciador de buffer ler uma página do disco. Para obter informações sobre como configurar a soma de verificação, confira Opções ALTER DATABASE SET (Transact-SQL).

Entender o acesso não uniforme a memória

O SQL Server reconhece o NUMA (acesso não uniforme a memória) e tem um bom desempenho em hardware de NUMA sem configuração especial. Devido ao aumento da velocidade de clock e do número de processadores, fica muito difícil reduzir a latência de memória exigida para usar este poder de processamento extra. Para evitar isto, fornecedores de hardware fornecem caches de L3 grandes, mas esta é apenas uma solução limitada. A arquitetura NUMA oferece uma solução escalonável para esse problema.

O SQL Server foi projetado para tirar proveito de computadores baseados em NUMA sem exigir nenhuma mudança na aplicação. Para obter mais informações, consulte Soft-NUMA (SQL Server).

Partição dinâmica de objetos de memória

Os alocadores de heap, chamados de objetos de memória no SQL Server, permitem que o mecanismo de banco de dados aloque memória a partir do heap. Eles podem ser acompanhados usando a DMV sys.dm_os_memory_objects.

CMemThread é um tipo de objeto de memória thread-safe que permite alocações de memória simultâneas a partir de múltiplos threads. Para um acompanhamento correto, os objetos CMemThread dependem de constructos de sincronização (um mutex) para garantir que um único thread esteja atualizando informações críticas por vez.

No entanto, o uso de mutexes poderá levar à contenção se muitos threads estiverem alocando do mesmo objeto de memória de maneira altamente simultânea. Sendo assim, o SQL Server tem o conceito de PMOs (objetos de memória particionada) e cada partição é representada por um só objeto CMemThread. O particionamento de um objeto de memória é definido estaticamente e não pode ser alterado após a criação. Como os padrões de alocação de memória variam amplamente com base em aspectos como o uso de hardware e de memória, é impossível chegar ao padrão de particionamento perfeito.

Na maioria dos casos, usar uma única partição é suficiente, mas em alguns cenários isso pode levar à contenção, que pode ser evitada apenas com um objeto de memória altamente particionado. Não é desejável particionar cada objeto de memória, pois mais partições podem resultar em outras ineficiências e aumentar a fragmentação de memória.

Começando no SQL Server 2014 (12.x) SP2 e no SQL Server 2016 (13.x), o mecanismo de banco de dados pode detectar dinamicamente a contenção em um objeto CMemThread específico e promover o objeto a uma implementação feita por nó ou por CPU. Uma vez promovido, o PMO permanece promovido até que o processo do SQL Server seja reiniciado. A contenção CMemThread pode ser detectada pela presença de altas esperas de CMEMTHREAD no DMV sys.dm_os_wait_stats e observando as colunas contention_factor, partition_type, exclusive_allocations_count e waiting_tasks_count do DMV sys.dm_os_memory_objects.

Conteúdo relacionado

• Fundamentos de E/S do SQL Server
• Opções de configuração de memória do servidor
• Lendo Páginas
• Gravando Páginas
• Soft-NUMA (SQL Server)
• Requisitos para usar tabelas com otimização de memória
• Solucionar problemas de memória insuficiente ou memória baixa no SQL Server
• Resolver problemas de memória insuficiente