Guia de arquitetura de gerenciamento de memória

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

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 superiores). 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 no sistema. Para obter informações sobre como permitir que o SQL Server use o AWE, confira Habilitar a a opção Bloquear páginas na memória.

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 alternará para o uso de memória convencional e, em casos de esgotamento da memória do SO, o erro 17890 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 (11.x)

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

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.

Importante

Examine com atenção suas configurações atuais de memória máxima do servidor (MB) e memória mínima do servidor (MB) depois de fazer upgrade para o SQL Server 2012 (11.x) e posteriores. Isso é necessário porque, do SQL Server 2012 (11.x) em diante, essas configurações agora incluem e consideram mais alocações de memória em comparação com as versões anteriores. Essas alterações se aplicam às versões de 32 bits e 64 bits do SQL Server 2012 (11.x) e do SQL Server 2014 (12.x) e às versões de 64 bits do SQL Server 2016 (13.x) e posteriores.

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):

Tipo de alocação de memória SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) e SQL Server 2008 R2 (10.50.x) Do SQL Server 2012 (11.x) em diante
Alocações de uma página Sim Sim, consolidadas em alocações de página de “qualquer tamanho”
Alocações de várias páginas Não Sim, consolidadas em alocações de página de “qualquer tamanho”
Alocações de CLR Não Sim
Memória de pilhas de thread Não Não
Alocações diretas do Windows Não Não

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 a configuração 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 o uso 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) excederá 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

Alterações em memory_to_reserve começando com o SQL Server 2012 (11.x)

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:

Tipo de alocação de memória SQL Server 2005 (9.x), SQL Server 2008 (10.0.x) e SQL Server 2008 R2 (10.50.x) Do SQL Server 2012 (11.x) em diante
Alocações de uma página Não Não, consolidados em alocações de páginas de “qualquer tamanho”
Alocações de várias páginas Sim Não, consolidados em alocações de páginas de “qualquer tamanho”
Alocações de CLR Sim Yes
Memória de pilhas de thread Sim Yes
Alocações diretas do Windows Sim Yes

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.

Memória máxima 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 de execução de consulta, memória do gerenciador de bloqueio e memória CLR1 (essencialmente, qualquer administrador de memória encontrado em sys.dm_os_memory_clerks).

1 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 1, o CLR 2, 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.

1 Confira o artigo 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. Estes são os tamanhos de pilha do SQL Server:

Arquitetura do SQL Server Arquitetura do SO Tamanho da pilha
x86 (32 bits) x86 (32 bits) 512 KB
x86 (32 bits) x64 (64 bits) 768 KB
x64 (64 bits) x64 (64 bits) 2048 KB
IA64 (Itanium) IA64 (Itanium) 4096 KB

2 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 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).

Observação

Como um processo, o SQL Server adquire mais memória do que a especificada pela opção max server memory (MB). Os componentes internos e externos podem alocar memória fora do pool de buffers, o que consome memória adicional, mas a memória alocada ao pool de buffers normalmente ainda representa a parte maior da memória consumida pelo SQL Server.

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 nunca consegue atingir a 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 outros aplicativos são interrompidos ou iniciados com frequência, a alocação e a desalocação de memória pela instância do SQL Server poderão reduzir as inicializações dos outros aplicativos. Além disso, se o SQL Server for um dos vários aplicativos de servidor em execução em um único computador, os administradores de 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.

Importante

Não defina a opção de configuração de servidor memória mínima por consulta com um valor muito alto, especialmente em sistemas muito ocupados, porque isso poderia levar a:

  • Maior competição por recursos de memória.
  • Redução de simultaneidade, aumentando a quantidade de memória para cada consulta única mesmo se a memória necessária em runtime é menor que essa configuração.

Para obter recomendações sobre como usar essa configuração, consulte Configurar a opção de configuração de servidor min memory per query.

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 hash ou de classificação, essas 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 um 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 um 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 serã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.

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 do disco de banco de dados no cache do buffer e gravando páginas modificadas de volta no 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 apenas 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 compatíveis

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.

E/S de disco

O gerenciador de buffer apenas faz leituras e gravações no banco de dados. Outras operações de arquivo e banco de dados, como abrir, fechar, estender e reduzir são executadas pelos componentes do gerenciador de banco de dados e de arquivos.

As operações de E/S de disco pelo gerenciador de buffer têm as seguintes características:

  • Todas as E/S são executadas de forma assíncrona, o que permite ao thread de chamada continuar o processamento enquanto a operação de E/S acontece em segundo plano.
  • Todas as E/S são emitidas nos threads de chamada, a menos que a opção affinity I/O esteja em uso. A opção affinity I/O mask associa o E/de disco de SQL Server a um subconjunto especificado de CPUs. Em ambientes OLTP (online transactional processing) avançados de SQL Server , esta extensão pode aumentar o desempenho de threads SQL Server que emitem E/S.
  • Várias E/Ss de página são realizadas com E/S de dispersar e reunir, que permite transferir dados para dentro ou para fora de áreas não contíguas de memória. Isso significa que o SQL Server pode preencher ou liberar o cache do buffer rapidamente enquanto evita várias solicitações de E/S físicas.

Solicitações de E/S demoradas

O gerenciador de buffer fornece informações sobre qualquer solicitação de E/S pendente durante pelo menos 15 segundos. Isso ajuda o administrador de sistema a distinguir entre problemas do SQL Server e problemas do subsistema de E/S. A Mensagem de erro 833 é informada e exibida no log de erros do SQL Server como segue:

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

Uma E/S demorada pode ser uma leitura ou uma gravação. Isso não está indicado atualmente na mensagem. Mensagens de E/S demoradas são avisos, não erros. Elas não indicam problemas com o SQL Server, mas com o sistema de E/S subjacente. As mensagens são informadas para ajudar o administrador de sistema a encontrar mais depressa a causa de tempos de resposta insatisfatórios do SQL Server e distinguir problemas que estão fora do controle do SQL Server. Como tal, eles não exigem nenhuma ação, mas o administrador do sistema deve investigar por que a solicitação de E/S demorou tanto e se o tempo é justificável.

Causas de solicitações de E/S demoradas

Uma mensagem de E/S demorada pode indicar que uma E/S está bloqueada permanentemente e nunca será concluída (conhecida como E/S perdida) ou, então, que apenas não foi concluída ainda. Não é possível distinguir qual é o cenário com base na mensagem, embora uma E/S perdida geralmente conduza a um tempo limite de trava.

E/S demorada geralmente indica uma carga de trabalho do SQL Server muito intensa para o subsistema de disco. Um subsistema de disco inadequado pode ser indicado quando:

  • Várias mensagens de E/S demorada são exibidas no log de erros durante uma carga de trabalho pesada do SQL Server.
  • Contadores de monitor de desempenho mostram latências de disco demoradas, filas de disco demoradas ou nenhum tempo ocioso de disco.

E/Ss demoradas também podem ser causadas por um componente no caminho de E/S (por exemplo, driver, controlador ou firmware) que continuamente adia o atendimento de uma solicitação de E/S antiga a favor do atendimento mais próximo à posição atual da cabeça de disco. A técnica comum de processar solicitações em prioridade com base em quais estão mais perto da posição atual do cabeçote de leitura/gravação é conhecida como "busca de elevador". Isso pode ser difícil de corroborar com a ferramenta Monitor de desempenho porque a maioria das E/S está sendo atendida prontamente. As solicitações de E/S demoradas podem ser agravadas por cargas de trabalho que executam grandes quantidades de E/S sequenciais, como backup e restauração, exames de tabela, classificação, criação de índices, carregamentos em massa e anulação de arquivos de saída.

E/Ss demoradas e isoladas que não aparecem relacionadas a quaisquer condições anteriores podem ser causadas por um problema de hardware ou driver. O log de eventos do sistema pode conter um evento relacionado que ajuda a diagnosticar o problema.

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 corte conjuntos de trabalho de processos atualmente em execução, o que pode resultar em um retardamento geral. O SQL Server pode reduzir o destino de confirmação de pool de buffers e passar a cortar 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 page-out da memória alocada atualmente.

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 novas tentativas falharem, o comando falhará com a mensagem de erro 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 posterior. 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.

Observação

Se a configuração de proteção de página for alterada, a configuração nova não afetará imediatamente o banco de dados inteiro. Em vez disso, as páginas adotarão o nível de proteção atual do banco de dados sempre que eles forem gravados posteriormente. Isso significa que o banco de dados pode ser composto de páginas com tipos diferentes de proteção.

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 falha 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 com cada gravação, de modo que seja sempre possível informar quando apenas uma parte dos setores realizou essa operação no disco: se um bit estiver com estado inválido quando a página tiver sido 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, tem 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).

Importante

Quando um banco de dados de usuário ou sistema é atualizado para o SQL Server 2005 (9.x) ou posterior, o valor PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) é mantido. Recomendamos enfaticamente o uso de CHECKSUM. TORN_PAGE_DETECTION pode usar menos recursos, mas fornece um subconjunto mínimo da proteção CHECKSUM.

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 adicional. 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 de aplicativo. Para saber mais, veja Como configurar o SQL Server para usar o Soft-NUMA.

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 partindo 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 de vários threads. Para um acompanhamento correto, os objetos CMemThread dependem de constructos de sincronização (um mutex) para garantir que apenas um thread esteja atualizando informações críticas por vez.

Observação

O tipo de objeto CMemThread é utilizado em toda a base de código do Mecanismo de Banco de Dados para muitas alocações diferentes e pode ser particionado globalmente, por nó ou por CPU.

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 partição é suficiente, mas em alguns cenários isso pode levar à contenção, que pode ser impedida 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.

Observação

Antes do SQL Server 2016 (13.x), o sinalizador de rastreamento 8048 podia ser usado para forçar um PMO baseado em nó a se tornar um PMO baseado em CPU. Do SQL Server 2014 (12.x) SP2 e do SQL Server 2016 (13.x) em diante, esse comportamento é dinâmico e controlado pelo mecanismo.

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 do DMV sys.dm_os_memory_objectscontention_factor, partition_type, exclusive_allocations_count e waiting_tasks_count.

Próximas etapas