No mundo do SQL Server, a memória desempenha um papel crucial no desempenho e eficiência do sistema de banco de dados. Muitos usuários frequentemente se perguntam por que o SQL Server consome uma grande quantidade de memória e não a libera, mesmo durante os horários de menor demanda. Neste post do blog, exploraremos o conceito de consumo de memória no SQL Server e discutiremos um relatório oculto no SQL Server Management Studio que fornece informações valiosas sobre o uso da memória.

O Relatório de Consumo de Memória

O Relatório de Consumo de Memória no SQL Server Management Studio é uma mina de ouro de informações que muitas vezes é negligenciada. Para acessar este relatório, basta clicar com o botão direito no Nó do Servidor no SSMS, ir para Relatórios, depois Relatórios Padrão e selecionar Consumo de Memória.

Contadores Relacionados à Memória

Uma das seções do Relatório de Consumo de Memória exibe três valores que nos dão uma indicação da pressão de memória na instância do SQL Server. Esses valores são obtidos a partir dos contadores de memória do SQL Server e incluem “Memory Grants Pending” (Concessões de Memória Pendentes), “Memory Grants Outstanding” (Concessões de Memória Pendentes) e “Page life expectancy” (Expectativa de Vida da Página). Esses contadores nos ajudam a entender o número atual de processos aguardando uma concessão de memória de espaço de trabalho e o número de processos que adquiriram com sucesso uma concessão de memória de espaço de trabalho.

Principais Componentes de Consumo de Memória

Outra seção do relatório mostra um gráfico de pizza que representa vários consumidores de memória, também conhecidos como “clerks”, com base na quantidade de memória consumida por cada um deles. Na maioria dos casos, o SQLBUFFERPOOL é o maior consumidor de memória. Essas informações são obtidas a partir do DMV sys.dm_os_memory_clerks, que é um DMV chave para monitorar o desempenho de memória do SQL Server. Usando esse DMV, podemos identificar onde exatamente a memória do SQL Server está sendo consumida.

Distribuição de Páginas de Buffer

O relatório também inclui uma seção que exibe o estado das páginas de buffer. Essas informações são obtidas usando o comando DBCC MEMORYSTATUS, que fornece a distribuição de páginas de buffer em vários estados, como ‘Stolen’ (Roubado), ‘Free’ (Livre), ‘Cached’ (Em Cache), ‘Dirty’ (Sujo), ‘Kept’ (Mantido), ‘I/O’, ‘Latched’ (Travado) ou ‘Other’ (Outro). É importante observar que nem todos esses estados podem ser visíveis ao executar o comando DBCC MEMORYSTATUS, pois o formato de saída foi alterado ao longo dos anos.

Mudanças de Memória ao Longo do Tempo

O Relatório de Consumo de Memória também inclui uma seção que mostra dados do rastreamento padrão. Um dos eventos capturados pelo rastreamento padrão é “Server Memory Change” (Mudança de Memória do Servidor) (ID do Evento 81). Esta seção lê o rastreamento padrão, filtra os dados para exibir registros dos últimos 7 dias e fornece informações sobre as mudanças de memória ao longo do tempo. Isso pode ser especialmente útil para identificar quaisquer flutuações ou tendências de memória em sua instância do SQL Server.

Uso de Memória por Componentes

Na parte inferior do relatório, há uma tabela que mostra o uso de memória para cada componente. Essas informações também são obtidas do DMV sys.dm_os_memory_clerks. O gráfico anterior no relatório exibe os 5% principais consumidores de memória por nome, enquanto o restante é agrupado como “Outros”. Vale ressaltar que, no SQL Server 2014, o relatório sempre mostrará MEMORYCLERK_XTP, que é usado pelo mecanismo In-Memory OLTP, mesmo que não seja um dos principais consumidores.

Compreender os vários componentes do consumo de memória no SQL Server é essencial para otimizar o desempenho e solucionar problemas relacionados à memória. O Relatório de Consumo de Memória no SQL Server Management Studio fornece uma visão abrangente do uso de memória e ajuda a identificar gargalos potenciais ou áreas para melhorias.

Da próxima vez que você se perguntar sobre o consumo de memória do SQL Server, não se esqueça de explorar o Relatório de Consumo de Memória. É uma joia oculta que pode fornecer informações valiosas sobre sua instância do SQL Server.

Você tem algum outro componente além de BufferPool ou SOSNode como os principais consumidores de memória em suas instâncias do SQL Server? Deixe-nos saber nos comentários!