Otimizar o alto consumo de memória em instâncias

Neste documento, explicamos como analisar e otimizar uma instância do Cloud SQL para SQL Server quando é identificada pelo recomendador de instâncias subprovisionadas como tendo alto consumo de memória.

Memória do SQL Server

A memória do SQL Server pode ser dividida em:

Caches

Esses são objetos em um disco que podem ser recarregados, como páginas de bancos de dados e procedimentos armazenados. Como resultado, o SQL Server pode aumentar e diminuir esses objetos com base na utilização da memória. Os caches incluem pools de buffer e planos de cache.

Memória fixa

A memória fixa pode aumentar e diminuir. Só é reduzida quando não está em uso. Por exemplo, quando o número de conexões cai ou o número de consultas em execução diminui. Ela é diferente dos caches. Se não houver memória fixa suficiente, o SQL Server poderá ficar sem memória. A memória fixa inclui memória de conexão e concessões de memória.

Overhead do SQL Server

O overhead do SQL Server inclui linhas de execução e pilhas.

OLTP na memória

OLTP na memória inclui tabelas e grupos de arquivos na memória.

O consumo de memória pelo SQL Server é controlado pela configuração de maximum server memory e memory.memory.limitmb. O parâmetro memory.memory.limitmb é definido automaticamente pelo Cloud SQL.

Para saber mais sobre memory.memory.limitmb, consulte a documentação da Microsoft.

Defina o limite de max server memory como um valor adequado. No SQL Server, se o max server memory não estiver definido, as páginas do banco de dados poderão consumir a maior parte da memória, chegando a 100%. Às vezes, isso pode ser confuso.

Opções de otimização de memória

Para determinar se uma instância precisa de mais ajuste de memória, faça o seguinte:

Defina o valor de max server memory. A recomendação geral é definir o max server memory em cerca de 80% para evitar que o SQL Server consuma toda a memória disponível. Para instâncias com grandes quantidades de memória, 80% pode ser muito baixo e levar ao desperdício de memória.
Monitorar Page life expectancy

Page life expectancy indica o tempo, em segundos, que a página mais antiga permanece no pool de buffer. Esse valor precisa ser maior que 300, conforme recomendado pela Microsoft. Se ela ficar abaixo de 300 de modo consistente, isso pode indicar que a instância está enfrentando uma alta utilização de memória. Execute a consulta a seguir para monitorar Page life expectancy.
```
SELECT 
  [object_name],
  [counter_name],
  [cntr_value] 
FROM 
  sys.dm_os_performance_counters
WHERE 
  [object_name] 
LIKE 
  '%Manager%'
AND 
  [counter_name] = 'Page life expectancy'
```
Verificar Memory Grants Pending

Memory Grants Pending especifica o número total de processos que aguardam uma concessão de memória do espaço de trabalho. Execute a consulta a seguir para verificar Memory Grants Pending. Se essa consulta mostrar de forma consistente concessões pendentes, ela indica alta utilização da memória. É possível reduzir a consulta consultando as esperas do banco de dados e o ajuste de qualquer instrução que esteja aguardando na memória.
```
SELECT
  @@SERVERNAME AS [Server Name],
  RTRIM([object_name]) AS [Object Name],
  cntr_value AS [Memory Grants Pending]
FROM 
  sys.dm_os_performance_counters WITH(NOLOCK)
WHERE
  [object_name] 
LIKE 
  N'%Memory Manager%'  -- Handles named instances
AND 
  counter_name = N'Memory Grants Pending'
```