Base de données tempdb

Article
06/25/2013

La base de données système tempdb est une ressource globale disponible pour tous les utilisateurs connectés à l'instance de SQL Server et sert de conteneur aux éléments suivants :

Les objets utilisateurs temporaires créés explicitement, tels que les tables temporaires locales ou globales, les procédures stockées temporaires, les variables de table ou les curseurs.
Des objets internes créés par le Moteur de base de données SQL Server, par exemple, les tables de travail servant à stocker des résultats intermédiaires pour les mises en attente ou le tri.
Les versions de ligne générées par les transactions de modification de données dans une base de données qui utilise l'isolement basé sur le contrôle de version de ligne read committed 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 sont soumises à un enregistrement minimal dans le journal. Cela permet de restaurer des transactions. La base de données tempdb est recréée chaque fois que SQL Server est démarré, de sorte que 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é. Par conséquent, aucune donnée de la base de données tempdb ne doit être enregistrée d'une session de SQL Server à l'autre. La sauvegarde et la restauration ne sont pas autorisées sur la base de données tempdb.

Propriétés physiques de tempdb

Le tableau suivant répertorie les valeurs de configuration initiales des fichiers de données et des journaux de tempdb. La taille de ces fichiers peut varier légèrement en fonction des éditions de SQL Server.

Fichier	Nom logique	Nom physique	Croissance du fichier
Données primaires	tempdev	tempdb.mdf	Croissance automatique de 10 pour cent jusqu'à ce que le disque soit plein
Journal	templog	templog.ldf	Croissance automatique de 10 % jusqu'à un maximum de 2 téraoctets

La taille de tempdb peut avoir des conséquences sur les performances d'un système. Par exemple, si la taille de tempdb est trop faible, la capacité de traitement du système pourrait être trop occupée à faire croître automatiquement la base de données pour qu'elle prenne en charge les impératifs de votre charge de travail à chaque démarrage de SQL Server. Vous pouvez éviter cette surcharge en augmentant la taille de tempdb.

Performances accrues dans tempdb

Dans SQL Server, les performances de tempdb connaissent les améliorations suivantes :

Les tables temporaires et les variables de table peuvent être 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 et réduit la contention d'allocation des pages.
Le protocole d'accès aux pages d'allocation est amélioré. Cela réduit le nombre de verrous de mise à jour utilisés.
La surcharge d'enregistrement pour tempdb est réduite. Cela réduit la consommation de bande passante au niveau des E/S de disque sur le fichier journal tempdb.
L'algorithme d'allocation des pages mixtes dans tempdb est amélioré.

Déplacement des fichiers de données et des journaux de tempdb

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

Options de base de données

Le tableau suivant répertorie les valeurs par défaut de chaque option de la base de données tempdb et précise si elles sont modifiables. 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	ON	Oui
AUTO_SHRINK	OFF	Non
AUTO_UPDATE_STATISTICS	ON	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	ON	Non
ENCRYPTION	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 SET de ALTER DATABASE (Transact-SQL).

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 à dbo.
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 données modifié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
Affectation de la valeur OFFLINE à la base de données.
Affectation de la valeur READ_ONLY à la base de données ou au groupe de fichiers primaire

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. Cependant, cela n'est pas recommandé, car certaines opérations courantes nécessitent l'utilisation de tempdb.