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

Sintomas

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 de memória e pressão de memória, causando condições de memória.

Há erros comuns que indicam memória baixa em 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 (páginas de dados ou índice).
• 1204: falha ao alocar memória para bloqueios.
• 6322: falha ao alocar memória para o analisador XML.
• 6513:Falha na inicialização do 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 não é executado devido à baixa 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 SQL Server erro 8645.
• 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 de inicialização do servidor devido à memória insuficiente.
• 17890: falha na alocação de memória devido à memória SQL sendo excluída pelo sistema operacional.
• 22986 ou 22987: alterar falhas de captura de dados devido à memória insuficiente.
• 25601: O mecanismo Xevent está sem memória.
• 26053: as interfaces de rede SQL não são inicializados devido à memória insuficiente.
• 30085, 30086, 30094: as operações de texto completo do SQL falham devido à memória insuficiente.

Motivo

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 de 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 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 à alta utilização de memória proveniente de um componente fora do processo que leva à 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. SQL Server é um dos poucos aplicativos projetados para responder à pressão de memória do sistema operacional reduzindo o 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 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 SQL Server começar a reduzir o uso de memória, seu pool de memória será reduzido e qualquer componente que precise de memória poderá não obtê-lo. Como resultado, você começa a receber 701 ou outros erros relacionados à memória. Para obter mais informações sobre como o SQL aloca e libera a memória dinamicamente, consulte SQL Server Arquitetura de Memória. Para obter diagnóstico e soluções mais detalhadas para o problema, consulte Pressão de memória externa neste artigo.

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

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

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

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

A pressão de memória interna refere-se à baixa disponibilidade de memória causada por fatores dentro do processo de SQL Server. Alguns componentes que podem ser executados dentro do processo de SQL Server são "externos" para o mecanismo SQL Server. Exemplos incluem DLLs (provedores OLE DB), 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 que armazena 20 milhões de linhas de dados de uma fonte externa em SQL Server memória. No que diz respeito SQL Server, nenhum funcionário de memória relatará alto uso de memória, mas a memória consumida dentro do processo de SQL Server será alta. Esse crescimento de memória de uma DLL de servidor vinculado, por exemplo, faria com que SQL Server começasse a cortar o uso de memória (consulte acima) e criaria condições de memória baixas para componentes dentro de SQL Server, causando erros de memória. Para obter diagnóstico e soluções mais detalhadas sobre o problema, consulte Pressão de memória interna, não proveniente de SQL Server.

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

A pressão de memória interna proveniente de componentes dentro do mecanismo de SQL Server também pode levar a erros de memória fora. Há centenas de componentes rastreados por meio de funcionários de memória que alocam memória em SQL Server. Você deve identificar quais funcionários de memória são responsáveis pelas maiores alocações de memória para resolve esse problema. Por exemplo, se você descobrir que o OBJECTSTORE_LOCK_MANAGER gerenciador de memória está mostrando uma grande alocação de memória, você 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 bloqueio está desabilitado. Cada funcionário ou componente de memória tem uma maneira exclusiva de acessar e usar a memória. Para obter mais informações, consulte tipos de funcionário de memória e suas descrições. Para obter diagnóstico e soluções mais detalhadas sobre o problema, consulte Uso interno de memória por SQL Server mecanismo.

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 fora da memória em SQL Server:

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

Configurar e coletar os seguintes contadores com Monitor de Desempenho:

• Memória:MBytes disponíveis
• Process:Working Set
• Process:Private Bytes
• SQL Server:Gerenciador de Memória: (todos os contadores)
• SQL Server:Gerenciador de Buffers: (todos os contadores)

DMVs ou DBCC MEMORYSTATUS

Você pode usar sys.dm_os_memory_clerks ou DBCC MEMORYSTATUS para observar o uso geral da memória dentro de 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 SQL Server Management Studio e conecte-se a um servidor.
2. Em Pesquisador de Objetos, clique com o botão direito do mouse no nome da instância SQL Server.
3. No menu de contexto, selecione Relatórios>Padrão Relata>Consumo de Memória.

LogScout PSSDiag ou SQL

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

• Se você usar o PSSDiag, configure-o para capturar o coletor Perfmon e o coletor de erros de memória De diagnóstico personalizado\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 fora de memória ocasionalmente aparecer 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 agir 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 o diagnóstico e soluções nas seções a seguir para solucionar ainda mais os erros de memória.

Pressão de memória externa

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

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

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

  Aqui está um exemplo da coleção de logs 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 o palavra-chave "memória". Sinta-se à vontade para usar outras cadeias de caracteres 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 os aplicativos além 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 de SQL Server

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

• Se SQL Server não usar Páginas Bloqueadas na Memória (API do AWE), a maior parte de sua memória será refletida no contador Process:Private Bytes (SQLServrinstância) em Monitor de Desempenho. O uso geral de memória proveniente de dentro do mecanismo SQL Server é refletido no contador SQL Server:Gerenciador de Memória: Memória Total do Servidor (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 bytes privados tiverem 300 GB e a Memória Total do Servidor for de 250 GB, aproximadamente 50 GB da memória geral no processo virá de fora do mecanismo SQL Server.

• Se SQL Server estiver usando páginas bloqueadas na memória (API da AWE), será mais desafiador identificar o problema porque o Monitor de Desempenho não oferece contadores AWE que acompanham o uso de memória para processos individuais. O uso geral da memória no mecanismo 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 no geral. 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 bytes privados tiver de 4 a 5 GB e SQL Server estiver usando Páginas Bloqueadas na Memória (AWE), grande parte dos bytes privados poderá estar vindo de fora do mecanismo SQL Server. Essa é uma técnica de aproximação.

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

  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 do Linked Server está causando um consumo significativo de memória, você poderá configurá-lo para ficar sem processo desabilitando a opção Permitir inprocess. Consulte Criar servidores vinculados para obter mais informações. Nem todos os provedores OLE DB do 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 a ser executado em um processo fora SQL Server especificando um valor de contexto de 4 (servidor OLE local (.exe). Para obter mais informações, consulte sp_OACreate.

Uso de memória interna por mecanismo de SQL Server

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

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

• Consulte o DMV dos funcionários de memória 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 forma como ela é alterada quando você vê essas mensagens de erro.

  DBCC MEMORYSTATUS
  

• Se você identificar um infrator claro entre os funcionários de memória, concentre-se em abordar as especificidades do consumo de memória para esse componente. Aqui estão diversos exemplos:

  • Se o funcionário MEMORYCLERK_SQLQERESERVATIONS de memória estiver consumindo memória, identifique consultas que estão usando enormes 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 dicas de min_grant_percent e max_grant_percent ). Você também pode criar um pool de governadores 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 memória lentos ou de baixo desempenho causados por concessões de memória em SQL Server.
  • Se um grande número de planos de consulta ad-hoc for armazenado em cache, o funcionário de CACHESTORE_SQLCP memória usará grandes quantidades de memória. Identifique consultas não parametrizadas cujos planos de consulta não podem ser reutilizados e parametrize-as convertendo-as em procedimentos armazenados, usando sp_executesqlou usando FORCED parametrização. Se você tiver habilitado o sinalizador de rastreamento 174, poderá desabilitá-lo para ver se isso resolve o problema.
  • Se o repositório CACHESTORE_OBJCP de cache do plano de objeto estiver consumindo muita memória, identifique quais procedimentos, funções ou gatilhos armazenados estão usando grandes quantidades de memória e possivelmente redesenhar o aplicativo. Normalmente, isso pode acontecer devido a grandes quantidades de bancos de dados ou esquemas com centenas de procedimentos em cada um deles.
  • Se o OBJECTSTORE_LOCK_MANAGER gerenciador de memória mostrar grandes alocações de memória, identifique consultas que aplicam muitos bloqueios e otimize-as usando índices. Encurtar transações que fazem com que os bloqueios não sejam liberados por longos períodos em determinados níveis de isolamento ou marcar se o escalonamento de bloqueio estiver 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 In-Memory OLTP provenientes do gerenciador de MEMORYCLERK_XTP memória, você poderá consultar Monitorar e solucionar problemas de uso de memória para In-Memorymetadados de tempdb com otimização de memória e OLTP (HkTempDB) de erros de memória.

Alívio rápido que pode disponibilizar memória

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

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

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

• memória máxima do servidor
• memória do servidor min

Se você não tiver configurado 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 Páginas Bloqueadas na configuração do servidor de memória .

Alterar ou mover carga de trabalho para fora do sistema

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

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

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

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

Liberar memória dentro de SQL Server

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

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

Reiniciar SQL Server serviço

Em alguns casos, se você precisar lidar com o esgotamento crítico da memória e SQL Server não for capaz de processar consultas, você poderá considerar reiniciar o serviço.

Considere usar Resource Governor para cenários específicos

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

Adicionar mais RAM no servidor físico ou virtual

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