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

Articolo
07/30/2024

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

Sintomi

Dopo aver abilitato la funzionalità dei metadati ottimizzati per tempdb 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 potrebbe notare che il clerk MEMORYCLERK_XTP di memoria per OLTP in memoria (Hekaton) sta crescendo gradualmente o rapidamente e non si riduce indietro. Man mano che la memoria XTP aumenta senza un limite massimo, viene visualizzato il messaggio di errore seguente in SQL Server:

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

Quando si esegue una query sulla DMV dm_os_memory_clerks, è possibile notare che la memoria delle pagine allocata è elevata per il clerk 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 con esecuzione prolungata con istruzioni DDL nelle 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 diversi scenari 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 VARHEAP consumer di memoria e LOOKASIDE.

Soluzione: 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) nelle tabelle temporanee e lasciarlo aperto per molto tempo quando viene eseguita un'altra 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 heap di tabella in cui Object_ID, XTP_Object_IDe Index_ID sono NULL.

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

Il consumer di memoria del database XTP in continua crescita "VARHEAP\Storage internal heap" causa un errore di memoria insufficiente 41805.

Risoluzione: il problema 14087445 già identificato e risolto in SQL Server 17 CU25 e versioni successive è sottoposto a esame per essere convertito in SQL Server 2019.

Picco improvviso o rapido aumento del consumo di memoria XTP

Scenario 5

La DMV tempdb.sys.dm_db_xtp_memory_consumers mostra valori elevati per i 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 con esecuzione prolungata con istruzioni DDL nelle 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 consentirà all'heap di tabella e all'heap lookaside di liberarsi per le transazioni successive usando tempdb i metadati.

Soluzione: 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 archiviata la memoria dei metadati tempdb ottimizzata per la memoria

Per evitare o risolvere transazioni con esecuzione prolungata che usano istruzioni DDL nelle tabelle temporanee, il materiale sussidiario generale consiste nel mantenere le transazioni brevi.
Aumentare la memoria massima del server per consentire il funzionamento di memoria sufficiente in presenza di carichi di lavoro tempdb-heavy.
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 di 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 per rendere effettivo anche se i metadati ottimizzati tempdb per la memoria sono già abilitati. Per altre informazioni, vedi:
- Configurare e usare i 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 le query che usano tempdb potrebbero non riuscire con errori di memoria insufficiente. Ad esempio:

Non consentire allocazioni di pagine per il database 'tempdb' a causa di 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 di un utilizzo elevato della memoria: FAIL_PAGE_ALLOCATION 8

In determinate circostanze, il servizio SQL Server potrebbe arrestarsi se si verifica un errore di memoria insufficiente. Per ridurre la probabilità che ciò accada, impostare il pool di MAX_MEMORY_PERCENT memoria su un valore elevato.
La funzionalità dei metadati ottimizzata tempdb per la memoria non supporta ogni carico di lavoro. Ad esempio, l'uso di transazioni esplicite con istruzioni DDL nelle tabelle temporanee eseguite per molto tempo porterà agli scenari descritti. Se si dispone di tali transazioni nel carico di lavoro e non è possibile controllarne la durata, forse questa funzionalità non è appropriata per l'ambiente in uso. Prima di usare HkTempDB, è consigliabile eseguire un test approfondito.

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 OLTP in memoria è un allocatore di memoria locale del thread per 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 sulla quantità di memoria che può allocare. Quando viene raggiunto il limite, il thread alloca la memoria da un pool di memoria condivisa di tipo 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 condivisa (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 maggiore memoria da questi lookaside, la memoria si riversa su e è soddisfatta dell'heap lookaside. I valori elevati per i byte usati possono essere un indicatore di un carico di lavoro elevato costante tempdb 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 i dati LOB/Off-row.
L'heap di tabella 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 costante tempdb e/o di una transazione aperta a esecuzione prolungata che usa oggetti temporanei.

Condividi tramite