Tout sur la base de données Sql server TempDB


J’ai souvent entendu les DBA, lors de mes passages en prestation chez  mes clients, échanger sur la Tempdb qui a grossit en taille et qui, par effet ricochet, a planté l’exploitation ou la production. D’où l’objet de cet article : à quoi sert cette base, quand/comment est-elle utilisée et comment réduire sa taille et quelles sont les bonnes pratiques pour optimiser les performances de la tempdb…

  • C’est quoi la Tempdb ? 

       Il s”agit d”une base de données temporaire qui est recréée à chaque fois que le service SQL Server est démarré. C’est une ressource globale disponible pour tous  les utilisateurs connectés à l’instance Sql server.

 

  • A quoi sert la Tempdb ? 

Les tables temporaires globales et locales sont créées dans la base Tempdb.

Si vous exécutez ce script les deux tables, ci-dessus, seront créées dans la base tempdb.

La base tempdb peut contenir  également d’autres objets :  des tables temporaires, procédures stockées temporaires, les curseurs et des objets internes créés par le moteur de base de données.Ces objets peuvent être des tables de travail créées pour les opérations de tris ou de mise en attentes. A titre d’exemple, il est possible d’utiliser l’instruction SORT_IN_TEMPDB dans une requête SQL de création d’index (CREATE INDEX). Cela aura pour conséquence  la réduction du temps nécessaire à la création et la construction de l’index lorsque la base de données tempdb se trouve sur un emplacement disque diffèrent de celui des bases de données utilisateur. Cependant il faut faire attention à l’espace disque temporaire utilisée (Plus de détails sur l’option  SORT_IN_TEMPDB : http://technet.microsoft.com/fr-fr/library/ms188281.aspx).

Enfin, La tempdb sert à maintenir les différentes versions de lignes créées généralement  par des déclencheurs ou des isolations de niveau Sanpashot ou Read-Committed Snapshot.Pour information le niveau d’isolation contrôle le verrouillage et le comportement de contrôle de version de ligne des instructions Transact-SQL émises par une connexion à SQL Server.Si le niveau d’isolation est Snapshot alors les données lues par n”importe quelle instruction d”une transaction représenteront la version cohérente d”un point de vue transactionnel des données qui existaient au début de la transaction. La transaction peut seulement reconnaître les modifications de données qui ont été validées avant qu”elle ne commence.Par contre s’il est  Read-Committed Snapshot  le Moteur de base de données utilise le contrôle de version de ligne pour présenter à chaque instruction un instantané cohérent des données (du point de vue transactionnel) telles qu’2014-02-20 17:02:34’elles étaient au début de l”instruction. Les verrous ne sont pas utilisés pour protéger les données des mises à jour par d”autres transactions.

  •  Comment réduire la Tempdb ? 

On peut vérifier  l’espace utilisé par la  tempdb avec la requête suivante :

 

Si on constate que la tempdb devient trop gourmande en terme d’espace disque, alors il est possible de réduire sa taille (Shrink) de différentes manières :

1ère façon :

La manière la plus simple, mais pas la plus élégante ! c’est de redémarrer l’instance Sql server. Cela obligera le moteur sql server à recréer une base tempdb vierge.

2ème façon :

On peut également utiliser Management Studio:

        En effectuant un clic droit sur la base Tempdb –>Tâche —>Réduire —>Base de données

 3ème façon:

Par le biais de SQL En utilisant la commande DBCC SHRINKFILE

                DBCC SHRINKFILE(logical_filename, size_in_MB)

 

 Si on exécute une nouvelle fois la requête qui affiche la taille de la tempdb ,on voit bien que la taille de la base est passée à 5M.

  

4ème façon:

Par le biais de SQL en utilisant la commande en utilisant ALTER DATABASE.Le script suivant redéfinit la taille des deux fichiers logs et données à 20M:

Il faut noter qu’avec Alter Database et Management studio, vous ne pouvez pas modifier la taille de la tempdb si sa taille est supérieure à la taille cible. 

  • Faut il réduire “Shrink” la Tempdb ? 

Il faut faire attention avec le « Shrink » de la tempdb car si au moment de son exécution la base est utilisée vous pouvez avoir des erreurs. Donc il est préférable de redémarrer l’instance Sql server.

  • Ce qui est interdit avec la Tempdb ? 

– Elle ne peut pas être supprimée.

– On ne peut pas la sauvegarder.

– On ne peut pas créer plusieurs groupes de fichiers,Seul le groupe « Primary »est disponible.

– On ne peut pas changer le mode de récupération de la base qui est par défaut « Simple ». 

  • Quelles sont les bonnes pratiques tempdb?

               – Autorisez les fichiers de tempdb à croître automatiquement en fonction des besoins.Ainsi, le fichier peut augmenter de volume jusqu”à ce que le disque soit plein.

– Définissez l”incrément de croissance de la taille du fichier avec une taille suffisante afin d”éviter que la valeur de croissance des fichiers de la base de données tempdb ne soit trop faible. Microsoft  recommande d”appliquer les règles              générales suivantes lors de la définition de l”incrément FILEGROWTH des fichiers tempdb :

 

Taille des fichiers de tempdb

Incrément FILEGROWTH

0 à 100 Mo

10 Mo

         100 à 200 Mo

                20 Mo

        200 Mo ou plus

10%*

   – Pré-allouez l”espace de tous les fichiers de tempdb en définissant leur taille avec une valeur suffisamment élevée pour supporter la charge de travail. Cela évite que tempdb ne se développe trop fréquemment et que les performances                        n”en soient affectées. 

                – Créez autant de fichiers que nécessaire pour optimiser la bande passante disque. L”utilisation de plusieurs fichiers réduit les conflits de stockage de tempdb et entraîne une évolutivité bien meilleure. 

                – Attribuez de la même taille à chaque fichier de données pour optimiser les performances du remplissage proportionnel.

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

                – Placez la base de données tempdb sur des disques différents de ceux employés par les bases de données utilisateur.

  • Le bonus

Voici en bonus une requête qui peut vous aider à surveiller la tempdb.

  • Références:

http://technet.microsoft.com/fr-fr/library/ms190768.asp

http://support.microsoft.com/kb/307487/fr

Commentez cet article