Base de données tempdb
La base de données système tempdb est une ressource globale disponible pour tous les utilisateurs connectés au instance de SQL Server et utilisée pour contenir les é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.
Les objets internes créés par le moteur de base de données SQL Server, par exemple, les tables de travail pour stocker les résultats intermédiaires pour les pools 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. tempdb est recréé chaque fois que SQL Server est démarré afin 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, il n’y a jamais rien dans tempdb à enregistrer d’une session de SQL Server à une 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 affecter les performances d’un système. Par exemple, si la taille de tempdb est trop petite, le traitement système peut être trop occupé par la croissance automatique de la base de données pour prendre en charge vos besoins de charge de travail chaque fois que vous démarrez SQL Server. Vous pouvez éviter cette surcharge en augmentant la taille de tempdb.
Performances accrues dans tempdb
Dans SQL Server, les performances de tempdb sont améliorées des manières 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 de pages mixtes dans tempdb est amélioré.
Déplacement des fichiers de données et des journaux de tempdb
Pour déplacer les données tempdb et les fichiers journaux, consultez Déplacer des bases de données système.
Options de base de données
Le tableau suivant répertorie la valeur par défaut de chaque option de base de données dans la base de données tempdb et indique si l’option peut être modifiée. 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 |
| NUMERIC_ROUNDABORT | OFF | Oui |
| PAGE_VERIFY | CHECKSUM pour les nouvelles installations de SQL Server. AUCUN 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).
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. 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 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 associé
Option SORT_IN_TEMPDB pour les index
sys.master_files (Transact-SQL)
Déplacer des fichiers de bases de données