Partilhar via

Resolver problemas relacionados com erros de falta de memória com a Base de Dados SQL do Azure

  • Artigo

Aplica-se a:Banco de Dados SQL do Azure

Você pode ver mensagens de erro quando o mecanismo de banco de dados SQL não conseguiu alocar memória suficiente para executar a consulta. Este erro pode ser causado por várias razões, incluindo os limites do objetivo do serviço selecionado, exigências agregadas de memória de carga de trabalho e exigências de memória pela consulta. Para obter mais informações sobre o limite de recursos de memória para Bancos de Dados SQL do Azure, consulte Gerenciamento de recursos no Banco de Dados SQL do Azure.

Nota

Este artigo é focado no Banco de Dados SQL do Azure. Para obter mais informações sobre como solucionar problemas de falta de memória no SQL Server, consulte MSSQLSERVER_701.

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

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

Exibir eventos sem memória

Se encontrar erros de memória esgotada, veja sys.dm_os_out_of_memory_events. Esta visão inclui informações sobre a causa prevista de falta de memória, 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 a alocação de memória

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

Usar o Detran para exibir eventos sem memória

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 summarized_oom_snapshot evento estendido faz parte da sessão de evento existente system_health para simplificar a deteção. Para obter mais informações, consulte sys.dm_os_out_of_memory_events e blog: uma nova maneira de solucionar erros de falta de memória no mecanismo de banco de dados.

Usar DMVs para visualizar os funcionários de memória

Comece com uma investigação ampla, se o erro de falta de memória ocorreu recentemente, visualizando a alocação de memória para os funcionários de memória. Os funcionários de memória são internos ao mecanismo de banco de dados para este Banco de Dados SQL do Azure. Os principais funcionários de memória em termos de páginas alocadas podem ser informativos sobre qual 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 funcionários de memória comuns, como MEMORYCLERK_SQLQERESERVATIONS, são melhor resolvidos identificando consultas com grandes concessões de memória e melhorando seu 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 alto quando as consultas reivindicam muitos bloqueios, por exemplo, devido ao escalonamento de bloqueio desativado ou transações muito grandes.
  • Espera-se que alguns funcionários sejam a maior utilização: MEMORYCLERK_SQLBUFFERPOOL é quase sempre o funcionário superior, enquanto CACHESTORE_COLUMNSTOREOBJECTPOOL será alta quando os índices columnstore forem usados. Espera-se uma maior utilização por parte destes funcionários.

Para obter mais informações sobre tipos de servidor de memória, consulte sys.dm_os_memory_clerks.

Usar DMVs para investigar consultas ativas

Na maioria dos casos, a consulta que falhou não é a causa desse erro.

A consulta de exemplo a seguir para o 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. Direcione as principais consultas identificadas para exame e ajuste de desempenho e avalie se elas estão ou não sendo executadas conforme pretendido. Considere o tempo de consultas de relatórios 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 decidir usar a instrução KILL para interromper uma consulta em execução no momento que está segurando ou aguardando uma concessão de memória grande. Use esta instrução com cuidado, especialmente quando processos críticos estão em execução. Para obter mais informações, consulte KILL (Transact-SQL).

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

Enquanto a consulta de exemplo anterior relata apenas resultados de consulta em tempo real, a consulta a seguir usa o Repositório de Consultas para retornar informações sobre a execução de consultas anteriores. Isso pode ser útil na investigação de um erro de falta de memória que ocorreu no passado.

A consulta de exemplo a seguir para o Banco de Dados SQL do Azure retorna informações importantes sobre execuções de consulta registradas pelo Repositório de Consultas. Direcione as principais consultas identificadas para exame e ajuste de desempenho e avalie se elas estão ou não sendo executadas conforme pretendido. Observe o filtro de tempo ativado qsp.last_execution_time para restringir os resultados ao 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 expandidos

Além das informações anteriores, pode ser útil capturar um rastreamento das atividades no servidor para investigar minuciosamente um problema de falta de memória no 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 para o Banco de Dados SQL do Azure. O Extended Events é a mais recente tecnologia de rastreamento que permite mais versatilidade e menos impacto no sistema observado, e sua interface é integrada ao SQL Server Management Studio (SSMS). Para obter mais informações sobre como consultar eventos estendidos no Banco de Dados SQL do Azure, consulte 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

  • Categoria Memória:

    • 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 de memória excessivas pode ser uma pista potencial para uma consulta de repente assumir mais memória do que tinha no passado, e uma explicação potencial para um erro emergente de falta de memória em uma carga de trabalho existente. O summarized_oom_snapshot evento estendido faz parte da sessão de evento existente system_health para simplificar a deteção. Para obter mais informações, consulte Blog: Uma nova maneira de solucionar erros de falta de memória no mecanismo de banco de dados.

OLTP na memória sem memória

Você pode 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 parâmetros com valor de tabela otimizados para memória ou aumente a escala do banco de dados para um objetivo de serviço mais alto para ter mais memória. Para obter mais informações sobre problemas de falta de memória com o OLTP na memória do SQL Server, consulte Resolver problemas de falta de memória.

Obter suporte da Base de Dados SQL do Azure

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

Conteúdos relacionados