Base de données tempdb

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.

Contenu connexe

Option SORT_IN_TEMPDB pour les index

Bases de données système

sys.databases (Transact-SQL)

sys.master_files (Transact-SQL)

Déplacer des fichiers de bases de données

Voir aussi

Autres ressources

Utilisation de tempdb dans SQL Server 2005