Otimização do uso de memória do SQL Server

O Microsoft SQL Server utiliza a memória para armazenar dados em cache e aprimorar o desempenho do servidor. Por padrão, o SQL Server lê as páginas de dados do disco para a memória. Ele armazena o máximo de páginas determinadas e só as exclui conforme necessário. Quando o servidor recebe consultas que exigem páginas que já usou antes, ele pode recuperá-las rapidamente da memória em vez de ter de criá-las do zero.

Um equívoco comum é achar que o SQL Server usa toda sua memória ou devora recursos da memória. Como o SQL Server funciona para otimizar o desempenho, ele usa o máximo de memória alocada possível. No entanto, cabe inteiramente a você decidir quanta memória alocar para o SQL Server e quanto deixar para outros aplicativos ou o sistema operacional.

Quanto se trata de desempenho do sistema, há três elementos principais que funcionam juntos para manter o sistema com o desempenho ideal: CPU, armazenamento e memória. A memória precisa ser alocada com eficácia para evitar gargalos de desempenho.

Determinar quanta memória alocar no SQL Server é uma tarefa difícil que exige monitoramento consistente do uso de memória do SQL Server. Ao monitorar e maximizar a memória do SQL Server, você pode aprimorar o desempenho do SQL Server e os tempos de resposta de consultas do servidor.

O monitoramento do uso de memória do SQL Server envolve a análise pelo Administrador de bancos de dados (DBA) das métricas de uso de memória em todo o sistema do computador – não só para o SQL Server – e o uso desses dados para alocar adequadamente os recursos de memória finitos.

O principal objetivo do monitoramento de uso da memória do SQL Server é determinar quanta memória deve ser designada para o SQL Server sem comprometer o desempenho do sistema operacional ou outros aplicativos. Para alcançar esse objetivo, você precisa monitorar as métricas de desempenho do seu sistema e usar esses dados para influenciar o ajuste de memória do SQL Server.

Vários fatores podem influenciar o uso maior de memória do sistema do computador. O monitoramento e a otimização do uso de memória do SQL envolve localizar os dados em relação a esses fatores para que você possa determinar quais consultas SQL precisam da sua atenção. Veja alguns dos fatores que influenciam o uso de memória:

• Expectativa de vida da página (PLE é uma métrica importante para o monitoramento do uso de memória. Quedas na PLE indicam que as páginas não estão permanecendo na memória para outros usos. Esse comportamento geralmente ocorre quando o SQL Server está ficando com pouca memória de buffer.)

• Tamanho de cache do plano
• Uso de memória do sistema operacional
• Tamanho de cache do buffer
• Taxa de acertos do cache do buffer
• Taxa de paginação de memória
• Bytes de log por segundo
• Limpezas de log por segundo
• Taxa de acertos do cache do procedimento
• Compilações SQL

Quando se trata do uso de memória do SQL Server, o monitoramento envolve analisar o relatório de consumo de memória do SQL Server que pode ser encontrado em qualquer sistema que tenha o SQL Server. Esse relatório fornece informações em seções e inclui o seguinte:

• Principais componentes que consomem memória
• Distribuição de páginas de buffer
• Alterações de memória ao longo dos últimos sete dias
• Uso de memória por componente

O monitoramento desses dados permite determinar melhor o que você deve definir como memória máxima para seu SQL Server e como você deve ajustar esse máximo conforme suas necessidades e usos mudam.

Embora seja possível monitorar o uso da memória do SQL Server manualmente, pode ser desafiador localizar e comparar métricas constantemente, todas elas encontradas em diferentes telas e em diferentes ferramentas. Em vez disso, é melhor usar uma única ferramenta para ajudar com suas necessidades de monitoramento de memória do SQL Server. O SolarWinds^®  Database Performance Analyzer, criado para coletar dados relevantes, correlacioná-los e apresentá-los em um painel único, é essa ferramenta.

O monitoramento do uso de memória do SQL Server é importante porque é correlacionado diretamente com o desempenho do SQL Server. Ele tem um grande impacto sobre a rapidez com que as consultas são executadas.

Ao monitorar a PLE como parte de seu monitoramento geral de uso da memória do SQL Server, você pode determinar se o SQL Server está com pouca memória de buffer. Isso pode indicar que você está perdendo índices ou que suas instruções SQL estão mal escritas.

Infelizmente, as configurações de uso de memória padrão no SQL Server geralmente estão distantes do ideal. Se não otimizar essas configurações, você poderá ter a sensação de que a única forma de aprimorar o desempenho é investindo em mais memória. Essa iniciativa cara não corrigirá o problema. Em vez disso, ela apenas atuará como uma solução transitória até que você fique sem memória de novo e seja forçado a comprar mais e mais.

Ao monitorar o uso de memória do SQL Server e aplicar os insights que obteve, você pode enfrentar os desafios que causam problemas de desempenho. Ao otimizar o uso de memória, você pode até mesmo aprimorar o desempenho do SQL Server sem desperdiçar dinheiro em mais memória.

Uma ferramenta de monitoramento de uso de memória do SQL Server fornece a você os insights necessários sobre o sistema para otimizar o desempenho do SQL Server e aprimorar o uso de memória do servidor. Essas ferramentas não só permitem ver informações sobre os diferentes fatores que influenciam o uso da memória, como também fornecem visibilidade das métricas de desempenho necessárias para otimizar consultas prejudiciais ou caras.

As ferramentas de monitoramento de uso d memória do SQL Server ajudam os DBAs a identificar em quais consultas SQL se concentrar, extraindo e coletando dados segundo a segundo das sessões ativas. Elas também classificam instruções SQL por tempos de resposta para que você possa identificar melhor onde residem os problemas.

Com uma ferramenta de monitoramento, você pode ver a correlação direta entre o tempo de resposta e os recursos do sistema, como CPU, armazenamento e memória, em um único painel. Ao fornecer visibilidade de métricas de desempenho como execução média, duração de consulta, leituras lógicas/físicas, etc., as ferramentas de monitoramento ajudam a coletar informações, estabelecer linhas de base e manter um histórico dos melhores métodos de execução para otimização de consulta SQL eficaz.

Quando se trata de memória, um dos maiores benefícios do uso de uma ferramenta para o monitoramento do SQL Server é que a ferramenta é projetada para constantemente coletar informações sobre a expectativa de vida da página, o tamanho de cache do plano e mais.

A expectativa de vida da página, ou PLE, é o número de segundos que uma página permanece no pool de buffer sem referências. Quanto mais a página permanece na memória, maiores são as chances de a página ser reutilizada. Quedas na PLE indicam que as páginas não estão permanecendo na memória para outros usos. Esse comportamento geralmente ocorre quando o SQL Server está ficando com pouca memória de buffer. Geralmente, instruções SQL mal escritas ou índices ausentes podem fazer com que o SQL Server use memória demais porque a consulta está lendo muitos dados para retornar um conjunto de resultados muito menor. A indexação adequada e consultas bem escritas podem reduzir a sobrecarga de memória, otimizando o uso de memória.

O cache do plano verifica o tamanho do pool do buffer e mostra o tamanho da memória configurada. Sempre que uma instrução SQL é executada, o mecanismo verifica se o plano da instrução SQL já existe. Se já existir, o mecanismo fará uso dele em vez de desperdiçar tempo compilando um novo plano. É assim que o cache do plano aprimora a eficiência do mecanismo do SQL Server. O armazenamento de um plano de consulta para uma instrução executada somente uma vez é chamado de plano de uso único. Muitos planos de uso único podem esgotar a memória do SQL Server e resultar em mau desempenho do SQL Server.

Ao monitorar de maneira consistente e fornecer dados detalhados sobre essas duas métricas, uma ferramenta de monitoramento de uso de memória do SQL Server pode fornecer aos DBAs as informações de que eles precisam para ajustar e otimizar com eficácia sua alocação de memória do SQL Server e suas consultas SQL em geral.

O SolarWinds SQL Sentry oferece ferramentas abrangentes e monitoramento em tempo real para ajudar os administradores de bancos de dados a otimizar o uso de memória do SQL Server. Ele coleta dados em vários fatores relacionados à memória, identifica consultas problemáticas, permite que você defina linhas de base de desempenho e aprimore a análise de desempenho. Além disso, seu monitoramento se estende aos ambientes de VMware, permitindo um gerenciamento proativo e oferecendo uma visão holística da integridade do banco de dados que vai além das métricas de memória. Os recurso do SQL Sentry ajudam a manter altos níveis de desempenho e estabilidade em ambientes do SQL Server.

• Memória do SQL Server : Tamanho de cache do buffer
• Memória do SQL Server : Tamanho de cache do plano (SQL)
• Memória do SQL Server : Tamanho de cache do plano (objetos)
• Memória do SQL Server : Outro tamanho de cache
• Memória do SQL Server : In-Mem OLTP
• Memória do SQL Server : Concessões de consulta
• Memória do SQL Server : Columnstore
• Memória do SQL Server : Taxa de acertos do cache do plano (SQL)
• Memória do SQL Server : Taxa de acertos do cache do plano (objetos)
• Memória do SQL Server : Buffer subtraído
• Memória do SQL Server : Leituras de página
• Memória do SQL Server : Gravações de página
• Memória do SQL Server : Expectativa de vida da página
• Memória do SQL Server : Concessões de memória pendentes
• Memória do sistema : Memória por instância do SQL Server / SSAS
• Memória do sistema : Outra memória de processos
• Memória do sistema : Memória do cache de arquivos
• Memória do sistema : Memória inflada**
• Memória do sistema : Memória disponível
• Memória do sistema : Falhas de leitura
• Memória do sistema : Falhas de gravação
• Memória do sistema : Uso do arquivo de página

Você encontra a lista completa na documentação da SolarWinds.