Compartilhar via

Solucionar problemas de memória insuficiente ou memória baixa no SQL Server

  • Artigo

Sintomas

O SQL Server usa uma arquitetura de memória complexa que corresponde ao conjunto de recursos complexo e avançado. Devido à variedade de necessidades de memória, pode haver muitas fontes de consumo e pressão de memória, causando condições de falta de memória.

Há erros comuns que indicam pouca memória no SQL Server. Exemplos de erros incluem:

  • 701: Falha ao alocar memória suficiente para executar uma consulta.
  • 802: Falha ao obter memória para alocar páginas no pool de buffers (dados ou páginas de índice).
  • 1204: Falha ao alocar memória para bloqueios.
  • 6322: Falha ao alocar memória para o analisador XML.
  • 6513:Falha ao inicializar o CLR devido à pressão de memória.
  • 6533: AppDomain descarregado devido à falta de memória.
  • 8318: Falha ao carregar contadores de desempenho SQL devido à memória insuficiente.
  • 8356 ou 8359: o rastreamento ETW ou SQL falha ao ser executado devido à pouca memória.
  • 8556: Falha ao carregar o MSDTC devido à memória insuficiente.
  • 8645: Falha ao executar uma consulta devido à falta de memória para concessões de memória (classificação e hash) Para obter mais informações, consulte Como solucionar problemas do erro 8645 do SQL Server.
  • 8902: Falha ao alocar memória durante a execução do DBCC.
  • 9695 ou 9696: Falha ao alocar memória para operações do Service Broker.
  • 17131 ou 17132: Falha na inicialização do servidor devido à memória insuficiente.
  • 17890: Falha ao alocar memória devido à memória SQL sendo paginada pelo sistema operacional.
  • 18053: O erro é impresso no modo conciso porque houve erro durante a formatação. Rastreamento, ETW, notificações, etc., foram ignorados.
  • 22986 ou 22987: Altere falhas de captura de dados devido à memória insuficiente.
  • 25601: O mecanismo Xevent está sem memória.
  • 26053: Falha na inicialização dos adaptadores de rede SQL devido à memória insuficiente.
  • 30085, 30086, 30094: as operações de texto completo do SQL falham devido à memória insuficiente.

Causa

Muitos fatores podem causar memória insuficiente. Esses fatores incluem configurações do sistema operacional, disponibilidade de memória física, componentes que usam memória dentro do SQL Server e limites de memória na carga de trabalho atual. Na maioria dos casos, a consulta que falha com um erro de memória insuficiente não é a causa desse erro. No geral, as causas podem ser agrupadas em três categorias:

Causa 1: pressão de memória externa ou do sistema operacional

A pressão externa refere-se à utilização de memória alta proveniente de um componente fora do processo que leva a memória insuficiente para SQL Server. Você precisa descobrir se outros aplicativos no sistema estão consumindo memória e contribuindo para a baixa disponibilidade de memória. O SQL Server é um dos poucos aplicativos projetados para responder à pressão de memória do sistema operacional reduzindo seu uso de memória. Isso significa que, se um aplicativo ou driver solicitar memória, o sistema operacional enviará um sinal a todos os aplicativos para liberar memória e o SQL Server responderá reduzindo seu próprio uso de memória. Poucos outros aplicativos respondem porque não foram projetados para escutar essa notificação. Portanto, se o SQL Server começar a reduzir o uso de memória, seu pool de memória será reduzido e os componentes que precisarem de memória poderão não obtê-la. Como resultado, você começa a receber erros 701 ou outros relacionados à memória. Para obter mais informações sobre como o SQL aloca e libera memória dinamicamente, consulte Arquitetura de memória do SQL Server. Para obter diagnósticos e soluções mais detalhados para o problema, consulte Pressão da memória externa neste artigo.

Existem três grandes categorias de problemas que podem causar pressão na memória do sistema operacional:

  • Problemas relacionados ao aplicativo: um ou vários aplicativos juntos esgotam a memória física disponível. O sistema operacional responderá a novas solicitações de aplicativos por recursos tentando liberar memória. A abordagem comum é descobrir quais aplicativos estão esgotando a memória e tomar as medidas necessárias para equilibrar a memória entre eles sem levar ao esgotamento da RAM.
  • Problemas de driver de dispositivo: os drivers de dispositivo podem causar a paginação do conjunto de trabalho de todos os processos se o driver chamar incorretamente uma função de alocação de memória.
  • Problemas com o produto do sistema operacional.

Para obter uma explicação detalhada dessas etapas e das etapas de solução de problemas, consulte MSSQLSERVER_17890.

Causa 2: pressão de memória interna, não proveniente do SQL Server

A pressão de memória interna refere-se à disponibilidade de memória baixa causada por fatores dentro do processo do SQL Server. Alguns componentes que podem ser executados dentro do processo do SQL Server são "externos" ao mecanismo do SQL Server. Os exemplos incluem provedores OLE DB (DLLs), como servidores vinculados, procedimentos ou funções SQLCLR, procedimentos estendidos (XPs) e automação OLE (sp_OA*). Outros incluem antivírus ou outros programas de segurança que injetam DLLs dentro de um processo para fins de monitoramento. Um problema ou um design ruim em qualquer um desses componentes pode levar a um grande consumo de memória. Por exemplo, considere um servidor vinculado armazenando em cache 20 milhões de linhas de dados de uma fonte externa na memória do SQL Server. No que diz respeito ao SQL Server, nenhum administrador de memória relatará uso de memória alta, mas a memória consumida dentro do processo do SQL Server será alta. Esse crescimento de memória de uma DLL de servidor vinculada, por exemplo, faria com que o SQL Server começasse a reduzir seu uso de memória (veja acima) e criaria condições de memória baixa para componentes dentro do SQL Server, causando erros de falta de memória. Para obter diagnósticos e soluções mais detalhados sobre o problema, consulte Pressão de memória interna, não proveniente do SQL Server.

Observação

Algumas DLLs da Microsoft usadas no espaço de processo do SQL Server (por exemplo, MSOLEDBSQL, SQL Native Client) são capazes de interagir com a infraestrutura de memória do SQL Server para relatórios e alocação. Você pode executar select * from sys.dm_os_memory_clerks where type='MEMORYCLERK_HOST' para obter uma lista deles e acompanhar o consumo de memória para algumas de suas alocações.

Causa 3: pressão de memória interna proveniente de componentes do SQL Server

A pressão de memória interna proveniente de componentes dentro do mecanismo do SQL Server também pode levar a erros de falta de memória. Há centenas de componentes rastreados por meio de administradores de memória que alocam memória no SQL Server. Você deve identificar quais administradores de memória são responsáveis pelas maiores alocações de memória para resolver esse problema. Por exemplo, se você achar que o administrador de OBJECTSTORE_LOCK_MANAGER memória está mostrando uma grande alocação de memória, precisará entender por que o Gerenciador de Bloqueio está consumindo tanta memória. Você pode descobrir que há consultas que adquirem muitos bloqueios. Você pode otimizar essas consultas usando índices, encurtando todas as transações que mantêm bloqueios por um longo tempo ou verificando se o escalonamento de bloqueios está desabilitado. Cada administrador de memória ou componente tem um modo exclusivo de acessar e usar a memória. Para obter mais informações, consulte tipos de administrador de memória e suas descrições. Para obter diagnósticos e soluções mais detalhados sobre o problema, consulte Uso de memória interna pelo mecanismo do SQL Server.

Representação visual dos tipos de pressão de memória

O gráfico a seguir ilustra os tipos de pressão que podem levar a condições de falta de memória no SQL Server:

Captura de tela dos tipos de pressão de memória.

Ferramentas de diagnóstico para coletar dados de solução de problemas

Você pode usar as seguintes ferramentas de diagnóstico para coletar dados de solução de problemas:

Monitor de desempenho

Configure e colete os seguintes contadores com o Monitor de Desempenho:

  • Memória:MBytes disponíveis
  • Process:Working Set
  • Process:Private Bytes
  • SQL Server:Memory Manager: (todos os contadores)
  • SQL Server:Buffer Manager: (todos os contadores)

DMVs ou DBCC MEMORYSTATUS

Você pode usar sys.dm_os_memory_clerks ou DBCC MEMORYSTATUS para observar o uso geral de memória dentro do SQL Server.

Relatório Padrão de Consumo de Memória no SSMS

Exibir o uso de memória no SQL Server Management Studio:

  1. Inicie o SQL Server Management Studio e conecte-se a um servidor.
  2. No Pesquisador de Objetos, clique com o botão direito do mouse no nome da instância do SQL Server.
  3. No menu de contexto, selecione Relatórios>padrão Relatórios>Consumo de memória.

PSSDiag ou SQL LogScout

Uma maneira alternativa e automatizada de capturar esses pontos de dados é usar ferramentas como PSSDiag ou SQL LogScout.

  • Se você usar PSSDiag, configure-o para capturar o coletor Perfmon e o coletor Diagnóstico Personalizado\Erro de Memória SQL.

  • Se você usar o SQL LogScout, configure-o para capturar o cenário de memória .

As seções a seguir descrevem etapas mais detalhadas para cada cenário (pressão de memória externa ou interna).

Metodologia de solução de problemas

Se um erro de memória insuficiente aparecer ocasionalmente ou por um breve período, pode haver um problema de memória de curta duração que se resolve. Talvez você não precise executar ações nesses casos. No entanto, se o erro ocorrer várias vezes em várias conexões e persistir por períodos de segundos ou mais, siga os diagnósticos e as soluções nas seções a seguir para solucionar problemas de memória

Pressão de memória externa

Para diagnosticar condições de memória insuficiente no sistema fora do processo do SQL Server, use os seguintes métodos:

  • Colete contadores do Monitor de Desempenho. Investigue se aplicativos ou serviços diferentes do SQL Server estão consumindo memória neste servidor examinando os seguintes contadores:

    • Memória:MBytes disponíveis
    • Process:Working Set
    • Process:Private Bytes

    Aqui está um exemplo de coleta de logs do Perfmon usando o PowerShell:

    clear
$serverName = $env:COMPUTERNAME
$Counters = @(
   ("\\$serverName" +"\Memory\Available MBytes"),
   ("\\$serverName" +"\Process(*)\Working Set"),
   ("\\$serverName" +"\Process(*)\Private Bytes")
)

Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
$_.CounterSamples | ForEach-Object   {
   [pscustomobject]@{
      TimeStamp = $_.TimeStamp
      Path = $_.Path
      Value = ([Math]::Round($_.CookedValue, 3)) }
}
}

  • Examine o log de eventos do sistema e procure erros relacionados à memória (por exemplo, memória virtual baixa).

  • Examine o log de eventos do aplicativo para obter problemas de memória relacionados ao aplicativo.

    Aqui está um exemplo de um script do PowerShell para consultar os logs de eventos do sistema e do aplicativo para a palavra-chave "memória". Sinta-se à vontade para usar outras strings como "recurso" para sua pesquisa:

    Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"

  • Resolva quaisquer problemas de código ou configuração para aplicativos ou serviços menos críticos para reduzir o uso de memória.

  • Se aplicativos além do SQL Server estiverem consumindo recursos, tente interromper ou reagendar esses aplicativos ou considere executá-los em um servidor separado. Essas etapas removerão a pressão de memória externa.

Pressão de memória interna, não proveniente do SQL Server

Para diagnosticar a pressão da memória interna causada por módulos (DLLs) dentro do SQL Server, use os seguintes métodos:

  • Se o SQL Server não usar Páginas Bloqueadas na Memória (API AWE), a maior parte de sua memória será refletida no contador Process:Private Bytes (SQLServr instância) no Monitor de Desempenho. O uso geral de memória proveniente do mecanismo do SQL Server é refletido no contador SQL Server:Memory Manager: Total Server Memory (KB). Se você encontrar uma diferença significativa entre o valor Process:Private Bytes e SQL Server:Memory Manager: Total Server Memory (KB), essa diferença provavelmente virá de uma DLL (servidor vinculado, XP, SQLCLR e assim por diante). Por exemplo, se os bytes privados forem 300 GB e a memória total do servidor for 250 GB, aproximadamente 50 GB da memória geral no processo virão de fora do mecanismo do SQL Server.

  • Se o SQL Server estiver usando Páginas Bloqueadas na Memória (API AWE), será mais desafiador identificar o problema porque o Monitor de Desempenho não oferece contadores AWE que rastreiam o uso de memória para processos individuais. O uso geral de memória no mecanismo do SQL Server é refletido no contador SQL Server:Gerenciador de Memória: Memória Total do Servidor (KB). Os valores típicos de Process:Private Bytes podem variar entre 300 MB e 1-2 GB. Se você encontrar um uso significativo de Process:Private Bytes além desse uso típico, a diferença provavelmente virá de uma DLL (servidor vinculado, XP, SQLCLR e assim por diante). Por exemplo, se o contador de bytes privados for de 4 a 5 GB e o SQL Server estiver usando AWE (Páginas Bloqueadas na Memória), uma grande parte dos bytes privados poderá vir de fora do mecanismo do SQL Server. Essa é uma técnica de aproximação.

  • Use o utilitário Tasklist para identificar as DLLs carregadas dentro do espaço do SQL Server:

    tasklist /M /FI "IMAGENAME eq sqlservr.exe"

  • Você também pode usar a consulta a seguir para examinar DLLs (módulos carregados) e ver se há algo inesperado.

    SELECT * FROM sys.dm_os_loaded_modules

  • Se você suspeitar que um módulo de servidor vinculado está causando um consumo significativo de memória, poderá configurá-lo para ficar sem processo desativando a opção Permitir em processo . Consulte Criar servidores vinculados para obter mais informações. Nem todos os provedores OLE DB de servidor vinculado podem ficar sem processo. Para obter mais informações, entre em contato com o fabricante do produto.

  • No caso raro em que objetos de automação OLE (sp_OA*) são usados, você pode configurar o objeto para ser executado em um processo fora do SQL Server especificando um valor de contexto de 4 (somente servidor OLE local (.exe)). Para obter mais informações, confira sp_OACreate.

Uso de memória interna pelo mecanismo do SQL Server

Para diagnosticar a pressão da memória interna proveniente de componentes dentro do mecanismo do SQL Server, use os seguintes métodos:

  • Comece a coletar contadores do Monitor de Desempenho para SQL Server: SQL Server:Gerenciador de Buffer e SQL Server: Gerenciador de Memória.

  • Consulte a DMV dos administradores de memória do SQL Server várias vezes para ver onde ocorre o maior consumo de memória dentro do mecanismo:

    SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC

  • Como alternativa, você pode observar a saída mais detalhada DBCC MEMORYSTATUS e a maneira como ela muda quando você vê essas mensagens de erro.

    DBCC MEMORYSTATUS

  • Se você identificar uma infração clara nos administradores de memória, concentre-se em abordar as especificações de consumo de memória para esse componente. Veja os seguintes exemplos:

    • Se o administrador MEMORYCLERK_SQLQERESERVATIONS de memória estiver consumindo memória, identifique as consultas que estão usando grandes concessões de memória e otimize-as por meio de índices, reescreva-as (remova ORDER by, por exemplo) ou aplique dicas de consulta de concessão de memória (consulte min_grant_percent e max_grant_percent dicas ). Você também pode criar um pool de administradores de recursos para controlar o uso da memória de concessão de memória. Para obter informações detalhadas sobre concessões de memória, consulte Solucionar problemas de desempenho lento ou de pouca memória causados por concessões de memória no SQL Server.
    • Se um grande número de planos de consulta ad hoc for armazenado em cache, o CACHESTORE_SQLCP administrador de memória usará grandes quantidades de memória. Identifique consultas não parametrizadas cujos planos de consulta não podem ser reutilizados e parametrize-os convertendo-os em procedimentos armazenados, usando sp_executesqlou usando parametrização FORCED . Se você ativou o sinalizador de rastreamento 174, poderá desativá-lo para ver se isso resolve o problema.
    • Se o armazenamento CACHESTORE_OBJCP de cache do plano de objeto estiver consumindo muita memória, identifique quais procedimentos armazenados, funções ou gatilhos estão usando grandes quantidades de memória e, possivelmente, reprojete o aplicativo. Normalmente, isso pode acontecer devido a grandes quantidades de bancos de dados ou esquemas com centenas de procedimentos em cada um.
    • Se o administrador de OBJECTSTORE_LOCK_MANAGER memória mostrar grandes alocações de memória, identifique consultas que aplicam muitos bloqueios e otimize-as usando índices. Reduza as transações que fazem com que os bloqueios não sejam liberados por longos períodos em determinados níveis de isolamento ou verifique se o escalonamento de bloqueios está desabilitado.
    • Se você observar muito grande TokenAndPermUserStore (select type, name, pages_kb from sys.dm_os_memory_clerks where name = 'TokenAndPermUserStore'), poderá usar o sinalizador de rastreamento 4618 para limitar o tamanho do cache.
    • Se você observar problemas de memória com o OLTP na memória provenientes do MEMORYCLERK_XTP administrador de memória, consulte Monitorar e solucionar problemas de uso de memória para erros de OLTP na memória e metadados tempdb otimizados para memória (HkTempDB).

Solução rápida que pode disponibilizar memória

As seguintes ações podem liberar alguma memória e disponibilizá-la para o SQL Server:

Alterar as definições de configuração de memória

Verifique os seguintes parâmetros de configuração de memória do SQL Server e considere aumentar a memória máxima do servidor, se possível:

  • memória máxima do servidor
  • memória mínima do servidor

Observação

Se você notar configurações incomuns, corrija-as conforme necessário e considere o aumento dos requisitos de memória. As configurações padrão estão listadas em Opções de configuração de memória do servidor.

Se você não configurou a memória máxima do servidor, especialmente com Páginas Bloqueadas na Memória, considere defini-la como um valor específico para permitir alguma memória para o sistema operacional. Consulte a opção de configuração do servidor Páginas bloqueadas na memória .

Alterar ou mover a carga de trabalho para fora do sistema

Investigue a carga de trabalho de consulta: número de sessões simultâneas, consultas em execução no momento e veja se há aplicativos menos críticos que podem ser interrompidos temporariamente ou movidos para outro SQL Server.

Para cargas de trabalho somente leitura, considere movê-las para uma réplica secundária somente leitura em um ambiente Always On. Para obter mais informações, consulte Descarregar carga de trabalho somente leitura para a réplica secundária de um grupo de disponibilidade AlwaysOn e Configurar o acesso somente leitura a uma réplica secundária de um grupo de disponibilidade AlwaysOn.

Garantir a configuração de memória adequada para máquinas virtuais

Se você estiver executando o SQL Server em uma VM (máquina virtual), verifique se a memória da VM não está sobrecarregada. Para obter ideias sobre como configurar a memória para VMs, consulte Virtualização - Comprometimento excessivo de memória e como detectá-la na VM e Solução de problemas de desempenho da máquina virtual ESX/ESXi (comprometimento excessivo de memória).

Liberar memória dentro do SQL Server

Você pode executar um ou mais dos seguintes comandos DBCC para liberar vários caches de memória do SQL Server:

  • DBCC FREESYSTEMCACHE

  • DBCC FREESESSIONCACHE

  • DBCC FREEPROCCACHE

Reiniciar o serviço do SQL Server

Em alguns casos, se você precisar lidar com o esgotamento crítico de memória e o SQL Server não conseguir processar consultas, considere reiniciar o serviço.

Considere usar o Administrador de Recursos para cenários específicos

Se você estiver usando o Administrador de Recursos, recomendamos que verifique as configurações do pool de recursos e do grupo de carga de trabalho para ver se elas não estão limitando a memória drasticamente.

Adicione mais RAM no servidor físico ou virtual

Se o problema persistir, você precisará investigar mais e possivelmente aumentar os recursos do servidor (RAM).