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.

-- Table temporaire locale (locale dans le sens d’une transaction SQL)
Create table #table2 (id int)   
-- Table temporaire globale
Create table ##table1 (id int)

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 : https://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 :

SELECT name, size*8 As Size_In_KB
FROM sys.master_files
WHERE database_id = DB_ID(N''tempdb'');

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)

DBCC SHRINKFILE(tempdev, 5);

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:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=20Mb);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=20Mb);
GO

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 :

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

SELECT sys.dm_exec_sessions.session_id AS [SESSION ID] ,
DB_NAME(database_id) AS [DATABASE Name] ,
HOST_NAME AS [System Name] ,
program_name AS [Program Name] ,login_name AS [USER Name] ,status ,cpu_time AS [CPU TIME (in milisec)] ,
total_scheduled_time AS [Total Scheduled TIME (in milisec)] ,total_elapsed_time AS [Elapsed TIME (in milisec)] ,
(memory_usage * 8) AS [Memory USAGE (in KB)] ,
(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)] ,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)] ,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)] ,
(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)] ,CASE is_user_process WHEN 1 THEN ''user session'' WHEN 0 THEN ''system session'' END AS [SESSION Type],
 row_count AS [ROW COUNT] 
FROM sys.dm_db_session_space_usage
 INNER join sys.dm_exec_sessions 
ON sys.dm_db_session_space_usage.session_id = sys.dm_exec_sessions.session_id

• Références:

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

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