tempdb saturée sur SQL Server : causes et solutions.

À quoi sert tempdb

tempdb héberge plusieurs types d'objets :

• les tables temporaires et les variables de type table ;
• les opérations de tri et de jointure qui débordent de la mémoire (spills) ;
• le magasin de versions (version store), utilisé par l'isolation par instantané, la lecture validée par instantané (RCSI) et certaines opérations d'index en ligne ;
• des objets internes du moteur.

tempdb est recréée à chaque redémarrage du service.

Trouver le consommateur

Plusieurs vues de gestion dynamique répondent à la question :

• sys.dm_db_file_space_usage : répartition de l'espace entre objets utilisateur, objets internes et magasin de versions.
• sys.dm_db_session_space_usage et sys.dm_db_task_space_usage : consommation par session et par tâche.
• sys.dm_tran_active_snapshot_database_transactions : transactions qui retiennent le magasin de versions.

Les causes fréquentes

• Des requêtes dont les estimations de lignes sont fausses, qui trient ou hachent des volumes énormes et débordent dans tempdb.
• Un traitement qui crée de très grosses tables temporaires.
• Une transaction longue qui empêche le nettoyage du magasin de versions.
• Des reconstructions d'index avec l'option SORT_IN_TEMPDB.
• Une configuration initiale inadaptée : un seul fichier, petite taille, croissance par petits incréments.

Les remèdes

• Corriger les requêtes et mettre à jour les statistiques pour supprimer les débordements.
• Limiter la taille des tables temporaires et indexer ce qui doit l'être.
• Terminer ou corriger la transaction longue qui bloque le magasin de versions.
• Dimensionner tempdb d'après la charge réelle et fixer une croissance en taille fixe.

Les bonnes pratiques de configuration

• Plusieurs fichiers de données de même taille et de même croissance, pour limiter la contention d'allocation. Le nombre se règle selon les observations, souvent jusqu'à huit fichiers. Depuis SQL Server 2016, l'installation propose d'en créer plusieurs.
• Un stockage rapide, avec de l'espace libre en réserve.
• Des alertes sur l'espace libre de tempdb.

En urgence

Identifier la session la plus consommatrice et décider de la terminer. Ajouter un fichier tempdb sur un autre volume peut gagner du temps. Le redémarrage du service recrée tempdb, mais c'est une coupure : il reste le dernier recours.