Tutoriel SQL Server TempDB

Par: Eric Blinn | Mise à jour: 2020-07-17 | Commentaires (2) | Connexes: Plus > Bases de données système

Problème

SQL Server est installé avec quatre bases de données système par défaut. Ils sont maître, modèle, msdb et TempDB. J’ai besoin de savoir ce qu’est la base de données système « TempDB » et à quoi elle sert. Ces tutoriels précédents covermaster, modèle etmsdb en détail.

Solution

Cette astuce couvrira la base de données TempDB. TempDB est une base de données qui ade nombreuses fonctions dans SQL Server, mais elle est rarement appelée explicitement. Il a tellement de fonctions que c’est souvent l’une des bases de données les plus chargées, sinon la plus occupée sur la plupart des instances SQL Server. Lisez la suite pour savoir ce que beaucoup de ces fonctions sont!

Aperçu de SQL Server TempDB

Quel est le but de SQL Server TempDB ?

L’une des fonctions de TempDB est d’agir comme une page ou un fichier d’échange au niveau du système d’exploitation. Si une opération SQL Server est trop importante pour être intégrée en mémoire ou si l’octroi initial de mémoire pour une requête est trop petit, l’opération peut être déplacée sur le disque dans TempDB.

Une autre fonction de TempDB est de stocker des tables temporaires. Toute personne qui a créé une table temporaire dans T-SQL en utilisant un préfixe dièse ou de hachage (#) ou le préfixe doublepound / hachage (##) a créé un objet dans TempDB car c’est là que ceux-ci sont stockés.

CREATE TABLE #MSSQLTips (RowID int);

Lors de l’exécution d’atrigger, les tables virtuelles insérées et supprimées sont stockées dans TempDB.

Toutes les bases de données qui utilisent l’ISOLATION des SNAPSHOTS VALIDÉS en lecture (RCSI) verront leurs informations de gestion des versions stockées dans TempDB.

Chacune des fonctions ci-dessus de TempDB est terminée sans jamais mentionner réellement Tempdb par son nom et peut donc être une utilisation surprenante pour TempDB.

TempDB peut également être appelé explicitement de plusieurs manières. Les tables peuvent être générées dans TempDB en référençant la base de données dans une instruction create. Le code ressemble à une opération DDL normale, mais lorsqu’elle est exécutée dans TempDB, la table est, par définition, une table temporaire.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB est régénérée à chaque démarrage de l’instance SQL Server. Tous les objets qui peuvent avoir été créés dans TempDB lors d’une session précédente ne seront pas conservés lors d’un redémarrage du service. TempDB obtient sa liste d’objets initiale à partir de la base de données de modèles qui sera généralement vide ou presque vide.

On peut éventuellement demander à la maintenance de l’index de faire des tris dans TempDB plutôt que de tenter de le faire en utilisant de l’espace libre dans la base de données des utilisateurs.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

Puis-je exécuter SQL Server sans base de données TempDB ?

SQL Server ne peut pas s’exécuter (sauf dans des scénarios de récupération très extrêmes et pour des périodes très courtes) sans TempDB.

La version de la base de données TempDB est-elle unique à la version, à l’édition et au niveau de patch de SQL Server ?

TempDB est une base de données en grande partie vierge et il n’y a rien de particulièrement unique à propos de tout niveau d’édition ou de correctif de SQL Server.

Il n’y a eu que 2 changements significatifs dans TempDB au cours des dernières années au niveau des versions. La première est qu’à partir de SQL Server 2016, le comportement de TEMPDB a été modifié de sorte que l’activation des indicateurs de trace T1117 et T1118 ne sont plus considérées comme une meilleure pratique. La seconde est qu’à partir de SQL Server 2019, certains objets système très utilisés dans TempDB ont été mis à niveau vers des tables en mémoire pour réduire le contenu et améliorer les performances globales du serveur.

Pouvez-vous supprimer ou renommer la base de données TempDB et devriez-vous le faire pour une raison quelconque?

TempDB ne peut pas être supprimé, détaché, mis hors ligne ou renommé. La tentative de Toutes ces opérations renverra une erreur. Il n’y a aucune raison que cela ne doive jamais être tenté car il s’agit d’une base de données système critique.

Emplacement de la base de données SQL Server TempDB

Quels fichiers et noms physiques prennent en charge la base de données TempDB ?

Les noms de fichiers logiques par défaut sont tempdev pour les données et templog pour le journal.Ils peuvent être trouvés sur le disque en tant que tempdb.mdf et templog.ldf respectivement. Tempdbcommunément a de nombreux fichiers de données.

Combien de fichiers de données TempDB devrait-il y avoir?

Il devrait y avoir un fichier de données TempDB pour chaque thread/core/vCPU sur l’instanceavec un maximum de 8.

Où puis-je trouver la base de données TempDB sur le disque et dans SSMS?

Les fichiers peuvent être trouvés en interrogeant sys.sysfiles dmv ou le volet fichier de la fenêtre databaseproperties.

SELECT * FROM TempDB.sys.sysfiles;

Cela fait-il une différence là où résident les fichiers de base de données TempDB (ont-ils besoin d’être sur C: \, SAN, etc.)?

Les meilleures pratiques exigent que vos fichiers de données TempDB soient sur le stockage le plus rapide disponible en raison de leur niveau d’activité. Dans certains cas, cela peut signifier les stocker avec les fichiers de données de la base de données utilisateur, mais un volume séparé sur un disque séparé est préféré, s’il est disponible.

Il existe des opinions divergentes sur le fichier journal TempDB, mais cet auteur préfère insérer ce fichier avec les autres fichiers journaux de transactions.

La base de données TempDB peut-elle être déplacée ?

La base de données TempDB peut être déplacée. La méthode de déplacement de TempDB est facileà mettre en œuvre. Il suffit de modifier la propriété FILENAME du ou des fichiers souhaités. Aucun fichier n’a besoin d’être déplacé comme lorsque d’autres bases de données système sont déplacées. SQLServer créera le(s) fichier(s) avec le nouveau nom et / ou dans le nouvel emplacement au démarrage du service suivant. N’oubliez pas de revenir en arrière et de supprimer les fichiers TempDB non utilisés qui seront laissés.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

Taille et croissance de la base de données SQL Server TempDB

Comment les paramètres de croissance de la base de données et du journal des transactions doivent-ils être configurés pour la base de données TempDB?

Les paramètres de croissance automatique doivent être définis sur des nombres spécifiques de Mo, et non sur un pourcentage.Chaque fichier de données doit avoir le même nombre de MO définis pour la croissance.

Le dimensionnement correct des fichiers pour une base de données est généralement une bonne pratique, mais jamais plus que pour TempDB. Surveillez l’instance – surtout si elle estnouvelle – et déterminez si les fichiers se développent pendant le fonctionnement normal.Si c’est le cas, modifiez la taille de départ de sorte que SQL Server ne soit pas obligé de faire croître les fichiers de manière cohérente après chaque démarrage du service.

Quelle est la taille typique de la base de données TempDB et quelle est sa taille ?

Il n’y a pas de bonne réponse à cette question. Ce qui est typique pour une instance peut ne pas être typique pour une autre. Cet auteur a vu des cas où la TempDB n’est fondamentalement pas utilisée en moyenne que quelques Mo d’espace utilisé à un moment donné. Pourtant, d’autres sont constamment interrogés et utilisent des centaines de Go d’espace – même dans l’espace mesuré en Tb –, en particulier lors d’une fenêtre de maintenance d’index.

Lorsque vous décidez de la quantité d’espace à consacrer à TempDB sur une nouvelle instance, peu de choses peuvent être faites. La nouvelle instance remplace-t-elle une instance plus ancienne et existante ? Si c’est le cas, utilisez une trace perfmon ou une capture DMV planifiée pour déterminer l’espace utilisé dans TempDB sur plusieurs jours. Assurez-vous d’inclure une fenêtre de maintenance dans cette capture. Essayez d’allouer suffisamment d’espace sur la nouvelle instance pour tenir compte du plus grand espace utilisé capturé pendant la trace avec une marge de manœuvre supplémentaire. Ensuite, la bonne taille des fichiers pour s’adapter à cette allocation d’espace.

Les 2 requêtes ci-dessous peuvent vous aider. Le premier prendra un instantané de la taille des fichiers de données et des fichiers journaux ainsi que de l’espace utilisé dans les fichiers. Cela peut être exécuté régulièrement pour suivre l’utilisation au fil du temps. La deuxième requête retournera le nombre de fois où le fichier journal a augmenté depuis le dernier redémarrage de l’instance.

S’il n’y a pas de charge de travail existante pouvant être utilisée comme guide et qu’il n’y a pas d’autres conseils, peut-être d’un fournisseur de logiciels, la seule chose qui peut être faite est de s’assurer qu’il y a de l’espace dans TempDB pour tenir compte des objets individuels les plus importants qui peuvent être triés dans TempDB lors d’une maintenance d’index. fenêtres. À partir de là, surveillez l’utilisation et ajustez-la en conséquence.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

Maintenance de la base de données TempDB du serveur

Pouvez-vous réduire la base de données et le journal des transactions pour la base de données TempDB ?

Techniquement, oui, mais ce n’est pas une opération qui devrait jamais être terminée.L’objectif devrait toujours être de dimensionner correctement TempDB. Si une sorte d’opération ponctuelle provoque une croissance inexplicable de ces fichiers, ils reviendront à la normale au redémarrage du service suivant.

Devez-vous reconstruire les index?

TempDB ne fera pas partie des plans de maintenance liés à l’index. Cela signifie qu’il n’y a pas de reconstruction d’index, de réorganisation ou de mise à jour de statistiques.

Devez-vous exécuter des contrôles d’intégrité quel est le code?

Il existe des opinions divergentes sur la question de savoir si l’intégrité de TEMPDB doit être vérifiée.Cet auteur exposera l’argumentpour et contre et permettra au lecteur de décider.

Contre : Une opération de contrôle d’intégrité ne peut pas vérifier TempDB avec la même totalité que toutes les autres bases de données.Les données qui existent dans TempDB sont très transitoires, de sorte que l’opération de vérification d’intégrité ne trouvera pas beaucoup de choses à vérifier à un moment donné.Pour la même raison, toutes les données qui sont corrompues sont susceptibles d’être des données actives qui provoqueront une alerte lorsqu’elles seront lues du disque bien avant qu’une opération de contrôle d’intégrité ne soit probablement appelée.

Pour: Bien qu’une opération de vérification d’intégrité ne vérifie pas tout dans TempDB qu’elle vérifie dans d’autres bases de données qui n’ont pas de valeur.TempDB est une base de données intégrale et s’il y a corruption, il est impératif de le savoir afin que des mesures correctives puissent être prises.

Le code pour exécuter une vérification d’intégrité est ci-dessous.

DBCC CHECKDB ('TempDB'); 

Objets de base de données SQL Server TempDB

Quelles sont les tables et procédures clés de la base de données TEMPDB qui sont importantes pour les professionnels de SQL Server ?

Il n’y en a pas.Cette base de données est utilisée pour le stockage temporaire de objects.It n’est pas installé avec des tables ou des procédures.

Puis-je stocker mes propres objets dans la base de données TEMPDB ?

Les objets peuvent certainement être créés dans TempDB, mais ils ne persisteront pas après le prochain démarrage du service SQL Server.

Autorisations de base de données SQL Server TempDB

Qui a accès à la base de données TempDB et existe-t-il différents niveaux d’autorisations ?

Tout le monde a accès et le même accès à la TempDB.

Sauvegarde de la base de données SQL Server TempDB

Dois-je sauvegarder la base de données TempDB?

TempDB ne peut pas être sauvegardé. Il ne stocke que des données transitoires et est recréé chaque fois que le service démarre, il n’y a donc rien à récupérer en cas de catastrophe.

Modèle de récupération de base de données SQL Server TempDB

Quelle TempDB de récupération la base de données TempDB devrait-elle avoir et le modèle de récupération peut-il être modifié?

TempDB est en récupération SIMPLE et cela ne peut pas être modifié.

Restauration de la base de données SQL Server TempDB

Est-il nécessaire de restaurer la base de données TempDB ?

TempDB ne peut pas être restauré. Il ne devrait y avoir aucune donnée et aucun code dans TEMPDB à récupérer. Si TempDB est corrompu, supprimez simplement les données et les fichiers journaux afin que de nouveaux fichiers puissent être générés lors du prochain démarrage du service.

Prochaines étapes

• Cette astuce montre comment créer des alertes pour les problèmes trouvés dans les tailles de TempDB et l’espace disque
• Cette astuce montre comment activer l’initialisation instantanée des fichiers, une fonctionnalité importante pour toutes les bases de données, mais surtout TempDB si elle n’est pas correctement dimensionnée.

Dernière mise à jour: 2020-07-17

À propos de l’auteur

Eric Blinn est le Sr. Architecte de données pour l’écuyer Patton Boggs. Il est également auteur SQL et leader de groupe local PASS.
Voir tous mes conseils

Ressources connexes

• Plus de conseils SQL Server DBA…