Errori di memoria insufficiente dei metadati tempdb ottimizzati per la memoria (HkTempDB)

Articolo
03/04/2024

Questo articolo fornisce soluzioni per risolvere i problemi di memoria insufficiente correlati alla funzionalità dei metadati ottimizzati tempdb per la memoria.

Sintomi

Dopo aver abilitato la funzionalità dei metadati ottimizzati tempdb per la memoria (HkTempDB), è possibile che venga visualizzato l'errore 701 che indica eccezioni di memoria insufficiente per tempdb le allocazioni e gli arresti anomali del servizio SQL Server. Inoltre, si può notare che l'impiegato MEMORYCLERK_XTP di memoria per In-Memory OLTP (Hekaton) sta crescendo gradualmente o rapidamente e non si riduce. Man mano che la memoria XTP cresce senza un limite superiore, viene visualizzato il messaggio di errore seguente in SQL Server:

Non consentire le allocazioni di pagine per il database 'tempdb' a causa della memoria insufficiente nel pool di risorse 'default'. Per altre informazioni, vedere 'http://go.microsoft.com/fwlink/?LinkId=510837'.

Quando si esegue una query sul dm_os_memory_clerks DMV , è possibile notare che la memoria delle pagine allocata è elevata per l'impiegato MEMORYCLERK_XTPdi memoria . Ad esempio:

SELECT type, memory_node_id, pages_kb 
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_XTP'

Risultato:

type                    memory_node_id                     pages_kb
------------------------------------------------------------ -------------- --------------------
MEMORYCLERK_XTP         0                                  60104496
MEMORYCLERK_XTP         64                                 0

Diagnosticare il problema

Per raccogliere dati per diagnosticare il problema, seguire questa procedura:

Raccogliere una traccia leggera o un evento esteso (XEvent) per comprendere tempdb il carico di lavoro e verificare se il carico di lavoro dispone di transazioni esplicite a esecuzione prolungata con istruzioni DDL in tabelle temporanee.

Raccogliere l'output delle DMV seguenti per analizzare ulteriormente.

SELECT * FROM sys.dm_os_memory_clerks
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_exec_sessions

-- from tempdb
SELECT * FROM tempdb.sys.dm_xtp_system_memory_consumers 
SELECT * FROM tempdb.sys.dm_db_xtp_memory_consumers

SELECT * FROM tempdb.sys.dm_xtp_transaction_stats
SELECT * FROM tempdb.sys.dm_xtp_gc_queue_stats
SELECT * FROM tempdb.sys.dm_db_xtp_object_stats

SELECT * FROM tempdb.sys.dm_db_xtp_transactions
SELECT * FROM tempdb.sys.dm_tran_session_transactions
SELECT * FROM tempdb.sys.dm_tran_database_transactions
SELECT * FROM tempdb.sys.dm_tran_active_transactions

Causa e risoluzione

Usando le DMV per verificare la causa, è possibile che vengano visualizzati scenari diversi del problema. Questi scenari possono essere suddivisi nelle due categorie seguenti. Per risolvere il problema, è possibile usare la risoluzione corrispondente per ogni scenario. Per altre informazioni su come risolvere il problema, vedere Procedura di mitigazione per mantenere sotto controllo la memoria dei metadati tempdb ottimizzata per la memoria.

Aumento graduale del consumo di memoria XTP

Scenario 1

La DMV tempdb.sys.dm_xtp_system_memory_consumers o tempdb.sys.dm_db_xtp_memory_consumers mostra una grande differenza tra i byte allocati e i byte usati.

Soluzione: per risolvere il problema, è possibile eseguire i comandi seguenti in SQL Server 2019 CU13, SQL Server 2022 CU1 o una versione successiva con una nuova procedura sys.sp_xtp_force_gc per liberare byte allocati ma inutilizzati.

Nota

A partire da SQL Server 2022 CU1, è necessario eseguire la stored procedure una sola volta.
```
/* Yes, 2 times for both*/
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc
GO
EXEC sys.sp_xtp_force_gc
```
Scenario 2

La DMV tempdb.sys.dm_xtp_system_memory_consumers mostra valori elevati per i byte allocati e usati per i tipi di consumer di VARHEAP memoria e LOOKASIDE.

Risoluzione: verificare la presenza di transazioni esplicite a esecuzione prolungata che coinvolgono istruzioni DDL nelle tabelle temporanee e risolvere dal lato applicazione mantenendo le transazioni brevi.

Nota

Per riprodurre questo problema in un ambiente di test, è possibile creare una transazione esplicita usando istruzioni DDL (Data Definition Language) su tabelle temporanee e lasciarla aperta per molto tempo quando si verificano altre attività.
Scenario 3

La DMV tempdb.sys.dm_db_xtp_memory_consumers mostra valori elevati per i byte allocati e usati in un allocatore lob (large object) o in un heap di tabella dove Object_ID, XTP_Object_IDe Index_ID sono NULL.

Risoluzione: applicare SQL Server 2019 CU16 per il problema 14535149.
Scenario 4

L'aumento continuo del consumer di memoria del database XTP "VARHEAP\Storage internal heap" causa un errore di memoria insufficiente 41805.

Soluzione: il problema 14087445 già identificato e risolto in SQL Server 17 CU25 e versioni successive è in fase di esame per essere convertito in SQL Server 2019.

Picco improvviso o aumento rapido del consumo di memoria XTP

Scenario 5

La DMV tempdb.sys.dm_db_xtp_memory_consumers mostra valori elevati per byte allocati o usati in un heap di tabella in cui Object_ID non NULLè . La causa più comune di questo problema è una transazione aperta in modo esplicito e a esecuzione prolungata con istruzioni DDL su tabelle temporanee. Ad esempio:
```
BEGIN TRAN
    CREATE TABLE #T(sn int)
    …
    …
COMMIT
```
Una transazione aperta in modo esplicito con istruzioni DDL nelle tabelle temporanee non consente di liberare l'heap della tabella e l'heap lookaside per le transazioni successive usando tempdb i metadati.

Risoluzione: verificare la presenza di transazioni esplicite a esecuzione prolungata che coinvolgono istruzioni DDL nelle tabelle temporanee e risolvere dal lato applicazione mantenendo le transazioni brevi.

Procedura di mitigazione per mantenere sotto controllo la memoria dei metadati tempdb ottimizzata per la memoria

Per evitare o risolvere le transazioni a esecuzione prolungata che usano istruzioni DDL in tabelle temporanee, le indicazioni generali consentono di mantenere le transazioni brevi.
Aumentare la memoria massima del server per consentire il funzionamento di memoria sufficiente in presenza di carichi di lavoro con utilizzo elevato di tempdb.
Eseguire sys.sp_xtp_force_gc periodicamente.
Per proteggere il server da potenziali condizioni di memoria insufficiente, è possibile associare tempdb a un pool di risorse Resource Governor. Ad esempio, creare un pool di risorse usando MAX_MEMORY_PERCENT = 30. Usare quindi il comando ALTER SERVER CONFIGURATION seguente per associare il pool di risorse ai metadati tempdb ottimizzati per la memoria.
```
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = '<PoolName>');
```
Questa modifica richiede un riavvio, anche se i metadati ottimizzati tempdb per la memoria sono già abilitati. Per ulteriori informazioni consulta:
- Configurare e usare metadati tempdb ottimizzati per la memoria.
- Creare un pool di risorse.
Avviso

Dopo l'associazione di HktempDB a un pool, il pool può raggiungere l'impostazione massima e tutte le query che usano tempdb potrebbero non riuscire con errori di memoria insufficiente. Ad esempio:

Non consentire le allocazioni di pagine per il database 'tempdb' a causa della memoria insufficiente nel pool di risorse 'HkTempDB'. Per altre informazioni, vedere 'http://go.microsoft.com/fwlink/?LinkId=510837'. Allocazione di pagine XTP non riuscita a causa della pressione della memoria: FAIL_PAGE_ALLOCATION 8

In determinate circostanze, il servizio SQL Server potrebbe potenzialmente arrestarsi se si verifica un errore di memoria insufficiente. Per ridurre le probabilità che ciò si verifichi, impostare il valore del pool di MAX_MEMORY_PERCENT memoria su un valore elevato.
La funzionalità dei metadati ottimizzata per tempdb la memoria non supporta ogni carico di lavoro. Ad esempio, l'uso di transazioni esplicite con istruzioni DDL in tabelle temporanee che vengono eseguite per lungo tempo comporterà gli scenari descritti. Se nel carico di lavoro sono presenti transazioni di questo tipo e non è possibile controllarne la durata, è possibile che questa funzionalità non sia appropriata per l'ambiente. È consigliabile eseguire test approfonditi prima di usare HkTempDB.

Ulteriori informazioni

Queste sezioni forniscono altri dettagli su alcuni dei componenti di memoria coinvolti nei metadati ottimizzati tempdb per la memoria.

Allocatore di memoria Lookaside

Lookaside in In-Memory OLTP è un allocatore di memoria locale del thread che consente di ottenere un'elaborazione rapida delle transazioni. Ogni oggetto thread contiene una raccolta di allocatori di memoria lookaside. Ogni lookaside associato a ogni thread ha un limite superiore predefinito per la quantità di memoria che può allocare. Quando viene raggiunto il limite, il thread alloca memoria da un pool di memoria condivisa con spill-over (VARHEAP). La DMV sys.dm_xtp_system_memory_consumers aggrega i dati per ogni tipo lookaside (memory_consumer_type_desc = 'LOOKASIDE') e il pool di memoria condiviso (memory_consumer_type_desc = 'VARHEAP' e memory_consumer_desc = 'Lookaside heap').

Consumer a livello di sistema: tempdb.sys.dm_xtp_system_memory_consumers

Circa 25 tipi di consumer di memoria lookaside sono il limite superiore. Quando i thread necessitano di più memoria da questi lookaside, la memoria si riversa su e viene soddisfatta dell'heap lookaside. I valori elevati per i byte usati possono essere un indicatore di un carico di lavoro elevato tempdb costante e/o di una transazione aperta a esecuzione prolungata che usa oggetti temporanei.

-- system memory consumers @ instance  
SELECT memory_consumer_type_desc, memory_consumer_desc, allocated_bytes, used_bytes
FROM sys.dm_xtp_system_memory_consumers

memory_consumer_type_desc     memory_consumer_desc                   allocated_bytes      used_bytes
------------------------- ------------------------------------------ -------------------- --------------------
VARHEAP                       Lookaside heap                             0                    0
PGPOOL                        256K page pool                             0                    0
PGPOOL                        4K page pool                               0                    0
VARHEAP                       System heap                                458752               448000
LOOKASIDE                     Transaction list element                   0                    0
LOOKASIDE                     Delta tracker cursor                       0                    0
LOOKASIDE                     Transaction delta tracker                  0                    0
LOOKASIDE                     Creation Statement Id Map Entry            0                    0
LOOKASIDE                     Creation Statement Id Map                  0                    0
LOOKASIDE                     Log IO proxy                               0                    0
LOOKASIDE                     Log IO completion                          0                    0
LOOKASIDE                     Sequence object insert row                 0                    0
LOOKASIDE                     Sequence object map entry                  0                    0
LOOKASIDE                     Sequence object values map                 0                    0
LOOKASIDE                     Redo transaction map entry                 0                    0
LOOKASIDE                     Transaction recent rows                    0                    0
LOOKASIDE                     Heap cursor                                0                    0
LOOKASIDE                     Range cursor                               0                    0
LOOKASIDE                     Hash cursor                                0                    0
LOOKASIDE                     Transaction dependent ring buffer          0                    0
LOOKASIDE                     Transaction save-point set entry           0                    0
LOOKASIDE                     Transaction FK validation sets             0                    0
LOOKASIDE                     Transaction partially-inserted rows set    0                    0
LOOKASIDE                     Transaction constraint set                 0                    0
LOOKASIDE                     Transaction save-point set                 0                    0
LOOKASIDE                     Transaction write set                      0                    0
LOOKASIDE                     Transaction scan set                       0                    0
LOOKASIDE                     Transaction read set                       0                    0
LOOKASIDE                     Transaction                                0                    0

Consumer a livello di database: tempdb.sys.dm_db_xtp_memory_consumers

L'allocatore LOB viene usato per le tabelle di sistema LOB/Off-row data.
L'heap delle tabelle viene usato per le righe delle tabelle di sistema.

I valori elevati per i byte usati possono essere l'indicatore di un carico di lavoro elevato tempdb costante e/o di una transazione aperta a esecuzione prolungata che usa oggetti temporanei.