MSSQLSERVER_701

Aplica-se: SQL Server

Detalhes

Explicação

O erro 701 ocorre quando o SQL Server não aloca memória suficiente para executar uma consulta. A memória insuficiente pode ser causada por vários fatores que incluem configurações do sistema operacional, disponibilidade de memória física, outros componentes usam memória dentro do SQL Server ou limites de memória na carga de trabalho atual. Na maioria dos casos, a transação com falha não é a causa do erro. Em geral, as causas podem ser agrupadas em três:

Pressão de memória externa ou de SO

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 disponibilidade de memória baixa. O SQL Server é um dos poucos aplicativos projetados para responder à pressão de memória do SO diminuindo seu uso de memória. Isso significa que, se algum aplicativo ou driver solicitar memória, o SO 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 começar a reduzir seu uso de memória, seu pool de memória será reduzido e quaisquer componentes que precisem de memória podem não obtê-la. Você começa a receber o erro 701 e outros erros relacionados à memória. Para obter mais informações, consulte Arquitetura de memória do SQL Server

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. Há componentes que podem ser executados dentro do processo do SQL Server que são "externos" ao mecanismo do SQL Server. Os exemplos incluem DLLs como servidores vinculados, componentes SQLCLR, XPs (procedimentos estendidos) 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 em cache 20 milhões de linhas de dados provenientes 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 aumento de memória de uma DLL de servidor vinculado, por exemplo, faria com que o SQL Server começasse a diminuir seu uso de memória (veja acima) e criaria condições de memória baixa dos componentes dentro do SQL Server, causando erros como o erro 701.

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 ao erro 701. Há centenas de componentes, rastreados por meio do sys.dm_os_memory_clerks, 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 poder resolver isso ainda mais. Por exemplo, se você descobrir que o administrador de memória do OBJECTSTORE_LOCK_MANAGER está mostrando 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 um grande número de bloqueios e os otimizam usando índices, ou encurtam transações que mantêm bloqueios por longos períodos ou verificam 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 sys.dm_os_memory_clerks e suas descrições.

Ação do usuário

Se o erro 701 aparecer ocasionalmente ou por um breve período, pode haver um problema de memória de curta duração que se resolveu sozinho. 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 as etapas para solucionar problemas.

A lista a seguir descreve etapas gerais que ajudarão a solucionar problemas de erros de memória.

Ferramentas de diagnóstico e captura

As ferramentas de diagnóstico que permitirão coletar dados de solução de problemas são Monitor de Desempenho, sys.dm_os_memory_clerks e DBCC MEMORYSTATUS.

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

• Memory:Available MB
• Process:Working Set
• Process:Private Bytes
• SQL Server:Memory Manager: (todos os contadores)
• SQL Server:Buffer Manager: (todos os contadores)

Coletar saídas periódicas dessa consulta no SQL Server afetado

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

Pssdiag ou SQL LogScout

Um modo alternativo e automatizado de capturar esses pontos de dados é usar ferramentas como PSSDIAG ou SQL LogScout.

• Se você usar o Pssdiag, configure para capturar o coletor Perfmon e o coletor Diagnóstico Personalizado\Erro de Memória do 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.

Pressão externa: diagnóstico e soluções

• Para diagnosticar condições de memória baixa no sistema fora do processo de SQL Server, 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:

  • Memory:Available MB
  • Process:Working Set
  • Process:Private Bytes

  Aqui está um exemplo de coleção 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 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: diagnósticos e soluções

Para diagnosticar a pressão de memória interna causada por módulos (DLLs) dentro do SQL Server, use a seguinte abordagem:

• Se o SQL Server não estiver usando a opção Bloquear páginas 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 de memória geral 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 será proveniente de uma DLL (servidor vinculado, XP, SQLCLR etc.). Por exemplo, se Bytes privados tiver 300 GB e a Memória Total do Servidor tiver 250 GB, aproximadamente 50 GB da memória geral no processo será proveniente de fora do mecanismo do SQL Server.

• Se o SQL Server estiver usando páginas de bloqueio 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 de memória geral proveniente do mecanismo do SQL Server é refletido no contador SQL Server:Memory Manager:Total Server Memory (KB). Processo típico : Os valores de bytes privados podem variar entre 300 MB e 1-2 GB no geral. Se houver um uso significativo de Process:Private Bytes além desse uso típico, a diferença provavelmente será proveniente de uma DLL (servidor vinculado, XP, SQLCLR etc.). Por exemplo, se o contador de bytes privados for de 5 a 4 GB e o SQL Server estiver usando AWE (Bloquear páginas 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 essa consulta para examinar os módulos carregados (DLLs) e ver se algo não deveria estar lá

  SELECT * FROM sys.dm_os_loaded_modules
  

• Se você suspeitar que um módulo de servidor vinculado está causando um consumo de memória significativo, poderá configurá-lo para que fique fora do processo desabilitando a opção Permitir no processo. Consulte Criar servidores vinculados (Mecanismo de Banco de Dados do SQL Server) para obter mais informações. Nem todos os provedores OLEDB de servidor vinculado ficam sem processo; Entre em contato com o fabricante do produto para obter mais informações.

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

Uso interno de memória pelo mecanismo do SQL Server: diagnóstico e soluções

• Comece a coletar contadores do monitor de desempenho para SQL Server:SQL Server:Gerenciador de Buffer, 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 DBCC MEMORYSTATUS mais detalhada 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 de memória MEMORYCLERK_SQLQERESERVATIONS estiver consumindo memória, identifique as consultas que estão usando concessões de memória enormes e otimize-as por meio de índices, reescreva-as (remova ORDER by, por exemplo) ou aplique dicas de consulta.
  • Se um grande número de planos de consulta ad hoc for armazenado em cache, o funcionário de memória CACHESTORE_SQLCP 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_executesql, ou usando parametrização FORCED.
  • Se o armazenamento de cache do plano de objeto CACHESTORE_OBJCP estiver consumindo muita memória, faça o seguinte: identifique quais procedimentos armazenados, funções ou gatilhos estão usando muita memória e, possivelmente, reprojete o aplicativo. Comumente isso pode acontecer devido a grandes quantidades de banco de dados ou esquemas com centenas de procedimentos em cada um.
  • Se o administrador de memória OBJECTSTORE_LOCK_MANAGER estiver mostrando 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 bloqueios não sejam liberados por longos períodos em determinados níveis de isolamento ou verifique se o escalonamento de bloqueios está desabilitado.

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

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

• 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

    Observe se há configurações incomuns. Corrija-as conforme necessário. Considere mais 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 de bloqueio na memória, considere definir um valor específico para permitir alguma memória para o sistema operacional. Consulte Bloquear páginas na opção de configuração do servidor de memória .

• Verifique 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.

• 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 ideias sobre como configurar a memória para VMs, consulte este blog Virtualização: sobrecarga na memória e como detectá-la dentro da VM e Solução de problemas de desempenho de máquina virtual ESX/ESXi (sobrecarga de memória)

• Você pode executar os seguintes comandos DBCC para liberar vários caches de memória do SQL Server.

  • DBCC FREESYSTEMCACHE

  • DBCC FREESESSIONCACHE

  • DBCC FREEPROCCACHE

• Se você estiver usando o Resource Governor, recomendamos verificar as configurações do pool de recursos ou do grupo de cargas de trabalho e ver se elas não estão limitando muito a memória.

• Se o problema persistir, será necessário aprofundar as investigações e possivelmente aumentar os recursos do servidor (RAM).