Métadonnées tempdb optimisées en mémoire (HkTempDB) en dehors des erreurs de mémoire

Article
07/30/2024

Cet article fournit des solutions pour résoudre les problèmes de mémoire insuffisante liés à la fonctionnalité de métadonnées optimisées tempdb en mémoire.

Symptômes

Une fois que vous avez activé la fonctionnalité de métadonnées optimisées tempdb en mémoire (HkTempDB), vous pouvez voir l’erreur 701 indiquant des exceptions de mémoire insuffisantes pour tempdb les allocations et le service SQL Server se bloque. En outre, vous pouvez constater que le commis de MEMORYCLERK_XTP mémoire pour l’OLTP en mémoire (Hekaton) augmente progressivement ou rapidement et ne se réduit pas. À mesure que la mémoire XTP augmente sans limite supérieure, le message d’erreur suivant s’affiche dans SQL Server :

Disalloiner les allocations de pages pour la base de données « tempdb » en raison d’une mémoire insuffisante dans le pool de ressources « default ». Consultez la rubrique « http://go.microsoft.com/fwlink/?LinkId=510837 » (éventuellement en anglais) pour plus d’informations.

Lorsque vous exécutez une requête sur le dm_os_memory_clerks DMV, vous pouvez voir que la mémoire allouée aux pages est élevée pour le commis à MEMORYCLERK_XTPla mémoire. Par exemple :

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

Résultat :

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

Diagnostiquer le problème

Pour collecter des données pour diagnostiquer le problème, procédez comme suit :

Collectez une trace légère ou un événement étendu (XEvent) pour comprendre tempdb la charge de travail et déterminez si la charge de travail a des transactions explicites longues avec des instructions DDL sur des tables temporaires.

Collectez la sortie des vues de gestion dynamiques suivantes pour analyser plus loin.

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

Cause et résolution

En utilisant les DMV pour vérifier la cause, vous pouvez voir différents scénarios du problème. Ces scénarios peuvent être divisés en deux catégories suivantes. Pour résoudre le problème, vous pouvez utiliser la résolution correspondante pour chaque scénario. Pour plus d’informations sur la façon d’atténuer le problème, consultez les étapes d’atténuation pour conserver la mémoire des métadonnées tempdb optimisée en mémoire.

Augmentation progressive de la consommation de mémoire XTP

Scénario 1

La DMV tempdb.sys.dm_xtp_system_memory_consumers ou tempdb.sys.dm_db_xtp_memory_consumers montre une grande différence entre les octets alloués et les octets utilisés.

Résolution : pour résoudre le problème, vous pouvez exécuter les commandes suivantes dans SQL Server 2019 CU13, SQL Server 2022 CU1 ou une version ultérieure qui a une nouvelle procédure sys.sp_xtp_force_gc pour libérer les octets alloués mais inutilisés.

Note

À compter de SQL Server 2022 CU1, vous devez exécuter la procédure stockée une seule fois.
```
/* 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
```
Scénario 2

La vue dynamique tempdb.sys.dm_xtp_system_memory_consumers affiche des valeurs élevées pour les octets alloués et utilisés pour les types VARHEAP de consommateurs de mémoire et LOOKASIDE.

Résolution : recherchez des transactions explicites longues impliquant des instructions DDL sur des tables temporaires et résolvez à partir de l’application en conservant les transactions courtes.

Note

Pour reproduire ce problème dans un environnement de test, vous pouvez créer une transaction explicite à l’aide d’instructions DDL (Data Definition Language) sur des tables temporaires et la laisser ouverte pendant longtemps lorsque d’autres activités ont lieu.
Scénario 3

La vue DMV tempdb.sys.dm_db_xtp_memory_consumers affiche des valeurs élevées pour les octets alloués et utilisés dans un allocateur d’objet volumineux (LOB) ou un tas de tables où Object_ID, XTP_Object_IDet Index_ID sont NULL.

Résolution : appliquez SQL Server 2019 CU16 pour le problème 14535149.
Scénario 4

La croissance continue du consommateur de mémoire de base de données XTP « VARHEAP\Storage internal heap » entraîne une erreur de mémoire insuffisante 41805.

Résolution : le problème 14087445 déjà identifié et résolu dans SQL Server 17 CU25 et versions ultérieures est examiné pour être transféré vers SQL Server 2019.

Pic soudain ou augmentation rapide de la consommation de mémoire XTP

Scénario 5

La vue DMV tempdb.sys.dm_db_xtp_memory_consumers affiche des valeurs élevées pour les octets alloués ou utilisés dans un tas de tables où Object_ID n’est pas NULL. La cause la plus courante de ce problème est une transaction ouverte de longue durée avec des instructions DDL sur des tables temporaires. Par exemple :
```
BEGIN TRAN
    CREATE TABLE #T(sn int)
    …
    …
COMMIT
```
Une transaction ouverte explicitement avec des instructions DDL sur des tables temporaires n’autorise pas le tas de tables et le tas lookaside à libérer pour les transactions suivantes à l’aide tempdb de métadonnées.

Résolution : recherchez des transactions explicites longues impliquant des instructions DDL sur des tables temporaires et résolvez à partir de l’application en conservant les transactions courtes.

Étapes d’atténuation pour conserver la mémoire des métadonnées tempdb optimisée en mémoire

Pour éviter ou résoudre des transactions de longue durée qui utilisent des instructions DDL sur des tables temporaires, les conseils généraux sont de limiter les transactions.
Augmentez la mémoire maximale du serveur pour permettre un fonctionnement suffisant de la mémoire en présence de charges de travail lourdes tempdb.
Exécutez sys.sp_xtp_force_gc régulièrement.
Pour protéger le serveur contre les conditions de mémoire insuffisantes potentielles, vous pouvez lier tempdb à un pool de ressources Resource Governor. Par exemple, créez un pool de ressources à l’aide MAX_MEMORY_PERCENT = 30de . Ensuite, utilisez la commande ALTER SERVER CONFIGURATION suivante pour lier le pool de ressources aux métadonnées tempdb optimisées en mémoire.
```
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON (RESOURCE_POOL = '<PoolName>');
```
Cette modification nécessite un redémarrage, même si les métadonnées optimisées en mémoire sont déjà activées tempdb . Pour plus d’informations, consultez l’article suivant :
- Configurez et utilisez des métadonnées tempdb optimisées en mémoire.
- Créez un pool de ressources.
Avertissement

Après avoir lié HktempDB à un pool, le pool peut atteindre son paramètre maximal, et toutes les requêtes qui utilisent tempdb peuvent échouer avec des erreurs de mémoire insuffisante. Par exemple :

Disalloiner les allocations de pages pour la base de données « tempdb » en raison d’une mémoire insuffisante dans le pool de ressources « HkTempDB ». Consultez la rubrique « http://go.microsoft.com/fwlink/?LinkId=510837 » (éventuellement en anglais) pour plus d’informations. Échec de l’allocation de page XTP en raison de la pression de la mémoire : FAIL_PAGE_ALLOCATION 8

Dans certaines circonstances, le service SQL Server peut potentiellement s’arrêter si une erreur de mémoire insuffisante se produit. Pour réduire la probabilité de ce problème, définissez le pool de MAX_MEMORY_PERCENT mémoire sur une valeur élevée.
La fonctionnalité de métadonnées optimisées tempdb en mémoire ne prend pas en charge chaque charge de travail. Par exemple, l’utilisation de transactions explicites avec des instructions DDL sur des tables temporaires qui s’exécutent pendant longtemps entraîne les scénarios décrits. Si vous avez ces transactions dans votre charge de travail et que vous ne pouvez pas contrôler leur durée, cette fonctionnalité n’est peut-être pas appropriée pour votre environnement. Vous devez tester largement avant d’utiliser HkTempDB.

Plus d’informations

Ces sections fournissent plus d’informations sur certains composants de mémoire impliqués dans les métadonnées optimisées tempdb en mémoire.

Allocateur de mémoire lookaside

Lookaside in-Memory OLTP est un allocateur de mémoire locale de thread pour aider à obtenir un traitement rapide des transactions. Chaque objet thread contient une collection d’allocateurs de mémoire lookaside. Chaque lookaside associé à chaque thread a une limite supérieure prédéfinie sur la quantité de mémoire qu’il peut allouer. Lorsque la limite est atteinte, le thread alloue de la mémoire à partir d’un pool de mémoire partagésVARHEAP (). La vue DMV sys.dm_xtp_system_memory_consumers agrège les données pour chaque type lookaside (memory_consumer_type_desc = 'LOOKASIDE') et le pool de mémoire partagée (memory_consumer_type_desc = 'VARHEAP' et memory_consumer_desc = 'Lookaside heap').

Consommateurs au niveau du système : tempdb.sys.dm_xtp_system_memory_consumers

Environ 25 types de consommateurs de mémoire lookaside sont la limite supérieure. Lorsque les threads ont besoin de plus de mémoire à partir de ces lookasides, la mémoire se déverse et est satisfaite du tas lookaside. Les valeurs élevées pour les octets utilisés peuvent être un indicateur d’une charge de travail importante tempdb constante et/ou d’une transaction ouverte longue qui utilise des objets temporaires.

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

Consommateurs au niveau de la base de données : tempdb.sys.dm_db_xtp_memory_consumers

L’allocateur métier est utilisé pour les données métier/hors ligne des tables système.
Le tas de tables est utilisé pour les lignes des tables système.

Les valeurs élevées pour les octets utilisés peuvent être l’indicateur d’une charge de travail importante tempdb constante et/ou d’une transaction ouverte longue qui utilise des objets temporaires.

Partager via