base de données tempdb

Article
11/22/2023

S’applique à : SQL Server base de données Azure SQL Azure SQL Managed Instance

Cet article décrit la base de données système tempdb, une ressource globale disponible pour tous les utilisateurs connectés à une instance de , 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. Notamment :
- 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 étant recréée chaque fois que SQL Server est démarré, le système démarre toujours avec 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 à l’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.

File	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 de données et de journaux tempdb dans le serveur SQL

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

Dans Azure SQL Database, le comportement de tempdb diffère du comportement dans SQL Server, Azure SQL Managed Instance et SQL Server sur les 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, chaque base de données unique sur un serveur logique a son propre tempdb. Dans un pool élastique, tempdb est une ressource partagée par 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 les autres bases de données du pool.

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

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

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

Pour en savoir plus sur les tailles de tempdb dans Azure SQL Managed Instance, consultez 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 de 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 absorbée par la croissance automatique tempdb jusqu'à la taille requise pour supporter 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, parce que SQL Server utilise un algorithme de remplissage proportionnel qui privilégie les allocations dans les fichiers ayant davantage 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 à une taille raisonnable et définissez le même incrément dans tous les fichiers de données, afin d’éviter que les fichiers de la base de données tempdb ne s’accroissent d’une valeur trop faible. Si la croissance du fichier est trop faible par rapport à la quantité de données écrites dans tempdb, tempdb peut être amené à se développer fréquemment via des événements de croissance automatique. Les événements de croissance spontanée ont une incidence négative sur 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.

Remarque

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 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 vue de gestion dynamique sys.dm_tran_version_store_space_usage est introduite pour suivre l’utilisation de la banque des 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 de tempdb.

Introduit dans SQL Server 2019 (15.x).

À compter de SQL Server 2019 (15.x), SQL Server n’utilise pas l’option FILE_FLAG_WRITE_THROUGH lors de l’ouverture de fichiers pour tempdb pour permettre un débit maximal de disque. Étant donné que tempdb est recréé 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 TempDB optimisées en mémoire suppriment un goulot d’étranglement sur les attentes PAGELATCH dans tempdbet déverrouillent un nouveau niveau de scalabilité. Pour plus d’informations, regardez cette démonstration vidéo sur Comment (et quand) : Métadonnées TempDB à mémoire optimisée Pour plus d’informations, lisez surveillance et dépannage des métadonnées tempdb à mémoire optimisée
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 projecteurs : 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 fichier tempdb fait 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 projecteurs : Pages GAM, SGAM et PFS Pour plus d’informations, regardez les améliorations de concurrence du verrou de page système (Ep. 6) | Données exposées.

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

La contention de métadonnées dans tempdb a toujours été un goulot d’étranglement pour la scalabilité de nombreuses charges de travail s’exécutant sur SQL Server.. SQL Server 2019 (15.x) introduit une nouvelle fonctionnalité qui fait partie de la famille de fonctionnalités des bases de données en mémoire : Les métadonnées TempDB à mémoire optimisée.

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

Remarque

À l’heure actuelle, la fonctionnalité des métadonnées tempdb à mémoire optimisée n’est pas disponible dans Azure SQL Database ni dans Azure SQL Managed Instance.

Regardez cette vidéo de sept minutes pour obtenir une vue d’ensemble des scénarios et du mode d’utilisation des métadonnées tempdb à mémoire optimisée :

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 à 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 tempdb à mémoire optimisée sont déjà activées.

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 à des vues système tempdb 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 pour les autres vues du catalogue système de SQL Server, toutes les transactions sur les vues système sont isolées en READ COMMITTED (ou dans ce cas, en READ COMMITTED SNAPSHOT ).
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 du système est disponible pour demander manuellement au moteur en mémoire de libérer la mémoire liée aux lignes supprimées des données en mémoire qui sont éligibles pour le 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).

Remarque

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 appropriée pour tempdb 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 de 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 :

Activez la croissance automatique de 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.

Surveillez l'utilisation de tempdb

L’espace disque insuffisant dans tempdb peut entraîner des interruptions significatives dans l’environnement de production. 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 fichiers tempdb :

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;

Partager via

base de données tempdb

Vue d’ensemble

Propriétés physiques de tempdb dans SQL Server

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

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

Surveillez l'utilisation de tempdb

Commentaires

Ressources supplémentaires

Partager via

base de données tempdb

Vue d’ensemble

Propriétés physiques de tempdb dans SQL Server

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

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

Surveillez l'utilisation de tempdb

Contenu connexe

Commentaires

Ressources supplémentaires