MS SQL Server - Aumente a memória alocada

Question

Meu problema é que o SQL Server leva muito tempo para aumentar seu uso de memória para instâncias com TB equivalente a RAM, enquanto temos esperas intermitentes de MEMORY_ALLOCATION_EXT que tornam nosso processamento lento até que o SQL Server alcance sua memória máxima.

Temos Failover Clustered Instances (FCI) do SQL Server 2019 Enterprise Edition com terabytes de memória nos nós. Em casos de uso normais, permitimos apenas 1 instância do SQL Server por nó e, portanto, definimos a memória máxima do servidor perto de ~ 85% da memória do nó, mas também definimos a memória mínima do servidor relativamente baixa, caso o SQL Server falhe para outro nó e precisa ser colocado online com uma pegada de memória reduzida para continuar.

• Estou bem ciente de que, se eu definir o mínimo de memória mais alto, o SQL Server consumirá a memória de uma só vez. Acontece que o SQL Server não aloca a memória mínima na inicialização.
• Estou ciente de que o SQL Server consumirá dinamicamente mais memória do sistema operacional conforme a necessidade e que, eventualmente, atingirá a memória máxima do servidor.
• Estou ciente de que executar uma grande consulta ou DBCC checkdb que puxa muitos dados forçará o SQL Server a consumir mais memória do sistema operacional.

Existe alguma outra maneira de forçar o SQL Server a aumentar o uso de memória rapidamente?

Answer 1

Estou bem ciente de que, se eu definir o mínimo de memória mais alto, o SQL Server consumirá a memória de uma só vez.

Não, isso não está correto, a memória mínima do servidor não tem nada a ver com a rapidez com que o SQL Server a consumirá, se configurada. Memória mínima do servidor significa a quantidade mínima de memória, se consumida, após a qual o SQL Server não liberará memória abaixo desse valor em condições normais se solicitado. Quando o valor mínimo do servidor é alcançado, o SQL Server não corta seus caches e libera memória abaixo desse valor.

Estou ciente de que o SQL Server consumirá dinamicamente mais memória do sistema operacional conforme a necessidade e que, eventualmente, atingirá a memória máxima do servidor.

Sim correto, apenas adicionar SQl Server em certas condições também pode consumir memória maior que o valor especificado na memória máxima do servidor. Você deve ler o Guia de Gerenciamento de Memória

Existe alguma outra maneira de forçar o SQL Server a aumentar o uso de memória rapidamente?

Existe outra maneira que força o SQL Server a reservar mais memória durante a inicialização e isso é chamado de " páginas grandes ". Este é um recurso da edição empresarial e permite que o SQL Server reserve rapidamente um pouco de memória livre durante a inicialização, a conta de serviço do SQL Server também deve ter bloqueado as páginas no privilégio de memória porque a alocação de páginas grandes é feita pela função VirtualAlloc () do sistema operacional Windows e o sistema operacional deve ter mais de 8 GB de RAM, se essas condições forem atendidas automaticamente A alocação de páginas grandes é ativada. Uma vez habilitado aparecerá como abaixo no log de erros

2009-06-04 12:21:08.16 Server      Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server      Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server      Large Page Allocated: 32MB

O tamanho normal da página de memória é de 4 KB no sistema X64, mas quando a página grande está ativada, o tamanho será de 2 MB. Observe também que LargePageSupport é habilitado e usado pelo mecanismo mesmo se você não habilitar o sinalizador de rastreamento 834. Mas não muita memória é usada para isso e a memória do pool de buffer não é usada, a menos que o sinalizador de rastreamento 834 esteja habilitado. Veja o blog compartilhado.

Answer 2

O SQL Server não usará memória para o pool de buffer (seu maior uso) até que precise carregar páginas do armazenamento porque são referenciadas. A menos que você esteja fazendo algo estranho para tornar a alocação de memória lenta ^[†] , é improvável que seu atraso seja esse, mas apenas lendo os dados que estão sendo alocados para reter. Suspeito que, ao mesmo tempo que essas esperas de alocação de memória, você tem esperas relacionadas a IO correspondentes.

Para forçar o servidor SQL a carregar coisas na memória e, portanto, alocar memória se disponível e necessário, acesse as coisas para que precisem ser carregadas. Depois de iniciar sua instância SQL, execute uma carga de trabalho somente leitura para que o conjunto de trabalho comum normal do seu aplicativo seja carregado no pool de buffer.

Não faça isso apenas SELECT * FROM HugeTableporque é provável que carregue um equilíbrio ruim de dados (não o conjunto de trabalho principal dos aplicativos), então o conjunto de trabalho principal ainda precisará ser carregado do disco / rede no próximo acesso de qualquer maneira, então você terá o mesmo IO atrasos. Além disso, se HugeTablefor realmente grande, o SQL Server pode perceber que nem tudo caberá e não se preocupará em manter uma quantidade significativa dele na memória de qualquer maneira [‡].

^{[†] Executando em uma VM em um host sobrecarregado, então o que parece ser a RAM real é realmente páginas em disco ?!}

^{[‡] Em circunstâncias normais, você não deseja que uma verificação inesperada de tabela / índice em um objeto grande remova todo o resto da memória. Um limite de quanto é mantido de um objeto para uma consulta é uma otimização para tentar impedir que isso aconteça).}