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

Si applica a: Database SQL di Azure

È possibile che vengano visualizzati messaggi di errore quando il motore di database SQL non è riuscito ad allocare 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 di risorse di memoria per i database di Azure SQL, vedere Gestione delle risorse in Azure SQL Database.

Nota

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

Provare le seguenti vie di indagine in risposta a:

  • Codice di errore 701 con messaggio di errore "Memoria di sistema insufficiente nel pool di risorse '%ls' per eseguire questa query".
  • Codice di errore 802 con messaggio di errore "Memoria insufficiente disponibile nel pool di buffer".

Visualizzare gli eventi di memoria insufficiente

Se si verificano errori di memoria insufficiente, esaminare sys.dm_os_out_of_memory_events. Introdotta nel gennaio 2022, questa vista include informazioni stimate sulla causa della memoria determinate da un algoritmo euristico e fornite 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 vengono mantenuti in Azure SQL Database, considerare almeno l'aumento temporaneamente dell'obiettivo del livello di servizio del database nel portale di Azure. Se gli errori di memoria insufficiente vengono mantenuti, usare le query seguenti per cercare concessioni di memoria di 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 Azure SQL).

Usare DMV per visualizzare gli eventi di memoria insufficiente

A partire da aprile 2022 è stata aggiunta una nuova dmv (Dynamic Management View) per consentire la visibilità degli eventi e le cause di eventi di memoria insufficiente (OOM) in Azure SQL Database, sys.dm_os_out_of_memory_events. Per altre informazioni, vedere sys.dm_os_out_of_memory_events.

Usare DMV per visualizzare i clerk di memoria

Iniziare con un'analisi generale, se l'errore di memoria insufficiente si è verificato di recente, visualizzando l'allocazione di memoria ai impiegati di memoria. I clek di memoria sono interni al motore di database per questo database Azure SQL Database. I principali impiegati di memoria in termini di pagine allocate potrebbero essere informativi per il tipo di query o funzionalità di SQL Server sta consumando 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, vengono 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 dei blocchi disabilitata o di transazioni molto grandi.
  • Si prevede che alcuni impiegati siano il più alto utilizzo: MEMORYCLERK_SQLBUFFERPOOL è quasi sempre il impiegato principale, mentre CACHESTORE_COLUMNSTOREOBJECTPOOL sarà elevato quando vengono usati gli indici columnstore. È previsto l'utilizzo più elevato di questi impiegati.

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 di questo errore.

La query di esempio seguente per Azure SQL Database restituisce informazioni importanti sulle transazioni che attualmente contengono 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;

È possibile decidere di usare l'istruzione KILL per arrestare una query attualmente in esecuzione che contiene o attende una concessione di memoria di grandi dimensioni. Usare attentamente questa istruzione, soprattutto quando vengono eseguiti processi critici. Per altre informazioni, vedere KILL (Transact-SQL).

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

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

La query di esempio seguente per Azure SQL Database restituisce informazioni importanti sulle esecuzioni di query registrate dal 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 per qsp.last_execution_time 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 Azure SQL Database.

Esistono due modi per acquisire tracce in SQL Server; Eventi estesi (XEvent) e tracce del profiler. Tuttavia, SQL Server Profiler è deprecata tecnologia di traccia non supportata per Azure SQL Database. Gli eventi estesi sono la tecnologia di traccia più recente che consente maggiore versatilità e minore impatto sul sistema osservato e la sua interfaccia è integrata in SQL Server Management Studio (SSMS). Per altre informazioni sull'esecuzione di query sugli eventi estesi in Azure SQL Database, vedere Eventi estesi in Azure SQL Database.

Fare riferimento al documento che illustra come usare la Creazione guidata nuova sessione eventi estesi in SSMS. Per Azure SQL database, 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 di 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

L'acquisizione di blocchi di concessioni di memoria, distribuzioni di concessioni di memoria o concessioni di memoria eccessive potrebbe essere un potenziale indizio per una query che acquisisce improvvisamente più memoria di quanto aveva in passato e una potenziale spiegazione di un errore di memoria insufficiente emergente in un carico di lavoro esistente.

OLTP in memoria insufficiente

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

Ottenere il supporto del database Azure SQL

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

Passaggi successivi