Partager via

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.

Overview

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. Il s’agit notamment des éléments suivants :

    • Tables et index temporaires globaux ou locaux sur ces tables
    • Procédures stockées temporaires
    • Variables volumineuses, y compris les variables de table
    • Tables renvoyées dans les fonctions de table
    • Cursors

    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. Il s’agit notamment des éléments suivants :

    • 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).
    • Fichiers de travail pour les opérations de correspondance de hachage ou d’agrégation de hachage.
    • 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. 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.

    À compter de SQL Server 2025 (17.x), lorsque la récupération accélérée de base de données (ADR) est activée, tempdbtempdb contient deux magasins de versions différents et indépendants :

    • Le magasin de versions traditionnel, utilisé pour les versions de ligne générées par les transactions dans les bases de données utilisateur qui n’ont pas d’ADR activée.
    • Le magasin de versions persistantes (PVS) est utilisé pour les versions de lignes générées par les transactions dans tempdb.

    Veillez à allouer suffisamment d’espace disque pour que les fichiers de données de tempdb contiennent les deux versions de magasins si ADR est activé dans tempdb. Selon votre charge de travail, la taille des fichiers de tempdb données peut avoir besoin d’augmenter pour contenir des données PVS.

    Pour plus d’informations sur tempdb l’utilisation de l’espace par le magasin de versions traditionnel, consultez Espace utilisé dans tempdb. Pour plus d’informations sur l’espace utilisé par PVS, consultez Espace utilisé par le magasin de versions persistantes (PVS).

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. Les opérations de sauvegarde et de restauration ne sont pas autorisées sur 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.

File Nom logique Nom physique Taille initiale Croissance des fichiers
Données principales 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.
Log 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 Yes1
ALLOW_SNAPSHOT_ISOLATION OFF Yes
ANSI_NULL_DEFAULT OFF Yes
ANSI_NULLS OFF Yes
ANSI_PADDING OFF Yes
ANSI_WARNINGS OFF Yes
ARITHABORT OFF Yes
AUTO_CLOSE OFF No
AUTO_CREATE_STATISTICS ON Yes
AUTO_SHRINK OFF No
AUTO_UPDATE_STATISTICS ON Yes
AUTO_UPDATE_STATISTICS_ASYNC OFF Yes
AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN) OFF No
CHANGE_TRACKING OFF No
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é.		 Yes
CONCAT_NULL_YIELDS_NULL OFF Yes
CONTAINMENT NONE No
CURSOR_CLOSE_ON_COMMIT OFF Yes
CURSOR_DEFAULT GLOBAL Yes
État de la base de données ONLINE No
Mise à jour de base de données READ_WRITE No
Accès utilisateur de base de données MULTI_USER No
DATE_CORRELATION_OPTIMIZATION OFF Yes
DB_CHAINING ON No
DELAYED_DURABILITY DISABLED

Indépendamment de cette option, la durabilité retardée est toujours activée sur tempdb.		 Yes
ENCRYPTION OFF No
MIXED_PAGE_ALLOCATION OFF No
NUMERIC_ROUNDABORT OFF Yes
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.		 Yes
PARAMETERIZATION SIMPLE Yes
QUOTED_IDENTIFIER OFF Yes
READ_COMMITTED_SNAPSHOT OFF No
RECOVERY SIMPLE No
RECURSIVE_TRIGGERS OFF Yes
Courtier de services ENABLE_BROKER Yes
TARGET_RECOVERY_TIME 60 Yes
TEMPORAL_HISTORY_RETENTION ON Yes
TRUSTWORTHY OFF No

1 Le paramétrage de ACCELERATED_DATABASE_RECOVERY sur ON dans tempdb est pris en charge à partir de SQL Server 2025 (17.x). Dans les versions précédentes de SQL Server, la modification de l’option ACCELERATED_DATABASE_RECOVERY de la tempdb base de données n’est pas autorisée.

Pour obtenir une description de ces options de base de données, consultez les options ALTER DATABASE SET.

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, est une ressource partagée pour toutes les bases de données du même pool, tempdb 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 :

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 tempdb les tailles de la base de données SQL dans Microsoft Fabric, consultez la section Limites des ressources dans la comparaison des fonctionnalités : Azure SQL Database et base de données SQL dans Microsoft Fabric.

De la même façon que Azure SQL Database, les tables temporaires globales dans la base de données SQL de Microsoft Fabric sont confiné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.

Limites

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.

Permissions

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.
  • 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.
  • À compter de SQL Server 2025 (17.x), envisagez d’activer la récupération accélérée de la base de données pour tempdb bénéficier des avantages de la restauration instantanée des transactions et de la troncation de journal agressive pour les transactions dans tempdb. Pour plus d’informations, consultez ADR dans tempdb.
    • L’activation ou la désactivation de l’ADR tempdb nécessite un redémarrage du moteur de la base de données pour prendre effet.

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 les fichiers tempdb sur des disques différents de ceux utilisés par les bases de données utilisateur.

Note

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éliorations apportées à tempdb pour SQL Server

Introduit dans SQL Server 2025 (17.x)

  • Tempdb la gouvernance des ressources spatiales applique une limite à la quantité totale d’espace consommée par une application ou une charge de tempdb travail utilisateur. Cela améliore la fiabilité et évite les pannes en empêchant les requêtes incontrôlées ou les charges de travail d'occuper une grande quantité d'espace dans tempdb. Pour plus d’informations, consultez Gouvernance des ressources spatiales Tempdb.
  • La récupération accélérée de la base de données est prise en charge dans tempdb, offrant une restauration instantanée des transactions et une troncature agressive du journal pour les transactions dans tempdb.

Introduit dans SQL Server 2022 (16.x).

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 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 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 trace et mises à jour, oh là là !.
  • La propriété AUTOGROW_ALL_FILES est toujours activée pour le groupe de fichiers PRIMARY.

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.

Tip

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 :

Note

Actuellement, la fonctionnalité de métadonnées TempDB optimisée en mémoire n’est pas disponible dans Azure SQL Database, SQL Database 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.

Enable

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

Disable

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 :

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

  • Lorsque vous utilisez In-Memory OLTP, une transaction unique n’est pas autorisée à accéder aux tables mémoire optimisées 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;

    Note

    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 disque libre qui peut être disponible pour la tempdb croissance).
  • Espace utilisé par le magasin de versions traditionnel.
    • Pour surveiller la taille du magasin de versions persistantes (PVS) lorsque la récupération de base de données accélérée (ADR) est activéetempdb, consultez Examiner la taille du PVS.
  • 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

  • Last updated on