Solucionar problemas com erros de falta de memória no Banco de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Você poderá ver mensagens de erro quando o mecanismo do banco de dados SQL não tiver alocado memória suficiente para executar a consulta. Isso pode ser causado por vários motivos, incluindo os limites do objetivo de serviço selecionado, as demandas agregadas de memória da carga de trabalho e as demandas de memória pela consulta. Para saber mais sobre o limite de recursos de memória para Bancos de Dados SQL do Azure, confira Gerenciamento de recursos no Banco de Dados SQL do Azure.

Observação

Este artigo se concentra no Banco de Dados SQL do Azure. Para saber mais sobre como solucionar problemas com memória do SQL Server, confira MSSQLSERVER_701.

Experimente as seguintes vias de investigação em resposta a:

  • Código de erro 701 com a mensagem "Não há memória suficiente do sistema no pool de recursos '%ls' para executar a consulta".
  • Código de erro 802 com a mensagem "Não há memória suficiente disponível no pool de buffers".

Exibir eventos de memória insuficiente

Se você encontrar erros de memória insuficiente, avalie sys.dm_os_out_of_memory_events. Essa exibição inclui informações de causa insuficiente da memória, que é determinada por um algoritmo heurístico e fornecida com um grau finito de confiança.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

Investigar alocação de memória

Se os erros de falta de memória persistirem no Banco de Dados SQL do Azure, considere pelo menos aumentar temporariamente o objetivo de nível de serviço do banco de dados no portal do Azure. Se os erros de memória insuficiente persistirem, use as consultas a seguir para procurar concessões de memória de consulta muito altas que possam contribuir para a condição de memória insuficiente. Execute as consultas de exemplo a seguir no banco de dados que apresentou erro (não no banco de dados master do servidor lógico do SQL do Azure).

Usar a exibição de gerenciamento dinâmico para exibir eventos de memória insuficiente

O sys.dm_os_out_of_memory_events permite visibilidade para os eventos e causas de eventos de falta de memória (OOM) no Banco de Dados SQL do Azure. O evento estendido summarized_oom_snapshot faz parte da sessão de evento existente system_health para simplificar a detecção. Para obter mais informações, consulte sys.dm_os_out_of_memory_events e o Blog: Uma nova maneira de solucionar erros de memória no mecanismo de banco de dados.

Usar DMVs para exibir os administradores de memória

Comece com uma ampla investigação, caso o erro de memória tenha ocorrido recentemente, e o exame da alocação de memória aos administradores de memória. Os administradores de memória são internos ao mecanismo de banco de dados para esse Banco de Dados SQL do Azure. Os principais administradores de memória em termos de páginas alocadas podem indicar que tipo de consulta ou recurso do SQL Server está consumindo mais memória.

SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY pages_kb DESC;
GO
SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY virtual_memory_committed_kb DESC;
  • Alguns administradores comuns de memória, como MEMORYCLERK_SQLQERESERVATIONS, são resolvidos da melhor forma pela identificação de consultas com grandes concessões de memória e pelo aprimoramento do desempenho com melhor indexação e ajuste de índice.
  • Embora OBJECTSTORE_LOCK_MANAGER não esteja relacionado a concessões de memória, espera-se que seja um número alto quando as consultas declaram muitos bloqueios, por exemplo, devido ao escalonamento de bloqueio desabilitado ou a transações muito grandes.
  • Espera-se que alguns administradores sejam a utilização mais alta: MEMORYCLERK_SQLBUFFERPOOL é quase sempre o principal; já CACHESTORE_COLUMNSTOREOBJECTPOOL é alto quando os índices columnstore são usados. É esperada uma utilização mais alta por esses administradores.

Para saber mais sobre tipos de administrador de memória, confira sys.dm_os_memory_clerks.

Usar DMVs para investigar consultas ativas

Na maioria dos casos, a consulta com falha não é a causa do erro.

A consulta de exemplo a seguir ao Banco de Dados SQL do Azure retorna informações importantes sobre transações que estão atualmente mantendo ou aguardando concessões de memória. Buscar as principais consultas identificadas para exame e ajuste de desempenho e avaliar se elas estão sendo executadas conforme o esperado. Considere o momento de consultas de relatório com uso intensivo de memória ou operações de manutenção.

--Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb
, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan
, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads
, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;

Você pode optar por usar a instrução KILL para interromper uma consulta em execução no momento que estiver mantendo ou aguardando uma grande concessão de memória. Use essa instrução com cuidado, especialmente quando processos críticos estiverem em execução. Para obter mais informações, confira KILL (Transact-SQL).

Usar Repositório de Consultas para investigar uso de memória de consulta anterior

Embora a consulta de exemplo anterior reporte apenas os resultados de consulta ao vivo, a consulta a seguir usa o Repositório de Consultas para retornar informações sobre a execução de consulta anterior. Isso pode ser útil na investigação de um erro de memória ocorrido no passado.

A consulta de exemplo a seguir ao Banco de Dados SQL do Azure retorna informações importantes sobre execuções de consulta registradas pelo Repositório de Consultas. Buscar as principais consultas identificadas para exame e ajuste de desempenho e avaliar se elas estão sendo executadas conforme o esperado. Observe o filtro de tempo em qsp.last_execution_time para restringir os resultados a um histórico recente. Você pode ajustar a cláusula TOP para produzir mais ou menos resultados, dependendo do seu ambiente.

SELECT TOP 10 PERCENT --limit results
  a.plan_id, query_id, plan_group_id, query_sql_text
, query_plan = TRY_CAST(query_plan as XML)
, avg_query_max_used_memory
, min_query_max_used_memory
, max_query_max_used_memory
, last_query_max_used_memory 
, last_execution_time  
, query_count_executions
    FROM (
    SELECT 
      qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    , last_execution_time = MAX(qsp.last_execution_time)
    , query_count_executions = SUM(qsrs.count_executions) 
    , avg_query_max_used_memory = AVG(qsrs.avg_query_max_used_memory)
    , min_query_max_used_memory = MIN(qsrs.min_query_max_used_memory)
    , max_query_max_used_memory = MAX(qsrs.max_query_max_used_memory)
    , last_query_max_used_memory = MAX(qsrs_latest.last_query_max_used_memory) --only from latest result
    FROM sys.query_store_plan AS qsp 
    INNER JOIN sys.query_store_query AS qsq
        ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id 
    INNER JOIN sys.query_store_runtime_stats AS qsrs
        ON qsp.plan_id = qsrs.plan_id 
    INNER JOIN (SELECT plan_id
            , last_query_max_used_memory 
            , rownum = ROW_NUMBER() OVER (PARTITION BY plan_id ORDER BY last_execution_time DESC)
            FROM sys.query_store_runtime_stats qsrs) AS qsrs_latest
        ON qsrs_latest.plan_id = qsp.plan_id
        AND qsrs_latest.rownum = 1 --use latest last_query_max_used_memory per plan_id
    WHERE DATEADD(hour, -24, sysdatetime()) < qsp.last_execution_time --past 24 hours only
    AND qsrs_latest.last_query_max_used_memory > 0
    GROUP BY qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    ) AS a
ORDER BY max_query_max_used_memory DESC, avg_query_max_used_memory DESC;

Eventos estendidos

Além das informações anteriores, poderá ser útil capturar um rastreamento das atividades no servidor para investigar detalhadamente um problema de falta de memória do Banco de Dados SQL do Azure.

Há duas maneiras de capturar rastreamentos no SQL Server; Eventos estendidos (XEvents) e rastreamentos do Profiler. No entanto, o SQL Server Profiler é uma tecnologia de rastreamento preterida sem suporte no banco de dados SQL do Azure. O Eventos Estendidos é a tecnologia de rastreamento mais recente que permite mais versatilidade e menos impacto no sistema observado e sua interface é integrada ao SSMS (SQL Server Management Studio). Para obter mais informações sobre a consulta a eventos estendidos no Banco de Dados SQL do Azure, veja Eventos estendidos no Banco de Dados SQL do Azure.

Consulte o documento que explica como usar o Assistente de Nova Sessão de Eventos Estendidos no SSMS. No entanto, para Bancos de Dados SQL do Azure, o SSMS fornece uma subpasta “Eventos Estendidos” em cada banco de dados no Pesquisador de Objetos. Use uma sessão de Eventos Estendidos para capturar esses eventos úteis e identificar as consultas que os geram:

  • Erros de categoria:

    • error_reported
    • exchange_spill
    • hash_spill_details

  • Execução da Categoria:

    • excessive_non_grant_memory_used

  • Memória da categoria:

    • query_memory_grant_blocking
    • query_memory_grant_usage

  • summarized_oom_snapshot

    A captura de blocos de concessão de memória, vazamentos de concessão de memória ou concessões excessivas de memória pode ser uma possível dica de consulta que ocupe repentinamente mais memória do que tinha no passado e uma possível explicação para um erro de falta de memória em uma carga de trabalho existente. O evento estendido summarized_oom_snapshot faz parte da sessão de evento existente system_health para simplificar a detecção. Para obter mais informações, consulte o blog: Uma nova maneira de solucionar erros de memória no mecanismo de banco de dados.

OLTP na memória sem memória

Você poderá encontrar Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation se estiver usando OLTP na memória. Reduza a quantidade de dados em tabelas com otimização de memória e em parâmetros com valor de tabela com otimização de memória ou dimensione o banco de dados para um objetivo de serviço mais alto a fim de ter mais memória. Para saber mais sobre problemas de falta de memória com OLTP na memória do SQL Server, confira Resolver problemas de falta de memória.

Obtenha suporte para o banco de dados SQL do Azure

Se os erros de falta de memória persistirem no Banco de Dados SQL do Azure, arquive uma solicitação de suporte do Azure selecionando Obter Suporte no site Suporte do Azure.

Conteúdo relacionado