Como soluciono problemas de pouca memória liberável na minha instância do RDS para SQL Server?

Breve descrição

O tempo de inatividade inesperado em sua instância é causado por muita pouca memória liberável. Portanto, é importante monitorar o uso da memória na instância e adotar medidas de correção.

Memória liberável é a quantidade de memória (RAM) disponível na instância do RDS. A quantidade total de memória disponível em uma instância do Amazon RDS depende da classe de instância. Por exemplo, a classe de instância db.r5.8xlarge fornece 32 vCPUs e 256 GiB de memória. Se você provisionar uma instância do RDS com a classe de instância db.r5.8xlarge, a memória total na classe da instância (256 GiB) será compartilhada pelo seguinte:

• O sistema operacional
• Processos do Amazon RDS
• O mecanismo de banco de dados
• Tópicos de operador
• Aplicativos do pacote Business Intelligence (SSIS, SSAS, SSRS) e assim por diante.

Para obter mais informações sobre como o SQL Server usa a memória, consulte o guia de arquitetura de gerenciamento de memória no site de documentação da Microsoft.

Resolução

Monitore o uso de memória em sua instância

Métricas do Amazon CloudWatch

Monitore as métricas do Amazon CloudWatch para FreeableMemory para identificar ocorrências de baixa memória. Junto com o FreeableMemory, você pode monitorar o seguinte para identificar um aumento de workload quando a memória disponível é pouca:

• DatabaseConnections
• CPUUtilization
• ReadIOPS
• ReadThroughput
• WriteIOPS
• WriteThroughput

Enhanced Monitoring

O Enhanced Monitoring pode ser ativado com diferentes granularidades, como 1, 5, 10, 15, 30 ou 60 segundos para monitorar as métricas do sistema operacional para o Microsoft SQL Server. É uma boa prática definir a granularidade como 1 ou 5 segundos (o padrão é 60 segundos). Você pode criar alarmes do CloudWatch para monitorar o consumo de memória da sua instância de banco de dados do Amazon RDS para SQL Server usando o Enhanced Monitoring

Solucionar problemas de pouca memória liberável

Para solucionar problemas de pouca memória liberável, faça o seguinte:

Limite a memória que a instância do RDS usa

Limite a memória que a instância do RDS usa definindo a memória máxima do servidor para um valor que não cause pressão de memória em todo o sistema. Você pode determinar o valor máximo da memória do servidor para sua instância usando a seguinte fórmula:

max_server_memory = total_RAM – (1 GB para o sistema operacional + memory_basis_amount_of_RAM_on_the_server)

Total_RAM é igual à memória daclasse de instância em que memory_basis_amount_of_RAM_on_the_server é determinado da seguinte forma:

• Se a RAM no servidor estiver entre 4 GB e 16 GB: deixe 1 GB por 4 GB de RAM. Por exemplo, para um servidor com 16 GB, deixe 4 GB.
• Se a RAM no servidor for superior a 16 GB: deixe 1 GB por 4 GB de RAM até 16 GB e 1 GB por 8 GB de RAM acima de 16 GB.

Por exemplo, se um servidor tiver 64 GB de RAM, o cálculo será o seguinte:

• 1 GB para o sistema operacional
• Até 16 GB de RAM: 16/4 = 4 GB
• RAM restante acima de 16 GB: (64-16)/8 = 6
• Total de RAM para sair: 1 + 4 + 6 = 11 GB
• max_server_memory: 64 – 11 = 53 GB

Observações:

• Se você estiver usando SSIS, SSAS ou SSRS na instância, então max_server_memory deve ser ajustado para acomodar esses componentes.
  Exemplo: você quer usar o SSRS com a instância do RDS. Defina o valor Max memory do SSRS para 10% (porcentagem da memória total da instância de banco de dados). Isso é cerca de 6,4 GiB em uma instância com 64 GiB de memória. O valor max_server_memory deve ser de cerca de 46 GiB (64-11-6,4).
• Após a configuração inicial do max_server_memory, FreeableMemory deve ser monitorado constantemente para decidir se deve aumentar ou diminuir a memória alocada.

Para alterar max_server_memory, configure o valor usando um grupo de parâmetros personalizado. O valor de max_server_memory precisa ser fornecido em MB.

Nota: O parâmetro max_server_memory é um parâmetro dinâmico. Portanto, não é necessário reinicializar para que as alterações entrem em vigor.

Verificar as conexões do banco

Cada conexão de banco de dados feita com a instância requer alguma alocação de memória fora do pool de buffer para segmentos de operador. Portanto, um pico no DatabaseConnections pode causar uma queda na memória liberável.

Verifique se os componentes SSIS, SSAS ou SSRS estão sendo usados na instância de banco de dados

Usando o grupo de opções do Amazon RDS para SQL Server, identifique se os componentes SSIS, SSAS ou SSRS são usados na instância de banco de dados. A memória usada por esses componentes existe fora da configuração max_server_memory. Se você não usar esses recursos, remova-os modificando o grupo de opções. A remoção desses recursos reduz o consumo de memória na instância.

Monitore a instância de banco de dados usando o Performance Insights

Usando o Performance Insights, você pode monitorar a instância de banco de dados para a análise do desempenho do banco. Você pode usar o painel do Performance Insights para monitorar a carga do banco de dados, esperas, consultas, hosts, usuários e assim por diante. O monitoramento deles ajuda a identificar possíveis gargalos que diminuem a velocidade da instância.

Execute manutenção regular na instância de banco de dados

Faça a manutenção regular do índice e mantenha as estatísticas atualizadas. Índices altamente fragmentados podem causar aumento na atividade de E/S, o que leva a um maior consumo de memória. Da mesma forma, estatísticas desatualizadas podem causar estimativas de cardinalidade imprecisas, levando à seleção de um plano de consulta abaixo do ideal.

Observação: é uma boa prática realizar a manutenção de índices e estatísticas fora do horário de pico ou durante uma janela de manutenção.

Monitore a expectativa de vida útil da página e a taxa de acertos do cache de buffer

A Page Life expectancy (PLE - Expectativa de vida útil da página) indica o número de segundos em que uma página permanece no buffer pool sem referências.

A Buffer Cache Hit Ratio (BCHR - Taxa de acertos do cache de buffer) é a porcentagem de solicitações de página atendidas pelas páginas de dados do grupo de buffers.

O monitoramento do PLE e do BCHR identifica a pressão da memória. Para monitorar essas métricas usando o Performance Insights, faça o seguinte:

1. Abra o console do Amazon RDS.
2. Selecione Performance Insights.
3. Selecione a instância do RDS para SQL Server que você deseja monitorar.
4. Defina o intervalo de tempo para o qual você deseja revisar as métricas e, em seguida, selecione Manage Metrics (Gerenciar métricas).
5. Selecione Database Metrics (Métricas do banco de dados), Page Life Expectancy, Buffer Cache Hit Ratio (Expectativa de vida útil da página, Taxa de acertos do cache).

Para um desempenho ideal, os valores dessas métricas devem ser os mais altos possíveis. Você pode monitorar essas métricas usando o Performance Insights. Você pode ver que os valores dessas métricas são consistentemente baixos ao longo de um período de tempo. Se isso ocorrer, ajuste as consultas que acessam dados ou aumente a classe da instância para fornecer mais memória.

Quando há pressão de memória na instância, junto com PLE e BCHR baixos, a espera de PAGEIOLATCH aumenta. Isso significa que o SQL Server está aguardando que uma página seja extraída do disco e carregada na memória. Além disso, a espera de RESOURCE_SEMAPHORE pode ser notada quando as solicitações de memória de uma consulta não podem ser atendidas devido à falta de memória. Isso causa um aumento no uso da CPU porque as páginas de dados não são armazenadas em cache por tempo suficiente na memória. Quando isso ocorre, o SQL Server precisa acessar repetidamente o disco para acessar os dados que causam problemas de desempenho.

Identifique as consultas usando a maioria dos recursos

Usando o Performance Insights, capture as consultas utilizando a maioria dos recursos e ajuste-as para melhorar o desempenho.

Selecione o tamanho de instância correto para workload

A quantidade de memória em uma instância depende do tipo de instância. É importante selecionar uma classe de instância com recursos adequados para que a instância de banco de dados tenha recursos suficientes para workload. Uma instância com menos recursos enfrenta problemas de desempenho, e uma instância superdimensionada desperdiça recursos.

Faça uma linha de base do uso de recursos

Faça uma linha de base do uso de recursos na instância monitorando métricas como FreeableMemory, Page Life Expectancy, Buffer Cache Hit Ratio e assim por diante. Se houver um aumento significativo no volume de dados na instância, aumente o valor de max_server_memory. Certifique-se de aumentar o valor de max_server_memory em proporção à alteração do volume de dados para manter o mesmo nível de desempenho na instância.

Notas: você pode usar as ferramentas nativas do SQL Server, como Relatórios e DMVs, para identificar componentes usando a memória dentro do SQL Server. Usando o SQL Server Management Studio (SSMS), você pode analisar o uso de memória do SQL Server:

1. Abra o SQL Server Management Studio (SSMS) e conecte-se à sua instância do RDS para SQL Server.
2. No Object Explorer, clique com o botão direito no nome do endpoint da instância RDS.
3. Selecione Reports (Relatórios),Standard Reports (Relatórios padrão), Memory Consumption (Consumo de memória).

Além disso, você pode consultar a DMV (Dynamic Management View - Exibição de Gerenciamento Dinâmico) sys.dm_os_memory_clerks para identificar os componentes que usam o máximo de memória dentro do SQL Server.

---