base de données tempdb

Article
08/12/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

Cet article décrit la tempdb base de données système, une ressource globale disponible pour tous les utilisateurs connectés à une instance de 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 :

les objets utilisateurs temporaires créés explicitement. Ils incluent les tables et index temporaires locaux ou globaux, les procédures stockées temporaires, les variables de table, les tables retournées dans des fonctions table et les curseurs.
Les objets internes créés par le moteur de base de données. En voici la liste :
- Les tables de travail afin de stocker les résultats intermédiaires pour les mises en spools, les curseurs, les tris et le stockage temporaire des des 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, qui sont des collections de pages de données contenant les lignes de données qui prennent en charge des fonctionnalités de contrôle de version de ligne. Il y a deux types de banques : une banque de versions commune et une banque de versions de construction d'index en ligne. Les banques de versions contiennent les éléments suivants :
- Les versions de ligne générées par les transactions de modification de données dans une base de données qui utilise READ COMMITTED via l'isolement basé sur le contrôle de version de ligne ou les transactions d'isolement d'instantané.
- Versions de ligne qui sont générées par les transactions de modification de données pour les fonctionnalités telles que : opérations d'index en ligne, MARS (Multiple Active Result Sets) et déclencheurs AFTER.

Les opérations effectuées dans tempdb font l’objet d’un enregistrement minimal pour permettre la restauration des transactions. tempdb est recréé chaque fois que SQL Server est démarré afin que le système commence toujours par une copie propre de la base de données. Les tables et les procédures stockées temporaires sont automatiquement supprimées à la déconnexion et aucune connexion n'est active lorsque le système est arrêté.

tempdb n’a jamais rien à enregistrer d’une session de SQL Server à 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. Les tailles de ces fichiers peuvent varier légèrement pour différentes é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.

Le nombre de fichiers de données secondaires dépend du nombre de processeurs (logiques) sur l’ordinateur. En règle 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 que de processeurs logiques. Si le nombre de processeurs logiques est supérieur à huit, utilisez huit fichiers de données. Si le conflit persiste, augmentez le nombre de fichiers de données par multiples de quatre jusqu’à ce que le conflit diminue à un niveau acceptable ou modifiez la charge de travail/le code.

La valeur par défaut du nombre de fichiers de données est basée sur les directives générales de l’article KB 2154845.

Pour vérifier les paramètres actuels de croissance et de taille pour tempdb, interrogez la vue tempdb.sys.database_files.

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

Pour déplacer les données de tempdb et les fichiers journaux, consultez Déplacer des 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
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	ACTIVÉ	Oui
AUTO_SHRINK	OFF	Non
AUTO_UPDATE_STATISTICS	ACTIVÉ	Oui
AUTO_UPDATE_STATISTICS_ASYNC	OFF	Oui
CHANGE_TRACKING	OFF	Non
CONCAT_NULL_YIELDS_NULL	OFF	Oui
CURSOR_CLOSE_ON_COMMIT	OFF	Oui
CURSOR_DEFAULT	GLOBAL	Oui
Options de disponibilité de base de données	ONLINE MULTI_USER READ_WRITE	Non Non Non
DATE_CORRELATION_OPTIMIZATION	OFF	Oui
DB_CHAINING	ACTIVÉ	Non
ENCRYPTION	OFF	Non
MIXED_PAGE_ALLOCATION	OFF	Non
NUMERIC_ROUNDABORT	OFF	Oui
PAGE_VERIFY	CHECKSUM pour les nouvelles installations de SQL Server NONE pour les mises à niveau de SQL Server	Oui
PARAMETERIZATION	SIMPLE	Oui
QUOTED_IDENTIFIER	OFF	Oui
READ_COMMITTED_SNAPSHOT	OFF	Non
RECOVERY	SIMPLE	Non
RECURSIVE_TRIGGERS	OFF	Oui
Options de Service Broker	ENABLE_BROKER	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

Le comportement d’Azure tempdb SQL Database diffère du comportement de SQL Server, d’Azure SQL Managed Instance et de SQL Server sur des machines virtuelles Azure.

tempdb dans SQL Database

Les bases de données uniques et mises en pool dans Azure SQL Database prennent en charge les tables temporaires globales et les procédures stockées temporaires globales étendues au niveau de la base de données, et stockées dans tempdb. Les tables temporaires globales et les procédures stockées temporaires globales sont partagées pour toutes les sessions utilisateur exécutées dans la même base de données. Les sessions utilisateur d’autres bases de données n’ont pas accès aux tables temporaires globales. Pour plus d’informations, consultez Database scoped global temporary tables (Azure SQL Database).

Pour les bases de données uniques et mises en pool dans Azure SQL Database, seules les bases de données système et tempdb la master base de données sont accessibles. Pour plus d’informations, consultez Qu’est-ce qu’un serveur logique dans Azure ?

Pour en savoir plus sur tempdb les tailles 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

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 gérée. De même, toutes les bases de données système sont accessibles.

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

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

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'un instantané 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.
Définition de la base de données sur 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 n'ont accès qu'aux objets qu'ils possèdent, sauf s'ils ont reçu des autorisations supplémentaires. Il est possible de révoquer l’autorisation de connexion à tempdb pour empêcher un utilisateur d’utiliser tempdb. Cela n’est pas recommandé, car certaines opérations de routine nécessitent l’utilisation de tempdb.

Optimiser les performances tempdb dans SQL Server

La taille et l’emplacement physique de la base de données tempdb peuvent influer sur les performances d’un système. Par exemple, si la taille définie pour tempdb est trop petite, une partie de la charge de traitement du système peut être prise en charge automatiquement tempdb à la taille requise pour prendre en charge la charge de travail chaque fois que vous redémarrez l’instance de SQL Server.

Si possible, utilisez l’initialisation instantanée de fichiers pour améliorer les performances des opérations de croissance de 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 permet de limiter le rythme de croissance de tempdb pour ne pas impacter les performances. La base de données tempdb doit être définie de façon à autoriser la croissance automatique pour augmenter l’espace disque en cas d’exceptions non prévues.

Les fichiers de données doivent être de taille égale dans chaque groupe de fichiers, car SQL Server utilise un algorithme de remplissage proportionnel qui favorise les allocations dans les fichiers avec plus d’espace libre. Le fait de diviser tempdb en plusieurs fichiers de données de taille égale procure un niveau élevé d’efficacité parallèle dans les opérations qui utilisent tempdb.

Définissez l’incrément de croissance du fichier sur une taille raisonnable et définissez-le sur le même incrément dans tous les fichiers de données, afin d’empêcher les tempdb fichiers de base de données de croître d’une valeur trop petite. Si la croissance du fichier est trop petite par rapport à la quantité de données en cours d’écriture tempdb, tempdb il peut être nécessaire de développer fréquemment par le biais d’événements de croissance automatique. Les événements de croissance automatique affectent négativement les performances.

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

 SELECT FileName = df.name,
   current_file_size_MB = df.size*1.0/128,
   max_size = CASE df.max_size
     WHEN 0 THEN 'Autogrowth is off.'
     WHEN -1 THEN 'Autogrowth is on.'
     ELSE 'Log file grows to a maximum size of 2 TB.'
   END,
   growth_value =
     CASE
       WHEN df.growth = 0 THEN df.growth
       WHEN df.growth > 0 AND df.is_percent_growth = 0 THEN df.growth*1.0/128.0
       WHEN df.growth > 0 AND df.is_percent_growth = 1 THEN df.growth
     END,
   growth_increment_unit =
     CASE
       WHEN df.growth = 0 THEN 'Size is fixed.'
       WHEN df.growth > 0 AND df.is_percent_growth = 0  THEN 'Growth value is MB.'
       WHEN df.growth > 0 AND df.is_percent_growth = 1  THEN 'Growth value is a percentage.'
     END
FROM tempdb.sys.database_files AS df;
GO

Placez la base de données tempdb sur un sous-système d’E/S rapide. Si plusieurs disques sont directement attachés, utilisez l'agrégation de disques. Il n’est pas obligatoire que les fichiers ou groupes de fichiers de données tempdb se trouvent sur des disques ou des broches différents, sauf si vous observez également des goulots d’étranglement d’E/S.

Placez la base de données tempdb sur des disques différents de ceux que les bases de données utilisateur emploient.

Note

Même si l’option DELAYED_DURABILITY de base de données est définie sur DISABLED pour tempdb, SQL Server utilise des validations différées pour vider tempdb les modifications du journal sur le disque, car tempdb elle est créée au démarrage et n’a pas besoin d’exécuter le processus de récupération.

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 (update) utilisés.
La surcharge d’enregistrement pour tempdb a été réduite pour consommer moins de bande passante d’E/S disque sur le fichier journal tempdb.
Le programme d’installation ajoute plusieurs fichiers de données tempdb lors de l’installation d’une nouvelle instance. Vous pouvez effectuer cette tâche à l’aide du nouveau contrôle d’entrée de l’interface utilisateur dans la section Configuration du moteur de base de données et du paramètre de ligne de commande /SQLTEMPDBFILECOUNT. Par défaut, le programme d’installation ajoute huit fichiers de données tempdb ou autant de fichiers de données que de processeurs logiques, la valeur la plus petite étant retenue.
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 extensions 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 !.
Pour le groupe de fichiers primaire, la propriété AUTOGROW_ALL_FILES est activée et la propriété ne peut pas être modifiée.

Introduit dans SQL Server 2017 (14.x)

L’expérience d’installation de SQL améliore les conseils pour l’allocation initiale tempdb de fichiers. 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.
Une nouvelle sys.dm_tran_version_store_space_usage DMV est introduite dans SQL Server 2017 pour suivre l’utilisation du magasin de versions par base de données. Cette nouvelle vue de gestion dynamique sera utile pour surveiller tempdb l’utilisation du magasin de versions pour les administrateurs de base de données qui peuvent planifier tempdb de manière proactive le dimensionnement en fonction de la configuration requise pour le magasin de versions par base de données.
De nouvelles fonctionnalités de traitement de requêtes intelligentes telles que les jointures adaptatives et les commentaires d’allocation de mémoire réduisent les dépassements de mémoire sur les exécutions consécutives d’une requête, ce qui réduit l’utilisation inutile tempdb .

Introduit dans SQL Server 2019 (15.x)

À compter de SQL Server 2019 (15.x), SQL Server n’utilise pas l’option lors de l’ouverture FILE_FLAG_WRITE_THROUGH de fichiers pour tempdb permettre un débit maximal de disque. Étant tempdb donné qu’elles sont recréées au démarrage de SQL Server, ces options ne sont pas nécessaires, car elles concernent d’autres bases de données système et des bases de données utilisateur pour la cohérence 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 optimisées tempdb en mémoire suppriment un goulot d’étranglement sur les attentes tempdbPAGELATCH et déverrouillent un nouveau niveau d’extensibilité. Pour plus d’informations, regardez cette démonstration vidéo sur comment (et quand) : métadonnées TempDB optimisées en mémoire. Pour plus d’informations, lisez la supervision et résolvez les problèmes des métadonnées tempdb optimisées en mémoire.
Les mises à jour simultanées de l’espace libre de page (PFS) réduisent la contention du verrou de correctif dans toutes les bases de données, un problème le plus couramment observé dans tempdb. Cette amélioration modifie la façon dont la concurrence est managée avec les mises à jour PFS afin qu’elles puissent être mises à jour sous un verrou partagé, plutôt qu’avec un verrou exclusif. Ce comportement est activé par défaut dans toutes les bases de données (y compris TempDB) à compter de SQL Server 2019 (15.x). Pour plus d’informations sur les pages PFS, lisez sous les couvertures : GAM, SGAM et PAGES PFS.
Par défaut, une nouvelle installation de SQL Server sur Linux crée plusieurs tempdb fichiers de données, en fonction du nombre de cœurs logiques (avec jusqu’à huit fichiers de données). Cela ne s’applique pas aux mises à niveau de versions mineures ou majeures sur place. Chaque tempdb fichier 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)

SQL Server 2022 (16.x) a introduit une scalabilité améliorée avec les améliorations de la concurrence du verrou de page système. Les mises à jour simultanées des pages GAM (Global Allocation Map) et SGAM (Shared Global Allocation Map) réduisent la contention des verrous de page durant l’allocation et la désallocation de pages et d’étendues de données. Ces améliorations s’appliquent à toutes les bases de données utilisateur et bénéficient particulièrement aux charges de travail tempdb lourdes. Pour plus d’informations sur les pages GAM et SGAM, lisez sous les couvertures : GAM, SGAM et PFS. Pour plus d’informations, regardez les améliorations de concurrence de latch de page système (Ep. 6) | Données exposées.

Métadonnées tempdb à mémoire optimisée

La contention tempdb des métadonnées est historiquement un goulot d’étranglement pour l’extensibilité de nombreuses charges de travail exécutées sur SQL Server. SQL Server 2019 (15.x) introduit une nouvelle fonctionnalité qui fait partie de la famille de fonctionnalités de base de données en mémoire : métadonnées optimisées tempdb en mémoire.

Cette fonctionnalité supprime efficacement ce goulot d’étranglement et déverrouille un nouveau niveau d’extensibilité pour tempdbles charges de travail lourdes. Dans SQL Server 2019 (15.x), les tables système impliquées dans la gestion des métadonnées de table temporaires peuvent être déplacées dans des tables sans verrou, non durables et optimisées en mémoire.

Note

Actuellement, la fonctionnalité de métadonnées optimisées tempdb en mémoire n’est pas disponible dans Azure SQL Database ou Azure SQL Managed Instance.

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

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

Pour bénéficier de cette nouvelle fonctionnalité, utilisez le script suivant :

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

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 le serveur ne parvient pas à démarrer pour une raison quelconque après que vous avez activé des métadonnées tempdb à mémoire optimisée, vous pouvez ignorer la fonctionnalité en démarrant l’instance SQL Server avec la configuration minimale via l’option de démarrage -f. Vous pouvez alors désactiver la fonctionnalité et redémarrer SQL Server en mode normal.

Pour protéger le serveur contre les conditions de mémoire insuffisante potentielles, vous pouvez lier tempdb à une liste de ressources partagées. Pour ce faire, utilisez la commande ALTER SERVER plutôt que les étapes suivies habituellement pour lier un pool de ressources partagées à une base de données.

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

Cette modification nécessite également un redémarrage, même si les métadonnées optimisées en mémoire sont déjà activées tempdb .

LImitations tempdb à mémoire optimisée

L’activation et la désactivation de la fonctionnalité ne sont pas dynamiques. En raison des modifications intrinsèques qui doivent être apportées à la structure de tempdb, un redémarrage est nécessaire pour activer ou désactiver la fonctionnalité.
Une transaction n’est pas autorisée à accéder aux tables à mémoire optimisée dans plus d’une base de données. Toute transaction qui implique une table à mémoire optimisée dans une base de données utilisateur ne peut pas parallèlement accéder à des vues système tempdb. Si vous essayez d’accéder aux tempdb vues système dans la même transaction qu’une table mémoire optimisée dans une base de données utilisateur, vous recevez l’erreur suivante :
```
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.
```
Exemple :
```
BEGIN TRAN;

SELECT *
FROM tempdb.sys.tables;  -----> Creates a user in-memory OLTP transaction in tempdb

INSERT INTO <user database>.<schema>.<mem-optimized table>
VALUES (1); ----> Tries to create a user in-memory OLTP transaction in the user database but will fail

COMMIT TRAN;
```
Les requêtes exécutées sur les tables à mémoire optimisée ne prennent pas en charge les indicateurs de verrouillage et d’isolation. Les requêtes sur les vues de catalogue tempdb à mémoire optimisée ne respectent donc pas les indicateurs de verrouillage et d’isolation. Comme avec d’autres vues de catalogue système dans SQL Server, toutes les transactions par rapport aux vues système se trouvent dans READ COMMITTED (ou dans ce cas, READ COMMITTED SNAPSHOT) isolation.
Les 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.
En raison de la limitation sur les index columnstore, 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 à mémoire optimisée sont activées.
Une procédure stockée système est disponible pour provoquer manuellement le moteur en mémoire à libérer de la mémoire liée aux lignes supprimées de données en mémoire éligibles au garbage collection. Cela peut vous aider à résoudre les erreurs de mémoire optimisées pour les métadonnées tempdb (HkTempDB) spécifiques. Pour plus d’informations, consultez sys.sp_xtp_force_gc (Transact-SQL).

Note

Ces limitations s’appliquent uniquement lorsque vous référencez des vues système tempdb. Si vous le souhaitez, vous pouvez créer une table temporaire dans la même transaction lorsque vous accédez à une table à mémoire optimisée dans une base de données utilisateur.

Planification de la capacité de tempdb dans SQL Server

La détermination de la taille tempdb appropriée dans un environnement de production SQL Server dépend de nombreux facteurs. Comme décrit précédemment, ces facteurs incluent la charge de travail existante et les fonctionnalités SQL Server utilisées.

Nous vous recommandons d’analyser la charge de travail existante en effectuant les tâches suivantes dans un environnement de test SQL Server :

Définissez la croissance automatique pour tempdb .
Exécutez les requêtes individuelles ou les fichiers de trace de la charge de travail, et superviser l’utilisation de l’espace dans tempdb.
Exécutez les opérations de maintenance des index, comme leur reconstruction, et supervisez l’espace de tempdb.
Utilisez les valeurs d’utilisation de l’espace des étapes précédentes pour prédire votre utilisation totale en termes de charge de travail. Ajustez cette valeur pour une activité simultanée prévue, puis définissez la taille de tempdb en conséquence.

Surveiller l’utilisation de tempdb

L’épuisement de l’espace tempdb disque peut entraîner des interruptions significatives dans l’environnement de production SQL Server. Il peut également empêcher des applications qui exécutent d’effectuer des opérations. Vous pouvez utiliser la vue de gestion dynamique sys.dm_db_file_space_usage pour surveiller l’espace disque utilisé dans les tempdb fichiers.

Par exemple, les quatre exemples de scripts suivants recherchent la quantité d’espace libre dans tempdb, la quantité d’espace utilisé par le magasin de versions, la quantité d’espace utilisé par les objets internes et l’espace utilisé par les objets utilisateur :

 -- Determining the amount of free space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by the version store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by internal objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

-- Determining the amount of space used by user objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM tempdb.sys.dm_db_file_space_usage;

Pour superviser l’activité d’allocation et de désallocation de 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 et les variables de table qui utilisent un espace disque volumineux dans tempdb. Vous pouvez également utiliser plusieurs compteurs pour superviser l’espace libre disponible dans tempdb, ainsi que les ressources qui utilisent tempdb.

Par exemple, utilisez le script suivant pour obtenir l’espace tempdb consommé par les objets internes dans toutes les tâches en cours d’exécution dans chaque session :

-- Obtaining the space consumed by internal objects in all currently running tasks in each 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 le script suivant pour rechercher l’espace tempdb consommé par les objets internes dans la session active, pour les tâches en cours d’exécution et terminées :

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

base de données tempdb

Vue d’ensemble

Propriétés physiques de tempdb dans SQL Server

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

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

tempdb dans Azure SQL

tempdb dans SQL Database

tempdb dans SQL Managed Instance

Restrictions

Autorisations

Optimiser les performances tempdb dans SQL Server

Amélioration des performances dans tempdb pour SQL Server

Introduit dans SQL Server 2016 (13.x)

Introduit dans SQL Server 2017 (14.x)

Introduit dans SQL Server 2019 (15.x)

Introduit dans SQL Server 2022 (16.x)

Métadonnées tempdb à mémoire optimisée

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

LImitations tempdb à mémoire optimisée

Planification de la capacité de tempdb dans SQL Server

Surveiller l’utilisation de tempdb

Étapes suivantes

Ressources supplémentaires

Ressources supplémentaires