base de données tempdb

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de données SQL dans Microsoft Fabric

Cet article décrit la base de données système tempdb, une ressource globale disponible pour tous les utilisateurs connectés à une instance du moteur de base de données dans SQL Server, Azure SQL Database ou Azure SQL Managed Instance.

Vue d’ensemble

La base de données système tempdb est une ressource globale qui contient les éléments suivants :

• Objets utilisateur qui sont créés explicitement. Notamment :

  • Tables et index temporaires globaux ou locaux sur ces tables
  • Procédures stockées temporaires
  • Variables de table
  • Tables renvoyées dans les fonctions de table
  • Curseurs

  Les objets utilisateur qui peuvent être créés dans une base de données utilisateur peuvent également être créés dans tempdb, mais ils sont créés sans garantie de durabilité et sont supprimés lorsque l’instance du moteur de base de données redémarre.

• Les objets internes créés par le moteur de base de données. Notamment :

  • Les tables de travail qui stockent les résultats intermédiaires pour les spouleurs, les curseurs, les tris et le stockage temporaire d’objets volumineux (LOB).
  • Les fichiers de travail correspondant aux opérations de jointures ou d'agrégations hachées.
  • les résultats de tris intermédiaires pour les opérations de création ou de reconstruction d'index (si SORT_IN_TEMPDB est spécifié) ou pour certaines requêtes GROUP BY, ORDER BY ou UNION.

  Chaque objet interne utilise un minimum de neuf pages : une page IAM et une étendue de huit pages. Pour plus d’informations sur les pages et les extensions, consultez Pages et étendues.

• Des banques de versions : collections de pages de données contenant les lignes de données qui prennent en charge le contrôle de version de ligne. Il existe deux types : un magasin de versions commun et un magasin de versions pour la création d’index en ligne. Les banques de versions contiennent les éléments suivants :

  • Versions de lignes générées par des transactions de modification de données dans une base de données utilisant des transactions d'isolation basées sur le contrôle de version des lignes, telles que READ COMMITTED ou SNAPSHOT.
  • Les versions de ligne sont générées par des transactions de modification de données pour les fonctionnalités telles que le service de résolution d'adresses de multidiffusion (MARS) et les déclencheurs AFTER.

Les opérations au sein de tempdb sont journalisées au minimum. tempdb est recréé chaque fois que le moteur de base de données est démarré afin que le système commence toujours par une base de données tempdb vide. Les procédures stockées temporaires et les tables temporaires locales sont supprimées automatiquement lorsque la session qui les a créées se déconnecte.

tempdb n’a jamais rien à sauvegarder d’une période de fonctionnement du moteur de base de données à une autre. La sauvegarde et la restauration ne sont pas autorisées pour la base de données tempdb.

Propriétés physiques de tempdb dans SQL Server

Le tableau suivant répertorie les valeurs de configuration initiales des fichiers de données et des journaux de tempdb dans SQL Server. Les valeurs sont basées sur les valeurs par défaut de la base de données model. La taille de ces fichiers peut varier légèrement en fonction des éditions de SQL Server.

Fichier	Nom logique	Nom physique	Taille initiale	Croissance du fichier
Données primaires	tempdev	tempdb.mdf	8 mégaoctets	Croissance automatique de 64 Mo jusqu’à saturation du disque.
Fichiers de données secondaires*	temp#	tempdb_mssql_#.ndf	8 mégaoctets	Croissance automatique de 64 Mo jusqu’à saturation du disque.
Journal	templog	templog.ldf	8 mégaoctets	Croissance automatique de 64 mégaoctets jusqu’à un maximum de 2 téraoctets.

Tous les fichiers de données tempdb doivent toujours avoir les mêmes paramètres de taille et de croissance initiales.

Nombre de fichiers de données tempdb

Selon la version du moteur de base de données, sa configuration et la charge de travail, tempdb peut nécessiter plusieurs fichiers de données pour atténuer la contention d’allocation.

Le nombre total recommandé de fichiers de données dépend du nombre de processeurs logiques sur l’ordinateur. En guise d’aide générale :

• Si le nombre de processeurs logiques est inférieur ou égal à huit, utilisez le même nombre de fichiers de données.
• Si le nombre de processeurs logiques est supérieur à huit, utilisez huit fichiers de données.
• Si l'on observe toujours une contention d'allocation tempdb, augmentez le nombre de fichiers de données par multiples de quatre jusqu'à ce que la contention soit réduite à des niveaux acceptables ou modifiez la charge de travail.

Pour plus d’informations, consultez les Recommandations pour réduire la contention d’allocation dans la base de données tempdb de SQL Server.

Pour vérifier la taille et les paramètres de croissance actuels pour tempdb, utilisez l’affichage catalogue sys.database_files dans tempdb.

Déplacer les fichiers de données et de journaux de tempdb dans SQL Server

Pour déplacer les données de tempdb et les fichiers de journal, consultez Déplacer les bases de données système.

Options de la base de données pour tempdb dans SQL Server

Le tableau ci-dessous indique la valeur par défaut de chaque option de la base de données tempdb, et précise si cette option est modifiable. Pour afficher les valeurs actuelles de ces options, utilisez l'affichage catalogue sys.databases .

Option de base de données	Valeur par défaut	Peut être modifiée
ACCELERATED_DATABASE_RECOVERY	OFF	Non
ALLOW_SNAPSHOT_ISOLATION	OFF	Oui
ANSI_NULL_DEFAULT	OFF	Oui
ANSI_NULLS	OFF	Oui
ANSI_PADDING	OFF	Oui
ANSI_WARNINGS	OFF	Oui
ARITHABORT	OFF	Oui
AUTO_CLOSE	OFF	Non
AUTO_CREATE_STATISTICS	ON	Oui
AUTO_SHRINK	OFF	Non
AUTO_UPDATE_STATISTICS	ON	Oui
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Oui
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN)	OFF	Non
CHANGE_TRACKING	OFF	Non
COMPATIBILITY_LEVEL	Dépend de la version du moteur de base de données. Pour plus d’informations, consultez ALTER DATABASE (Transact-SQL) pour le niveau de compatibilité.	Oui
CONCAT_NULL_YIELDS_NULL	OFF	Oui
CONTAINMENT	NONE	Non
CURSOR_CLOSE_ON_COMMIT	OFF	Oui
CURSOR_DEFAULT	GLOBAL	Oui
État de la base de données	ONLINE	Non
Mise à jour de base de données	READ_WRITE	Non
Accès utilisateur de base de données	MULTI_USER	Non
DATE_CORRELATION_OPTIMIZATION	OFF	Oui
DB_CHAINING	ON	Non
DELAYED_DURABILITY	DISABLED Indépendamment de cette option, la durabilité retardée est toujours activée sur tempdb.	Oui
ENCRYPTION	OFF	Non
MIXED_PAGE_ALLOCATION	OFF	Non
NUMERIC_ROUNDABORT	OFF	Oui
PAGE_VERIFY	CHECKSUM pour les nouvelles installations de SQL Server Une valeur PAGE_VERIFY existante peut être conservée lorsqu’une instance de SQL Server est mise à niveau en place.	Oui
PARAMETERIZATION	SIMPLE	Oui
QUOTED_IDENTIFIER	OFF	Oui
READ_COMMITTED_SNAPSHOT	OFF	Non
RECOVERY	SIMPLE	Non
RECURSIVE_TRIGGERS	OFF	Oui
Courtier de services	ENABLE_BROKER	Oui
TARGET_RECOVERY_TIME	60	Oui
TEMPORAL_HISTORY_RETENTION	ON	Oui
TRUSTWORTHY	OFF	Non

Pour obtenir une description de ces options de base de données, consultez Options ALTER DATABASE SET (Transact-SQL).

tempdb dans Azure SQL Database

Dans Azure SQL Database, certains aspects du comportement et de la configuration de tempdb diffèrent de SQL Server.

Pour les bases de données uniques, chaque base de données sur un serveur logique a sa propre tempdb. Dans un pool élastique, tempdb est une ressource partagée pour toutes les bases de données du même pool, mais les objets temporaires créés par une base de données ne sont pas visibles par d’autres bases de données dans le même pool élastique.

Les objets de tempdb, y compris les vues de catalogue et les vues de gestion dynamique (DMV), sont accessibles via une référence inter-bases de données à la base de données tempdb. Par exemple, vous pouvez interroger la vue sys.database_files :

SELECT file_id,
       type_desc,
       name,
       size,
       max_size,
       growth
FROM tempdb.sys.database_files;

Les tables temporaires globales dans Azure SQL Database sont limitées à la base de données. Pour plus d’informations, consultez Tables temporaires globales délimitées de base de données dans Azure SQL Database.

Pour en savoir plus sur les tailles de tempdb dans Azure SQL Database, consultez :

• Modèle d’achat vCore : bases de données uniques, bases de données mises en pool
• Modèle d’achat DTU : bases de données uniques, bases de données mises en pool

tempdb dans SQL Managed Instance

Dans Azure SQL Managed Instance, certains aspects du comportement de tempdb et de la configuration par défaut sont différents de SQL Server.

Vous pouvez configurer le nombre de fichiers tempdb, leurs incréments de croissance et leur taille maximale. Pour plus d’informations sur la configuration des paramètres tempdb dans Azure SQL Managed Instance, consultez Configurer les paramètres tempdb pour Azure SQL Managed Instance.

Azure SQL Managed Instance prend en charge les objets temporaires de la même façon que SQL Server, où toutes les tables temporaires globales et les procédures stockées temporaires globales sont accessibles par toutes les sessions utilisateur au sein de la même instance managée SQL.

Pour en savoir plus sur les tailles de tempdb dans Azure SQL Managed Instance, consultez limites des ressources.

tempdb dans la base de données SQL dans Fabric

Pour en savoir plus sur les tailles de tempdb dans la base de données SQL dans Microsoft Fabric, consultez la section limites des ressources dans Comparaison des fonctionnalités : Azure SQL Database et base de données SQL dans Microsoft Fabric.

Tout comme pour Azure SQL Database, les tables temporaires globales dans la Base de données SQL dans Microsoft Fabric sont délimitées par la base de données. Pour plus d’informations, consultez Tables temporaires globales délimitées de base de données dans Azure SQL Database.

Restrictions

Les opérations suivantes ne peuvent pas être effectuées sur la base de données tempdb :

• Ajout de groupes de fichiers
• Sauvegarde ou restauration de la base de données
• Modification du classement. Le classement par défaut est le classement du serveur.
• Modification du propriétaire de la base de données. La base de données tempdb appartient à sa.
• Création d'une capture instantanée de base de données.
• Suppression de la base de données
• Suppression de l'utilisateur Invité de la base de données
• Activation de la capture des changements de données.
• Participation à la mise en miroir de bases de données
• Suppression du groupe de fichiers primaire, du fichier de données primaire ou du fichier journal
• Changement du nom de la base de données ou du groupe de fichiers primaire
• Exécution de DBCC CHECKALLOC.
• Exécution de DBCC CHECKCATALOG.
• Paramétrage de la base de données à OFFLINE.
• Définition de la base de données ou du groupe de fichiers primaire sur READ_ONLY.

autorisations

Tous les utilisateurs peuvent créer des objets temporaires dans tempdb.

Les utilisateurs peuvent accéder uniquement à leurs propres objets non temporaires dans tempdb, sauf s’ils reçoivent des autorisations supplémentaires.

Il est possible de révoquer l’autorisation de CONNECT sur tempdb pour empêcher un utilisateur ou un rôle de base de données d’utiliser tempdb. Cela n’est pas recommandé, car de nombreuses opérations nécessitent l’utilisation de tempdb.

Optimiser les performances de tempdb dans SQL Server

La taille et le positionnement physique des fichiers tempdb peuvent affecter les performances. Par exemple, si la taille initiale de tempdb est trop petite, le temps et les ressources peuvent être consommés pour faire croître automatiquement tempdb jusqu'à la taille requise nécessaire pour prendre en charge la charge de travail chaque fois que l’instance du moteur de base de données est redémarrée.

• Si possible, utilisez l’IFI pour améliorer les performances des opérations de croissance des fichiers de données.
  • À compter de SQL Server 2022 (16.x), les événements de croissance des fichiers journaux des transactions jusqu’à 64 Mo peuvent également bénéficier de l’initialisation instantanée des fichiers. Pour plus d'informations, consultez l'Initialisation de fichiers instantanés et le journal des transactions.
• Pré-allouez l’espace de tous les fichiers de tempdb en définissant leur taille avec une valeur suffisamment élevée pour assumer la charge de travail habituelle de l’environnement. La préallocation empêche tempdb de se générer automatiquement trop souvent, ce qui peut affecter négativement les performances.
• Les fichiers de la base de données tempdb doivent être définis sur la croissance automatique pour fournir de l’espace pendant les événements de croissance non planifiés.
• La division tempdb en plusieurs fichiers de données de taille égale peut améliorer l’efficacité des opérations qui utilisent tempdb.
  • Pour éviter le déséquilibre d’allocation de données, les fichiers de données doivent avoir les mêmes paramètres de taille initiale et de croissance, car le moteur de base de données utilise un algorithme de remplissage proportionnel qui favorise les allocations dans les fichiers avec plus d’espace libre.
  • Définissez l’incrément de croissance du fichier sur une taille raisonnable, par exemple 64 Mo, et faites en sorte que l’incrément de croissance soit le même pour tous les fichiers de données afin d’éviter le déséquilibre de la croissance.

Pour vérifier les paramètres actuels de croissance et de taille de tempdb, utilisez la requête suivante :

SELECT name AS file_name,
       type_desc AS file_type,
       size * 8.0 / 1024 AS size_mb,
       max_size * 8.0 / 1024 AS max_size_mb,
       CAST(IIF(max_size = 0, 0, 1) AS bit) AS is_autogrowth_enabled,
       CASE WHEN growth = 0 THEN growth
            WHEN growth > 0 AND is_percent_growth = 0 THEN growth * 8.0 / 1024
            WHEN growth > 0 AND is_percent_growth = 1 THEN growth
       END
       AS growth_increment_value,
       CASE WHEN growth = 0 THEN 'Autogrowth is disabled.'
            WHEN growth > 0 AND is_percent_growth = 0  THEN 'Megabytes'
            WHEN growth > 0 AND is_percent_growth = 1  THEN 'Percent'
       END
       AS growth_increment_value_unit
FROM tempdb.sys.database_files;

Placez la base de données tempdb sur un sous-système d’E/S rapide. Les fichiers individuels de données ou groupes de fichiers de données tempdb ne sont pas obligés de se trouver sur des disques différents, sauf si vous observez des goulots d’étranglement d’E/S au niveau du disque.

S’il existe une contention d’E/S entre tempdb et les bases de données utilisateur, placez tempdb fichiers sur les disques qui diffèrent des disques utilisés par les bases de données utilisateur.

Remarque

Pour améliorer les performances, la durabilité retardée est toujours activée sur tempdb, même si l’option de base de données DELAYED_DURABILITY est définie sur DISABLED. Étant donné que tempdb est recréé au démarrage, il ne passe pas par un processus de récupération et ne fournit pas de garantie de durabilité.

Amélioration des performances dans tempdb pour SQL Server

Introduit dans SQL Server 2016 (13.x).

• Les tables temporaires et les variables de table sont mises en cache. La mise en cache permet aux opérations de création et de suppression des objets temporaires de s'exécuter très rapidement. La mise en cache réduit également l’allocation de pages et les conflits de métadonnées.
• Le protocole de verrouillage des pages d’allocation a été amélioré pour réduire le nombre de verrous UP (mise à jour) utilisés.
• La charge d'enregistrement pour tempdb a été réduite afin de diminuer la consommation de bande passante d'E/S disque sur le fichier journal tempdb.
• Le programme d’installation SQL ajoute plusieurs fichiers de données tempdb lors d’une nouvelle installation d’instance. Passez en revue les recommandations et configurez votre tempdb dans la page configuration du moteur de base de données du programme d’installation de SQL, ou utilisez le paramètre de ligne de commande /SQLTEMPDBFILECOUNT. Par défaut, le programme d’installation de SQL ajoute autant de fichiers de données tempdb que le nombre de processeurs logiques ou huit, selon ce qui est inférieur.
• S’il y a plusieurs fichiers de données tempdb, tous les fichiers continuent de croître automatiquement de la même manière et en même temps, sur la base des paramètres de croissance définis. L’indicateur de trace 1117 n’est plus nécessaire. Pour plus d’informations, consultez les modifications -T1117 et -T1118 pour les bases de données TEMPDB et utilisateur.
• Toutes les allocations dans tempdb utilisent des étendues uniformes. L’indicateur de trace 1118 n’est plus nécessaire. Pour plus d’informations sur les améliorations des performances dans tempdb, consultez l’article de blog TEMPDB – Fichiers, indicateurs de traces et mises à jour, Oh My !.
• La propriété AUTOGROW_ALL_FILES est toujours activée pour le groupe de fichiers PRIMARY.

Introduit dans SQL Server 2017 (14.x).

• L’expérience d’installation de SQL améliore les conseils pour l’allocation initiale de fichiers tempdb. Le programme d’installation SQL avertit les clients si la taille de fichier initiale est définie sur une valeur supérieure à 1 Go et si l’initialisation instantanée de fichier n’est pas activée, ce qui empêche les retards de démarrage de l’instance.
• La vue de gestion dynamique sys.dm_tran_version_store_space_usage effectue le suivi de l’utilisation du magasin de versions par base de données. Cette vue de gestion dynamique (DMV) est utile pour les administrateurs de base de données qui souhaitent planifier de manière proactive le dimensionnement de tempdb en fonction des besoins d'utilisation du magasin de versions par base de données.
• Les fonctionnalités de traitement intelligent des requêtes, telles que les jointures adaptatives et les commentaires d’allocation de mémoire, réduisent les débordements de mémoire sur les exécutions consécutives d’une requête, ce qui réduit l’utilisation de tempdb.

Introduit dans SQL Server 2019 (15.x).

• Le moteur de base de données n’utilise pas l’option FILE_FLAG_WRITE_THROUGH lors de l’ouverture de fichiers tempdb pour permettre un débit de disque maximal. Étant donné que tempdb est recréée au démarrage, cette option n’est pas nécessaire pour assurer la durabilité des données. Pour plus d’informations sur FILE_FLAG_WRITE_THROUGH, consultez les algorithmes de journalisation et de stockage de données qui étendent la fiabilité des données dans SQL Server.
• Les métadonnées TempDB à mémoire optimisée suppriment la contention de métadonnées d’objet temporaire dans tempdb.
• Les mises à jour simultanées de l’espace libre de page (PFS) réduisent la contention des verrous de page dans toutes les bases de données, problème le plus couramment observé dans tempdb. Cette amélioration modifie la gestion de la concurrence des mises à jour de page PFS afin qu’elles puissent être mises à jour sous un verrou partagé, plutôt qu’un verrou exclusif. Ce comportement est activé par défaut dans toutes les bases de données (y compris tempdb) à partir de SQL Server 2019 (15.x). Pour plus d’informations sur les pages PFS, consultez Sous le capot : pages GAM, SGAM et PFS.
• Par défaut, une nouvelle installation de SQL Server sur Linux crée plusieurs fichiers de données tempdb en fonction du nombre de coeurs logiques (avec jusqu’à 8 fichiers de données). Cela ne s’applique pas aux mises à niveau de versions mineures ou majeures sur place. Chaque tempdb fichier de données est de 8 Mo, avec une croissance automatique de 64 Mo. Ce comportement est similaire à l’installation de SQL Server par défaut sur Windows.

Introduit dans SQL Server 2022 (16.x).

• Introduction d’une extensibilité améliorée avec des améliorations de concurrence des verrous des pages système. Les mises à jour simultanées des pages de la carte d'allocation globale (GAM) et des pages de la carte d'allocation globale partagée (SGAM) réduisent la contention des verrous de page lors de l'allocation et de la désallocation des pages de données et des étendues. Ces améliorations s’appliquent à toutes les bases de données utilisateur et profitent particulièrement aux charges de travail lourdes dans tempdb. Pour plus d’informations sur les pages GAM et SGAM, consultez Sous le capot : pages GAM, SGAM et PFS. Pour plus d’informations, regardez la vidéo Améliorations de la concurrence de verrou de page système (épisode 6) | Data Exposed.

Métadonnées TempDB optimisées en mémoire

La contention des métadonnées d’objet temporaires a historiquement été un goulot d’étranglement pour l’extensibilité de nombreuses charges de travail SQL Server. Pour y remédier, SQL Server 2019 (15.x) a introduit une fonctionnalité qui fait partie de la base de données en mémoire famille de fonctionnalités : métadonnées TempDB optimisées en mémoire.

L’activation de la fonctionnalité de métadonnées TempDB optimisée pour la mémoire améliore ce goulot d’étranglement pour les charges de travail précédemment limitées par la contention des métadonnées des objets temporaires dans tempdb. À compter de SQL Server 2019 (15.x), les tables système impliquées dans la gestion des métadonnées d’objet temporaires peuvent devenir des tables sans verrou, non durables et optimisées en mémoire.

Conseil

En raison des limitations actuelles , nous vous recommandons d’activer les métadonnées TempDB optimisées en mémoire uniquement lorsque la contention des métadonnées d’objet se produit et a un impact significatif sur vos charges de travail.

La requête de diagnostic suivante retourne une ou plusieurs lignes si la contention de métadonnées d’objet temporaire se produit. Chaque ligne représente une table système et retourne le nombre de sessions qui prétendent accéder à cette table au moment où cette requête de diagnostic est exécutée.

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT(r.session_id)) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
      AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
      AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Regardez cette vidéo de sept minutes pour obtenir une vue d’ensemble de la façon et du moment d’utiliser la fonctionnalité de métadonnées TempDB optimisée en mémoire :

Remarque

Actuellement, la fonctionnalité de métadonnées TempDB optimisée en mémoire n’est pas disponible dans Azure SQL Database, la base de données SQL dans Microsoft Fabric et Azure SQL Managed Instance.

Configurer et utiliser des métadonnées TempDB optimisées en mémoire

Les sections suivantes incluent les étapes à suivre pour activer, configurer, vérifier et désactiver la fonctionnalité de métadonnées TempDB optimisée en mémoire.

Activer

Pour activer cette fonctionnalité, utilisez le script suivant :

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Pour plus d’informations, consultez ALTER SERVER. Cette modification de la configuration nécessite un redémarrage du service.

Vous pouvez vérifier si tempdb est à mémoire optimisée à l’aide de la commande T-SQL suivante :

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Si la valeur retournée est 1 et qu’un redémarrage s’est produit après l’activation de la fonctionnalité, la fonctionnalité est activée.

Si le serveur ne démarre pas pour une raison quelconque après avoir activé les métadonnées TempDB optimisées en mémoire, vous pouvez contourner la fonctionnalité en démarrant l’instance du moteur de base de données avec configuration minimale à l’aide de l’option de démarrage -f. Vous pouvez ensuite désactiver la fonctionnalité et supprimer l’option -f pour redémarrer le moteur de base de données en mode normal.

Lier à la liste de ressources partagées pour limiter l’utilisation de la mémoire

Pour protéger le serveur contre les conditions de mémoire insuffisante potentielles, nous vous recommandons de lier tempdb à un gouverneur de ressources pool de ressources qui limite la mémoire consommée par les métadonnées TempDB optimisées en mémoire. L’exemple de script suivant crée un pool de ressources et définit sa mémoire maximale sur 20%, active resource governoret lie tempdb au pool de ressources.

Cet exemple utilise 20% comme limite de mémoire à des fins de démonstration. La valeur optimale dans votre environnement peut être plus grande ou plus petite en fonction de votre charge de travail et peut changer au fil du temps si la charge de travail change.

CREATE RESOURCE POOL tempdb_resource_pool
WITH (MAX_MEMORY_PERCENT = 20);

ALTER RESOURCE GOVERNOR RECONFIGURE;

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON 
(RESOURCE_POOL = 'tempdb_resource_pool');

Cette modification nécessite également qu’un redémarrage du service prenne effet, même si les métadonnées TempDB optimisées en mémoire sont déjà activées.

Vérifier les liaisons de la liste de ressources partagées et surveiller l’utilisation de la mémoire

Pour vérifier que tempdb est lié à un pool de ressources et à surveiller les statistiques d’utilisation de la mémoire pour le pool, utilisez la requête suivante :

WITH resource_pool AS
(
SELECT p.pool_id,
       p.name,
       p.max_memory_percent,
       dp.max_memory_kb,
       dp.target_memory_kb,
       dp.used_memory_kb,
       dp.out_of_memory_count
FROM sys.resource_governor_resource_pools AS p
INNER JOIN sys.dm_resource_governor_resource_pools AS dp
ON p.pool_id = dp.pool_id
)
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS is_tempdb_memory_optimized_metadata_enabled,
       rp.name AS resource_pool_name,
       rp.max_memory_percent,
       rp.max_memory_kb,
       rp.target_memory_kb,
       rp.used_memory_kb,
       rp.out_of_memory_count
FROM sys.databases AS d
LEFT JOIN resource_pool AS rp
ON d.resource_pool_id = rp.pool_id
WHERE d.name = 'tempdb';

Supprimer la liaison de liste de ressources partagées

Pour supprimer la liaison de pool de ressources tout en conservant les métadonnées TempDB optimisées en mémoire activées, exécutez la commande suivante et redémarrez le service :

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Désactiver

Pour désactiver les métadonnées TempDB optimisées en mémoire, exécutez la commande suivante et redémarrez le service :

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED TEMPDB_METADATA = OFF;

Limitations des métadonnées TempDB optimisées en mémoire

• L’activation ou la désactivation de la fonctionnalité de métadonnées TempDB optimisée en mémoire nécessite un redémarrage.

• Dans certains cas, vous pouvez observer une utilisation élevée de la mémoire par le régisseur de mémoire MEMORYCLERK_XTP, ce qui entraîne des erreurs de mémoire insuffisante dans votre charge de travail.

  Pour voir l’utilisation de la mémoire par le commis MEMORYCLERK_XTP par rapport à tous les autres commis de mémoire et par rapport à la mémoire du serveur cible, exécutez la requête suivante :

  SELECT SUM(IIF(type = 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS memoryclerk_xtp_pages_mb,
         SUM(IIF(type <> 'MEMORYCLERK_XTP', pages_kb, NULL)) / 1024. AS other_pages_mb,
         SUM(committed_target_kb) / 1024. AS committed_target_memory_mb
  FROM sys.dm_os_memory_clerks
  CROSS JOIN sys.dm_os_sys_info;
  

  Si MEMORYCLERK_XTP mémoire est élevée, vous pouvez atténuer le problème comme suit :

  • Liez la base de données tempdb à un pool de ressources qui limite la consommation de mémoire par les métadonnées TempDB optimisées en mémoire. Pour plus d’informations, consultez Configurer et utiliser des métadonnées tempdb à mémoire optimisée.
  • Une procédure système de stockage peut être exécutée régulièrement pour libérer la mémoire MEMORYCLERK_XTP dont on n’a plus besoin. Pour plus d’informations, consultez sys.sp_xtp_force_gc (Transact-SQL).

  Pour plus d’informations, consultez Métadonnées tempdb à mémoire optimisée (HkTempDB) de mémoire insuffisante.

• Lorsque vous utilisez In-MemoryOLTP, une transaction unique n’est pas autorisée à accéder aux tables optimisées en mémoire dans plusieurs bases de données. En raison de cela, toute transaction de lecture ou d’écriture impliquant une table optimisée en mémoire dans une base de données utilisateur ne peut pas également accéder aux vues système tempdb dans la même transaction. Si cela se produit, vous recevez l’erreur 41317 :

  A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
  

  Cette limitation s’applique également aux autres scénarios où une transaction unique tente d’accéder à des tables mémoire optimisées dans plusieurs bases de données.

  Par exemple, vous pouvez obtenir l’erreur 41317 si vous interrogez l'sys.stats vue catalogue dans une base de données utilisateur qui contient des tables mémoire optimisées. Cela se produit, car la requête tente d’accéder aux données statistiques d’une table à mémoire optimisée dans la base de données utilisateur et aux métadonnées à mémoire optimisée dans tempdb.

  L’exemple de script suivant génère cette erreur lorsque les métadonnées TempDB optimisées en mémoire sont activées :

  BEGIN TRAN;
  
  -- Create an In-memory OLTP transaction that accesses a system view in tempdb
  SELECT name
  FROM tempdb.sys.tables;
  
  -- An attempt to create an In-memory OLTP transaction in the user database fails
  INSERT INTO <user database>.<schema>.<memory-optimized table>
  VALUES (1);
  
  COMMIT TRAN;
  

  Remarque

  Cette limitation ne s’applique pas aux tables temporaires. Vous pouvez créer une table temporaire dans la même transaction qui accède à une table optimisée en mémoire dans une base de données utilisateur.

• Les requêtes sur les vues de catalogue système utilisent toujours le niveau d’isolation READ COMMITTED. Lorsque les métadonnées TempDB optimisées en mémoire sont activées, les requêtes sur les vues de catalogue du système dans tempdb utilisent le niveau d’isolation SNAPSHOT. Dans les deux cas, les indicateurs de verrouillage ne sont pas respectés.

• Des index columnstore ne peuvent pas être créés sur les tables temporaires quand les métadonnées tempdb à mémoire optimisée sont activées.

  • Par conséquent, l’utilisation de la procédure stockée système sp_estimate_data_compression_savings avec le paramètre de compression de données COLUMNSTORE ou COLUMNSTORE_ARCHIVE n’est pas prise en charge lorsque les métadonnées TempDB optimisées en mémoire sont activées.

Planification de la capacité de tempdb dans SQL Server

La détermination de la taille appropriée pour tempdb dépend de nombreux facteurs. Ces facteurs incluent la charge de travail et les fonctionnalités du moteur de base de données utilisées.

Nous vous recommandons d’analyser tempdb consommation d’espace en effectuant les tâches suivantes dans un environnement de test où vous pouvez reproduire votre charge de travail classique :

• Activez la croissance automatique pour les fichiers tempdb. Tous les fichiers de données tempdb doivent avoir la même taille initiale et la même configuration de croissance automatique.
• Reproduire la charge de travail et surveiller l’utilisation de l’espace tempdb.
• Si vous utilisez la maintenance d’index périodique, exécutez vos tâches de maintenance et surveillez l’espace tempdb.
• Utilisez les valeurs d’utilisation d’espace maximal des étapes précédentes pour prédire votre utilisation totale de charge de travail. Ajustez cette valeur pour une activité simultanée prévue, puis définissez la taille de tempdb en conséquence.

Surveillez l'utilisation de tempdb

L’épuisement de l’espace disque dans tempdb peut entraîner des interruptions importantes et un temps d’arrêt de l’application. Vous pouvez utiliser la vue de gestion dynamique sys.dm_db_file_space_usage pour surveiller l’espace utilisé dans les fichiers tempdb.

Par exemple, l’exemple de script suivant recherche :

• Espace libre dans tempdb (sans tenir compte de l’espace libre sur le disque qui peut être disponible pour la croissance de tempdb)
• Espace utilisé par le magasin de version
• Espace utilisé par les objets internes
• Espace utilisé par les objets utilisateur

SELECT SUM(unallocated_extent_page_count) * 8.0 / 1024 AS tempdb_free_data_space_mb,
       SUM(version_store_reserved_page_count) * 8.0 / 1024 AS tempdb_version_store_space_mb,
       SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS tempdb_internal_object_space_mb,
       SUM(user_object_reserved_page_count) * 8.0 / 1024 AS tempdb_user_object_space_mb
FROM tempdb.sys.dm_db_file_space_usage;

Pour superviser l’activité d’allocation et de désallocation des pages dans tempdb au niveau de la session ou de la tâche, vous pouvez utiliser les vues de gestion dynamique sys.dm_db_session_space_usage et sys.dm_db_task_space_usage. Ces vues peuvent vous aider à identifier les requêtes, les tables temporaires ou les variables de table qui utilisent de grandes quantités d’espace tempdb.

Par exemple, utilisez l’exemple de script suivant pour obtenir l’espace tempdb alloué et libéré par des objets internes dans toutes les tâches en cours d’exécution dans chaque session :

SELECT session_id,
       SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
       SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

Utilisez l'exemple de script suivant pour rechercher l'espace tempdb alloué et actuellement consommé par les objets internes et utilisateurs pour chaque session et requête, pour les tâches en cours et terminées.

WITH tempdb_space_usage AS
(
SELECT session_id,
       request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_task_space_usage
UNION ALL
SELECT session_id,
       NULL AS request_id,
       user_objects_alloc_page_count + internal_objects_alloc_page_count AS tempdb_allocations_page_count,
       user_objects_alloc_page_count + internal_objects_alloc_page_count - user_objects_dealloc_page_count - user_objects_deferred_dealloc_page_count - internal_objects_dealloc_page_count AS tempdb_current_page_count
FROM sys.dm_db_session_space_usage
)
SELECT session_id,
       COALESCE(request_id, 0) AS request_id,
       SUM(tempdb_allocations_page_count * 8) AS tempdb_allocations_kb,
       SUM(IIF (tempdb_current_page_count >= 0, tempdb_current_page_count, 0) * 8) AS tempdb_current_kb
FROM tempdb_space_usage
GROUP BY session_id, COALESCE (request_id, 0)
ORDER BY session_id, request_id;

Contenu connexe

• Option SORT_IN_TEMPDB pour les index
• Bases de données système
• sys.databases
• sys.master_files
• Déplacer des fichiers de bases de données