Risolvere i problemi relativi a errori di memoria insufficiente con il database SQL di Azure

  • Articolo

Si applica a: Database SQL di Azure

È possibile che vengano visualizzati messaggi di errore quando il motore di database SQL non è riuscito ad assegnare memoria sufficiente per eseguire la query. Ciò può essere causato da vari motivi, tra cui i limiti dell'obiettivo di servizio selezionato, le richieste di memoria del carico di lavoro aggregate e le richieste di memoria da parte della query. Per altre informazioni sul limite delle risorse di memoria per le database SQL di Azure, vedere Gestione delle risorse in database SQL di Azure.

Nota

Questo articolo è incentrato sul database SQL di Azure. Per altre informazioni sulla risoluzione dei problemi di memoria insufficiente in SQL Server, vedere MSSQLSERVER_701.

Provare le vie di indagine seguenti in risposta a:

  • Codice errore 701 con messaggio di errore “Memoria di sistema insufficiente nel pool di risorse '%Is' per l'esecuzione della query”.
  • Codice di errore 802 con messaggio di errore "Memoria insufficiente disponibile nel pool di buffer".

Visualizzazione eventi di memoria insufficiente

Se si verificano errori di memoria insufficiente, esaminare sys.dm_os_out_of_memory_events. Questa vista include informazioni sulla causa stimata di memoria insufficiente, determinata da un algoritmo euristico e fornita con un grado di attendibilità finito.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

Analizzare l'allocazione di memoria

Se gli errori di memoria insufficiente persistono nel database SQL di Azure, prendere in considerazione almeno temporaneamente l'aumento dell'obiettivo del livello di servizio del database nel portale di Azure. Se gli errori di memoria insufficiente persistono, usare le seguenti query per cercare concessioni di memoria per query insolitamente elevate che potrebbero contribuire a una condizione di memoria insufficiente. Eseguire le query di esempio seguenti nel database che ha riscontrato l'errore (non nel master database del server logico SQL di Azure).

Usare DMV per visualizzare gli eventi memoria insufficiente

sys.dm_os_out_of_memory_events consente la visibilità degli eventi e delle cause di eventi di memoria insufficiente (OOM) nel database SQL di Azure. L'evento summarized_oom_snapshot esteso fa parte della sessione eventi esistente system_health per semplificare il rilevamento. Per altre informazioni, vedere sys.dm_os_out_of_memory_events e Blog: un nuovo modo per risolvere gli errori di memoria insufficiente nel motore di database.

Usare DMV per visualizzare i clerk di memoria

Iniziare con un’indagine generale, se l'errore di memoria insufficiente si è verificato di recente, visualizzando l'allocazione della memoria ai clerk di memoria. I clerk di memoria sono interni al motore di database per questo database SQL di Azure. I migliori impiegati di memoria in termini di pagine allocate potrebbero essere informativi sul tipo di query o funzionalità di SQL Server che utilizza la maggior parte della memoria.

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;
  • Alcuni impiegati di memoria comuni, ad esempio MEMORYCLERK_SQLQERESERVATIONS, sono risolti in modo ottimale identificando le query con concessioni di memoria di grandi dimensioni e migliorando le prestazioni con un'indicizzazione e un'ottimizzazione dell'indice migliori.
  • Anche se OBJECTSTORE_LOCK_MANAGER non è correlato alle concessioni di memoria, è previsto che sia elevato quando le query sostengono molti blocchi, ad esempio a causa dell'escalation blocchi disabilitata o di transazioni molto grandi.
  • Si prevede che alcuni clerk siano l'utilizzo più elevato: MEMORYCLERK_SQLBUFFERPOOL è quasi sempre il clerk principale, mentre CACHESTORE_COLUMNSTOREOBJECTPOOL sarà elevato quando vengono usati indici columnstore. È previsto l'utilizzo più elevato di questi clerk.

Per altre informazioni sui tipi di clerk di memoria, vedere sys.dm_os_memory_clerks.

Usare DMV per analizzare le query attive

Nella maggior parte dei casi, la query non riuscita non è la causa dell'errore.

La seguente query di esempio per database SQL di Azure restituisce informazioni importanti sulle transazioni attualmente in attesa o in attesa di concessioni di memoria. Specificare come destinazione le query principali identificate per l'analisi e l'ottimizzazione delle prestazioni e valutare se sono in esecuzione come previsto. Prendere in considerazione la tempistica delle query di report a elevato utilizzo di memoria o delle operazioni di manutenzione.

--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;

Provare a usare l'istruzione KILL per arrestare una query attualmente in esecuzione che contiene o attende una concessione di memoria di grandi dimensioni. Eseguire l'istruzione con cautela, soprattutto quando sono in esecuzione processi critici. Per altre informazioni, vedere KILL (Transact-SQL).

Usare Query Store per analizzare l'utilizzo della memoria delle query precedenti

Mentre la query di esempio precedente segnala solo i risultati delle query in tempo reale, la query seguente usa Query Store per restituire informazioni sull'esecuzione della query precedente. Ciò può essere utile per analizzare un errore di memoria insufficiente che si è verificato in passato.

La seguente query di esempio per database SQL di Azure restituisce informazioni importanti sulle esecuzioni di query registrate da Query Store. Specificare come destinazione le query principali identificate per l'analisi e l'ottimizzazione delle prestazioni e valutare se sono in esecuzione come previsto. Si noti il filtro temporale su qsp.last_execution_time per limitare i risultati alla cronologia recente. È possibile modificare la clausola TOP per produrre più o meno risultati a seconda dell'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;

Eventi estesi

Oltre alle informazioni precedenti, può essere utile acquisire una traccia delle attività nel server per analizzare accuratamente un problema di memoria insufficiente in database SQL di Azure.

Esistono due modi per acquisire tracce in SQL Server: Eventi estesi (XEvent) e tracce profiler. SQL Server Profiler, tuttavia, è una tecnologia di traccia deprecata per database SQL di Azure. Eventi estesi è la tecnologia di traccia più recente che consente maggiore versatilità e minore impatto sul sistema osservato e presenta un’interfaccia integrata in SQL Server Management Studio (SSMS). Per altre informazioni sulle query per gli eventi estesi nel database SQL di Azure, vedere Eventi estesi nel database SQL.

Fare riferimento al documento che illustra come usare la Creazione guidata nuova sessione di Eventi estesi in SSMS. Per i database SQL di Azure, tuttavia, SSMS fornisce una sottocartella Eventi estesi in ogni database in Esplora oggetti. Usare una sessione Eventi estesi per acquisire questi eventi utili e identificare le query che le generano:

  • Errori categoria:

    • error_reported
    • exchange_spill
    • hash_spill_details

  • Esecuzione categoria:

    • excessive_non_grant_memory_used

  • Memoria categoria:

    • query_memory_grant_blocking
    • query_memory_grant_usage

  • summarized_oom_snapshot

    L'acquisizione di blocchi di concessioni di memoria, la perdita di concessioni di memoria o un numero eccessivo di concessioni di memoria potrebbe essere un potenziale indizio di una query che assume improvvisamente più memoria rispetto al passato e una potenziale spiegazione per un errore di memoria insufficiente emergente in un carico di lavoro esistente. L'evento summarized_oom_snapshot esteso fa parte della sessione eventi esistente system_health per semplificare il rilevamento. Per altre informazioni, vedere Blog: Un nuovo modo per risolvere gli errori di memoria insufficiente nel motore di database.

Memoria insufficiente per OLTP in memoria

Si potrebbe incontrare Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation se si usa OLTP in memoria. Ridurre la quantità di dati nelle tabelle ottimizzate per la memoria e nei parametri con valori di tabella ottimizzati per la memoria o aumentare le prestazioni del database a un obiettivo di servizio superiore per avere più memoria. Per altre informazioni sui problemi di memoria insufficiente con OLTP in memoria di SQL Server, vedere Risolvere i problemi di memoria insufficiente.

Ricevere supporto del database SQL di Azure

Se gli errori di memoria insufficiente persistono in database SQL di Azure, inviare una richiesta di supporto ad Azure selezionando Ottieni supporto nel sito supporto di Azure.

Contenuto correlato